Fetching one record from a collection using navigational properties in Entity Framework may lead to unnecessary JOINs. To show the problem we need two tables Products and Prices.
The query shown below is fetching products along with their first price.
var products = ctx.Products
.Select(p => new
{
p.Name,
FirstPriceStartdate = p.Prices
.OrderBy(price => price.Startdate)
.FirstOrDefault().Startdate,
FirstPriceValue = p.Prices
.OrderBy(price => price.Startdate)
.FirstOrDefault()
.Value,
})
.ToList();
Looks simple.
Lets look at the SQL statement or rather the execution plan.
The table Prices is JOINed twice because of the two occurrences of the expression p.Prices.OrderBy(...).FirstOrDefault()
. The Entity Framework doesn't recognize that these expressions are identical but we can help. Just use a sub-select.
var products = ctx.Products
.Select(p => new
{
Product = p,
FirstPrice = p.Prices
.OrderBy(price => price.Startdate)
.FirstOrDefault()
})
.Select(p => new
{
p.Product.Name,
FirstPriceStartdate = p.FirstPrice.Startdate,
FirstPriceValue = p.FirstPrice.Value,
})
.ToList();
That's it, the table Prices is JOINed only once now.
Having a complex query you may need multiple sub-select to select a navigational property of another navigational property. But in this case please write an email to your colleagues or a comment so the other developers understand what's going on otherwise your funny looking query will be refactored pretty soon :)