CODEWITHSUNDEEP
c#postgresql.net-coreentity-framework-corenpgsql
amiry jd
amiry jd

Reputation: 27585

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

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.

Upvotes: 1

Views: 713

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16692

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.

Upvotes: 1

Related Questions