Answered by:
SQL query to LINQ

Question
-
Hi Everyone,
Just having a little problem with a sql to linq query. Essentially I have a table where I'm wanting to get the first record for each unique datetime. The sql query below achieves this:
select car.* from CampAuditRecords car
inner join (select min(ID) as Id from CampAuditRecords group by AuditDate) c
on car.Id = c.Id81 0 CampRoom 176 NULL NULL 2011-07-11 14:10:00 CORPORATE\snesbitt
86 0 CampRoom 176 CampRoomFile 250 2011-07-11 14:11:00 CORPORATE\snesbitt
88 2 CampRoom 176 CampRoomFile 250 2011-07-11 14:24:00 CORPORATE\snesbitt
89 1 CampRoom 176 CampRoomField 1527 2011-07-11 14:26:00 CORPORATE\snesbitt
91 0 CampRoom 177 NULL NULL 2011-07-11 16:44:00 CORPORATE\snesbittAnd I need to write this in linq. I'm assuming its a merger of the two queries.
var innerQuery = (from records in db.CampAuditRecords group records by records.Id).Min();
var query = (from records in db.CampAuditRecords orderby sortExpression select records).ToList<CampAuditRecord>();
Any help would be appreciated.
Cheers
Tuesday, July 12, 2011 9:01 AM
Answers
-
HI
you can try this code:
var query=( from car in db.CampAuditRecords
let minID= db.CampAuditRecords.GroupBy(c=>c.AuditDate).Select(g=>g.Min(c=>c.Id))
where minID.Contains(car.Id)
select car).ToList<CampAuditRecord>();
Best Regards,
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".- Marked as answer by Alan_chen Tuesday, August 9, 2011 9:28 AM
Wednesday, July 13, 2011 2:39 AM
All replies
-
http://msdn.microsoft.com/en-us/vcsharp/aa336758#SelectManyMultiplefrom In Linq is pretty much the same as in TSQL, read the sampleTuesday, July 12, 2011 5:01 PM
-
HI
you can try this code:
var query=( from car in db.CampAuditRecords
let minID= db.CampAuditRecords.GroupBy(c=>c.AuditDate).Select(g=>g.Min(c=>c.Id))
where minID.Contains(car.Id)
select car).ToList<CampAuditRecord>();
Best Regards,
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".- Marked as answer by Alan_chen Tuesday, August 9, 2011 9:28 AM
Wednesday, July 13, 2011 2:39 AM -
Once you have obtained the resultset from your query, you can apply a distinct operation to it:
ie
query = query.Distinct();
You can do this on the resultset itself, or you can supposedly pass a second resultset in the Distinct clause:
query = query.Distinct(innerQuery);
also, union will do a similar thing, where only unique records are returned from the unions of two resultsets:
query = query.Union(innerQuery);
Wednesday, July 13, 2011 7:11 PM