r mk r r mk r -4 years ago 100
C# Question

get the most recent updated record for each ID in the given list of IDs. LINQ

I have a Table, in SQLserver, called CandidateContacts which has the following fields Id, createDate,dateUpdated,address.

I am trying to get the most recent updated record for each ID for the given list of IDs.

List<int?> candidateIds = new List<int?>;

candidateIds .Add(761);
candidateIds .Add(789);
candidateIds .Add(742);

The following statement gives the following error :

Operator '&&' cannot be applied on operands of type 'bool and 'DateTime?'

var itemsMax = db.CandidateContacts.Where(x => candidateIds.Contains(x.idCandidateLogin) && db.CandidateContacts.Max(y => y.dateUpdated));

Answer Source

You could use a GroupBy and get the first element of each group after your order by dateUpdated in descending order:

var res = await db.CandidateContacts.Where(p => candidateIds.Contains(p.idCandidateLogin))
                                    .GroupBy(y => y.idCandidateLogin)
                                    .Select(g=>g.OrderByDescending(y => y.dateUpdated).FirstOrDefault()).ToListAsync();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download