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. :)

In this article:

pg
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.

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()  
               })  
  .ToList();
				
			

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())  
   .ToList();
				
			

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())  
   .ToList();
				
			

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.

				
					SELECT t0.*  
FROM   
  ProductGroups AS p  
  LEFT JOIN  
  (  
     SELECT *  
     FROM   
     (  
         SELECT *, ROW_NUMBER() OVER(PARTITION BY p0.GroupId ORDER BY p0.Name) AS row  
         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.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

EN Newsletter Anmeldung (#7)
Related Articles
AI
sg
One of the more pragmatic ways to get going on the current AI hype, and to get some value out of it, is by leveraging semantic search. This is, in itself, a relatively simple concept: You have a bunch of documents and want to find the correct one based on a given query. The semantic part now allows you to find the correct document based on the meaning of its contents, in contrast to simply finding words or parts of words in it like we usually do with lexical search. In our last projects, we gathered some experience with search bots, and with this article, I'd love to share our insights with you.
17.05.2024
Angular
SL-rund
If you previously wanted to integrate view transitions into your Angular application, this was only possible in a very cumbersome way that needed a lot of detailed knowledge about Angular internals. Now, Angular 17 introduced a feature to integrate the View Transition API with the router. In this two-part series, we will look at how to leverage the feature for route transitions and how we could use it for single-page animations.
15.04.2024
.NET
KP-round
.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023