Select one recent row for that record with group by two columns

Question

User-1637592233 posted

I want to select the newest record ordering by RequestDate if that has the RoomStatus as Request having the same id RoomId and RequesterId.

I have written the following query but this doesn't help to produce the output in fact this is giving me the error:

var query = _dbContext.RoomReservation
                  .Include(x => x.Room)
                  .GroupBy(x => new { x.RoomId, x.RequesterId })
                  .Select(room => new
                  {
                      Status = room.First().Status,
                      RoomId = room.First().BookId,
                      Name = room.First().Room.Name
                  }).Where(x => x.Status == Domain.Roomtatus.Requested);

The error I am getting is:

.First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I mean to group by RoomId, RequesterId, order each key's item list by RequestDate descending, and pick the first.

Included Class

public class RoomReservation
{
    public int Id { get; set; }
    public RoomStatus Status { get; set; }
    public int RoomId { get; set; }
    public string RequesterId { get; set; }
    public Room Room { get; set; }
}

public class Room
{
    public int Id { get; set; }
    public string Name{ get; set; }
}

What is the correct way of Group By with multiple columns with an order by? What I am missing in my Linq query?

Answer 1

User-1330468790 posted

Hi aakashbashyal, 

 

Are you using EF?

It seems that the problem is not about LINQ itself but traslation to sql. You should not directly use First() in a grouping.

The below group by method is correct.

.GroupBy(x => new { x.RoomId, x.RequesterId })

Then, if you want to fetch the first room reservation, you should use below methods to get the room list of each grouping and then apply First() method.

.Select(room => new
                  {
                      Status = room.ToList().First().Status,
                      RoomId = room.ToList().First().BookId,
                      Name = room.ToList().First().Room.Name
                  })

 

Apart from that, I cannot see "BookId" and "RequestDate" in your model which you mentioned in your description so that I cannot help you directly in the LINQ codes.

However, I think it might have problems to use WHERE method after the select since the 'room' here is of type "Anonymous" which means that it cannot gurantee an existence of 'Status' property with a value "Domain.Roomtatus.Requested".

  

Regarding how you could use Group by multiple columns, you could refer to below links:

EF: https://entityframework.net/knowledge-base/40755567/group-by-multiple-column-in-linq-in-csharp

 

Hope helps.

Best regards,

Sean

Answer 2

User-1637592233 posted

public class RoomReservation
{
    public int Id { get; set; }
    public RoomStatus Status { get; set; }
    public int RoomId { get; set; }
    public string RequesterId { get; set; }
    public DateTime RequestDate {get;set;}
    public Room Room { get; set; }
}

public class Room
{
    public int Id { get; set; }
    public string Name{ get; set; }
}

Some property was missing on my question earlier, so I have updated the class which has all the property. And also I am struggling to select the records using `RequestDate`.

Yes, I am using EF core in .NET 3.1 application.

Answer 3

User-1330468790 posted

Hi aakashbashyal,

  

Thank you for updating the class. 

Now I check your problem again and can conclude that you have two problems to solve:

1. Eliminate the error (LINQ Translation Error)
2. Select the records Ordered by "RequestDate"

The first problem is caused by translation between LINQ to SQL. You have to learn some basic concepts about "Client Evaluation" and "Server Evaluation". In your LINQ codes, you could select "RoomReservations" from database and then do a "Client Evaluation" by using either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync() as below.

var query = _dbContext.RoomReservation
                  .Include(x => x.Room)
                  .ToList()
                  .GroupBy(x => new { x.RoomId, x.RequesterId })
                  .Select(room => new
                  {
                      Status = room.First().Status,
                      RoomId = room.First().BookId,
                      Name = room.First().Room.Name
                  }).Where(x => x.Status == Domain.Roomtatus.Requested);

The rest of the query, GroupBy, Select and Where clauses, are evaluated on the client.

 

By solving the first problem, now you are free to use any LINQ clauses to order the result in asc or desc order.

 

Hope helps.

Best regards,

Sean

Answer 4

User-1637592233 posted

So, is Groupby could only be performed as a client evaluation with linq?

Answer 5

User-1330468790 posted

Hi aakashbashyal,

  

The answer is: No, GroupBy can also be used as a server evaluation with a limited condition.

Unfortunately currently EF Core 3.0 / 3.1 only supports server translation of GroupBy with projection of key / aggregates (similar to SQL), which is obviously different from your scenario.

In other words, you must have projection containing only key and/or aggregates after GroupBy, otherwise it won't translate.

  

You could refer to this documentation: https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupby

When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. The SQL GROUP BY is restrictive too. It requires you to group only by scalar values. The projection can only contain grouping key columns or any aggregate applied over a column.

 

Hope helps.

Best regards,

Sean