Entity Framework Core: Getting more Deadlock Information with named Transactions
Entity Framework Core 3.0: "Hidden" GROUP BY Capabilities (Part 2)

Entity Framework Core 3.0: "Hidden" GROUP BY Capabilities (Part 1)

With Entity Framework Core 3.0 (EF) the internal implementation of the LINQ query translation has been changed a lot. So, some queries that didn't work previously are working now and some that worked are not working anymore. :)

The LINQ extension method GroupBy is a special one, sometimes it works and sometimes it throws an exception. One of the use cases that are supported is the usage of an aggregate function right after calling GroupBy:

var productCount = Context.Products
.GroupBy(p => p.GroupId)
.Select(g => new
GroupId = g.Key,
Count = g.Count()

The generated SQL statement is the one we expect:

SELECT p.GroupId, COUNT(*) AS Count
FROM Products AS p
GROUP BY p.GroupId

The previous use case is simple and very limited in its usefulness. A use case that I see in the projects more often is something like: "Give me the first/last product for each group ordered by columns x, y". The query that comes to mind first is:

var firstProducts = Context.Products
.GroupBy(p => p.GroupId)
.Select(g => g.OrderBy(p => p.Name).FirstOrDefault())

Looks good but leads to an InvalidOperationException. To get the desired result without using the method GroupBy is to start the LINQ query with ProductGroups and use the navigational property Products.

var firstProducts = Context.ProductGroups
.Select(g => g.Products.OrderBy(p => p.Name).FirstOrDefault())

Remarks: If a group has no products then null is going to be pushed into our list firstProducts. Most of the time these nulls are of no use and should be filtered out.

The generated SQL is using the window function ROW_NUMBER for grouping.

ProductGroups AS p
FROM Products AS p0
) AS t
WHERE t.row <= 1
) AS t0 ON p.Id = t0.GroupId


In the end we can say that the support of GroupBy has been improved in version 3.0. The queries that have been evaluated on the client are now translated to SQL and executed on the database. The only drawback is that we need to rewrite our LINQ queries sometimes.





Feed You can follow this conversation by subscribing to the comment feed for this post.


What is ProductGroups? Is that an entity?
I need to solve the exact issue and i have tried your example but i cannot seem to find a entity with the name *Groups.

Pawel Gerr

Yes, "ProductGroups" is a DbSet.
You may look into my EF playground on github to get better idea: https://github.com/PawelGerr/EntityFrameworkCore-Demos/blob/master/src/EntityFramework.Demo/Model/DemoDbContext.cs

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment

Your Information

(Name and email address are required. Email address will not be displayed with the comment.)