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.Id

81    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\snesbitt

 

And 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

 

Answer 1

http://msdn.microsoft.com/en-us/vcsharp/aa336758#SelectManyMultiplefrom In Linq is pretty much the same as in TSQL, read the sample

Answer 2

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".

Answer 3

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);