Selecting last orders for each user using Npgsql and EF Core 3.1

.net-core c# entity-framework-core npgsql postgresql

Question

I have an Order entity with 2 main columns named UserId and CreationTimeUtc. What I'm trying to do is to select last Order for each user (based on UserId column of course). Normally I expect to do so by grouping and ordering. Here is what I've tried:

var q = _context.Orders.AsNoTracking()
    .Where(t => t.CreationTimeUtc > SomeDate)
    .GroupBy(t => t.UserId)
    .Select(g => g.OrderBy(t => t.CreationTimeUtc).First())
    .Skip(10)
    .Take(10)
    .ToList();

But running the query couses this error:

System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: (r.UserId), ElementSelector:(EntityShaperExpression: EntityType: Order ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False )

I googled a lot and it seems EF Core doesn't support grouping! Is this true? And if so, how can I solve the problem? Do I need to load all rows in memory or is there any LINQ-based way? Have you any idea? Thanks in advance.

I'm using EF Core 3.1, ASP.NET Core 3.1, Npgsql 3.1, PostgreSQL 12, and C# 8.

1
1
4/11/2020 10:06:31 PM

Popular Answer

You can do this via the following:

var result = ctx.Users
    .Select(u => u.Orders
        .Where(t => t.CreationTimeUtc > someDate)
        .OrderBy(t => t.CreationTimeUtc)
        .First())
    .ToList();

It's a slightly different way to express pretty much the same thing. I'll follow up with the relevant person to see about translating your specific pattern.

0
4/12/2020 9:55:36 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow