Group by column and get first record of group using Linq

Question

I'm trying to query a database using Linq to get only the last message a patient has sent from every patient from the SmsMessages table. I am trying to group the messages by patient Id, sort the groupings by the date, and then return the first record of that group, like follows:

   var sms = await _dataContext.SmsMessages
                .GroupBy(message => message.PatientId)
                .Select(group => group.OrderByDescending(message => message.CreatedOn).FirstOrDefault())
                .ToListAsync();

Several answers to similar questions, for example, suggest doing a variation of the above, but I receive the following error when the query executes: The error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware1 An unhandled exception has occurred while executing the request. System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: (s.PatientId), ElementSelector:(EntityShaperExpression: EntityType: SmsMessage ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .OrderByDescending(message => message.CreatedOn)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation.

Sort by date in descending order before grouping. The grouping will not change the order. So the then .Select(group => group.First()) will get the last message from each patient. — jdweng, Jun 18 '20 at 21:36
What EF are you using? EF 6/EF Core 2.0/EF Core 2.1/EF Core 3.0/EF Core 3.1? — NetMage, Jun 18 '20 at 22:49

score 1 · Answer 1 · answered Jun 19 '20 at 09:38

This issue is related to Client vs. Server Evaluation.

Before performing GroupBy, you need to add ToList() operation to _dataContext.SmsMessages to convert the server-side operation to the client side.

Otherwise, on the server side, sql server cannot recognize client-side methods.

Just change your code as follow:

 var sms = await _dataContext.SmsMessages.ToList()
                .GroupBy(message => message.PatientId)
                .Select(group => group.OrderByDescending(message => message.CreatedOn).FirstOrDefault())
                .ToListAsync();

score 1 · Answer 2 · answered Apr 22 '21 at 13:15

In my case I couldn't afford grouping on client side since the data was too heavy to download all the rows into client side when only the last row was needed. After spending some time trying different ef core and LINQ expressions, the only way I found was using Raw SQL query which uses a temp table. The SQL query looks like:

SELECT message.* FROM message JOIN 
(SELECT PatientId, MAX(message.CreatedOn) AS CreatedOn 
FROM message GROUP BY message.PatientId) temp 
ON (message.PatientId = temp.PatientId and message.CreatedOn = temp.CreatedOn);

Then the method FromSqlRaw should be used to call this query using entity framework.

var sms = _dataContext.SmsMessages.FromSqlRaw(
    "SELECT message.* FROM message JOIN (SELECT PatientId, MAX(message.CreatedOn) AS CreatedOn FROM message GROUP BY message.PatientId) temp ON (message.PatientId = temp.PatientId and message.CreatedOn = temp.CreatedOn);").
    ToList();

Group by column and get first record of group using Linq

2 Answers2