In the previous blog post we used a navigational property to work around the limitations of the extension method GroupBy. The problem is, there is not always such a property we can use for grouping. Especially, when following domain driven design practices, a bidirectional navigation is undesirable to not to pollute the domain model (too much). Besides not introducing a navigational property intentionally there might be use cases when we have to group data by property such as the name of a record. In this case there simply cannot be a navigational property.

In this post we look at LINQ queries that behave in the same way as the navigational property we used in the previous post:

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

Let's achieve the same without using the property Products. Yet again, we start from the product groups but instead of using the navigational property we use the DbSet Products. The join condition between a group and the products is specified inside of the extension method FirstOrDefault.

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

The generated SQL looks like the following:

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

The SQL statement is identical to the one that was generated when using the navigational property.

Now, imagine there is no DbSet ProductGroups and the property GroupId is just a regular property like Name. As a replacement for missing ProductGroups we use SELECT DISTINCT GroupId on the Products. The 2nd part of the query stays the same.

var firstProducts = Context.Products  
   .Select(p => p.GroupId)  
   .Distinct()  
   .Select(id => Context.Products  
        .OrderBy(p => p.Name)  
        .FirstOrDefault(p => p.GroupId == id))  
   .ToList();

The generated SQL statement is:

    SELECT [t1].*  
    FROM  
    (  
       SELECT DISTINCT p.GroupId  
       FROM Products AS p  
    ) AS t  
    LEFT JOIN (  
        SELECT t0.*  
        FROM (  
            SELECT p0.*, ROW_NUMBER() OVER(PARTITION BY p0.GroupId ORDER BY p0.Name) AS row  
            FROM Products AS p0  
        ) AS t0  
        WHERE t0.row <= 1  
    ) AS t1 ON t.GroupId = t1.GroupId

For grouping of data using built-in capabilities of Entity Framework Core 3.0 should be enough for most cases. For more advanced use cases we can't do much but extending EF with custom functionality.

Demos: NavigationPropertiesAlternativeQueriesDemo.cs

Related Articles

entity framework
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 is a special one…
Pawel Gerr
entity framework
Entity Framework Core - Getting more Deadlock Information with named Transactions
Whether in an application in production or in integration tests on CI, the deadlocks are virtually unavoidable. The behavior of a deadlock depends on the database (MS SQL Server, MySQL, etc)  and the isolation level (like Snapshot Isolation). Some of the databases are blocking…
Pawel Gerr
entity framework
Entity Framework Core - Beware of Lifetime Extension of TransactionScopes using C# 8.0
In the blog post Entity Framework Core: Use TransactionScope with Caution! we saw a few cases we have to be aware of when using . Here is another one that can lead to an exception when using the new C# 8.0 feature: . First, we look at a code snippet without C# 8.0 feature: Which…
Pawel Gerr
entity framework
Entity Framework Core - Making RowNumber (more) useful
In the blog post ROW_NUMBER Support we saw how to implement a custom function and in the previous post Improved Value Conversion Support we realized that inserting and selecting custom types is one thing but using them for filtering is something totally different. Let's take a…
Pawel Gerr