none
SQL query to LINQ RRS feed

  • 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

     

    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".
    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 sample
    Tuesday, 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".
    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