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
Products. The join condition between a group and the products is specified inside of the extension method
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
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.