Entity Framework: High performance querying trick using SqlBulkCopy and temp tables
.NET Abstractions - It's not just about testing!

Entity Framework: Prevent redundant JOINs - watch your LINQ !

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.

EF Blog - Redundant Joins - DB Schema

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.

EF Blog - Redundant Joins - Before Subselect

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.

EF Blog - Redundant Joins - After Subselect

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

Comments

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

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment

Your Information

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