c# - How to select only the records with the highest date in LINQ

I have a table, 'lasttraces', with the following fields.

Id, AccountId, Version, DownloadNo, Date

The data looks like this:

28092|15240000|1.0.7.1782|2009040004731|2009-01-20 13:10:22.000
28094|61615000|1.0.7.1782|2009040007696|2009-01-20 13:11:38.000
28095|95317000|1.0.7.1782|2009040007695|2009-01-20 13:10:18.000
28101|15240000|1.0.7.1782|2009040004740|2009-01-20 14:10:22.000
28103|61615000|1.0.7.1782|2009040007690|2009-01-20 14:11:38.000
28104|95317000|1.0.7.1782|2009040007710|2009-01-20 14:10:18.000

How can I, in LINQ to SQL, only get the last lasttrace of every AccountId (the one with the highest date)?

5 Answers

  1. Randy- Reply

    2019-11-13

    If you just want the last date for each account, you'd use this:

    var q = from n in table
            group n by n.AccountId into g
            select new {AccountId = g.Key, Date = g.Max(t=>t.Date)};
    

    If you want the whole record:

    var q = from n in table
            group n by n.AccountId into g
            select g.OrderByDescending(t=>t.Date).FirstOrDefault();
    
  2. kate- Reply

    2019-11-13

    Here is a simple way to do it

    var lastPlayerControlCommand = this.ObjectContext.PlayerControlCommands
                                    .Where(c => c.PlayerID == player.ID)
                                    .OrderByDescending(t=>t.CreationTime)
                                    .FirstOrDefault();
    

    Also have a look this great LINQ place - LINQ to SQL Samples

  3. Aaron- Reply

    2019-11-13

    If you want the whole record,here is a lambda way:

    var q = _context
                 .lasttraces
                 .GroupBy(s => s.AccountId)
                 .Select(s => s.OrderByDescending(x => x.Date).FirstOrDefault());
    
  4. Aaron- Reply

    2019-11-13

    It could be something like:

    var qry = from t in db.Lasttraces
              group t by t.AccountId into g
              orderby t.Date
              select new { g.AccountId, Date = g.Max(e => e.Date) };
    
  5. Abel- Reply

    2019-11-13

    Go a simple way to do this :-

    Created one class to hold following information

    • Level (number)
    • Url (Url of the site)

    Go the list of sites stored on a ArrayList object. And executed following query to sort it in descending order by Level.

    var query = from MyClass object in objCollection 
        orderby object.Level descending 
        select object
    

    Once I got the collection sorted in descending order, I wrote following code to get the Object that comes as top row

    MyClass topObject = query.FirstRow<MyClass>()
    

    This worked like charm.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>