Linq Get the maximum version of items from a list

Question

I have a table in sql Server:

CREATE TABLE AD_Users (
    [ImportID]          BIGINT           NOT NULL,
    [LogonID]           NVARCHAR (50)    NOT NULL,
    [EmployeeId]        NVARCHAR (64)    NULL,
    [FirstName]         NVARCHAR (64)    NULL,  
    [LastName]          NVARCHAR (64)    NULL,
 ......

There are multiple versions of the data for each loginId, keyed by ImportID. So to get the latest info for each user I use:

SELECT LogonID,ImportID,EmployeeId, FirstName, LastName
   FROM AD_Users adUsers where ImportID =  (select Max(ImportID) from 
    AD_Users  a where a.LogonID = adUsers.LogonId);

Now I want to do the same thing using Linq. EF maps this to a data set of:

public partial class ActiveDirectoryUser
{
    public long ImportID { get; set; }
    public string LogonId { get; set; }
    public long EmployeeId{ get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    .......

So all I need to do is get the items from this dbset which have the max importId for that logonid.

So far I have:

var latests = this.DbSet.GroupBy(g => g.SystemUniqueUserID)

but I am at a loss where to go after that. Is it possible to do what I want in linq or should I just generate the SQL?

Not a duplicate of How do I get the MAX row with a GROUP BY in LINQ query? as this doesn't get the latest version of each item.

Possible duplicate of [How do I get the MAX row with a GROUP BY in LINQ query?](https://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query) — tschmit007, Jul 12 '17 at 13:19
Love the way I get down voted for it being a duplicate...when it's not :( — Lobsterpants, Jul 12 '17 at 14:05

AD.Net · Accepted Answer · 2017-07-12T14:03:41.020

1

You can GroupBy LogonId and the OrderByDescending ImportID and select the first one, something like

    context.AD_Users.GroupBy(u=>u.LogonId).Select(g=>g.OrderByDescending(gg=>gg.ImportID)
.FirstOrDefault())

Please check the syntax, I just woke up

edited Jul 12 '17 at 14:03

answered Jul 12 '17 at 13:17

AD.Net

13,352
2
28
47

This seems to be what I am after - though you need FirstOrDefault() instead of First() or you get an exception (telling you to use FirstOrDefault!) Thanks – Lobsterpants Jul 12 '17 at 14:03
You just need to replace `FirstOrDefault` with `.SelectMany(u => u)`. This will give you the desired result – Romano Zumbé Jul 12 '17 at 14:07

Romano Zumbé · Answer 2 · 2017-07-12T13:43:37.967

0

This is a variation of Tejas answer. But this will give you the already existing objects instead of creating new ones.

var latest =  (from x in context.AD_Users
              group x by x.LogonId into g
              select context.AD_Users.Where(u => u.LogonId == g.Key 
                     && u.ImportID == g.Max(uu => uu.ImportID)))
              .SelectMany(u => u);

edited Jul 12 '17 at 13:43

answered Jul 12 '17 at 13:15

Romano Zumbé

7,893
4
33
55

you are doing extra and unnecessary `Where` conditions – AD.Net Jul 12 '17 at 13:36
You are doing `GroupBy` which would segregate for each logonid – AD.Net Jul 12 '17 at 13:40
This looks very much like the sort of thing I am after only it returns IQueryable> but I only need a simple IQueryable ? – Lobsterpants Jul 12 '17 at 13:41
@Lobsterpants You're right. Didn't recognize that. I've updated my answer to correct that. – Romano Zumbé Jul 12 '17 at 13:44
1

@AD.Net that is correct. But I don't see how to select the maximum object without the where. Could you post an example? – Romano Zumbé Jul 12 '17 at 13:46
@RomanoZumbé, after the grouping by logonid, each collection would have all the records for each logonid, so you need to order by each collection by the importid and pick the first one, which by the way was my answer, lol – AD.Net Jul 12 '17 at 13:49
@AD.net that is one possible way to do it. Another one is my answer (which I think is more intuitive to read as it clearly shows what is done). As far as I know, the `Where` is needed in my approach. So you are free to remove your downvote ;-) – Romano Zumbé Jul 12 '17 at 13:54
But as I said, you're doing extra where conditions. In a table of reasonable size you'll see the difference in performance. – AD.Net Jul 12 '17 at 13:56
Do you expect there to be tens of thousands of users in his AD? Maintainability should also be considered. Not only performance. – Romano Zumbé Jul 12 '17 at 13:58
@AD.Net Nevertheless, I do like your solution. More than mine ;-). It should be the accepted answer. I just wanted to justify, that there are always other solutions and that those can have a right of existence, too. – Romano Zumbé Jul 12 '17 at 14:01
Thanks @RomanoZumbé - a good answer that helped me understand what was going on. – Lobsterpants Jul 12 '17 at 14:03

Linq Get the maximum version of items from a list

2 Answers2