Entity Framework - Navigation Properties Generating Extra JOINs

Question

I have noticed some very ugly SQL generated by my Entity Framework queries. Even very simple queries are generating a lot of extra JOINs when I inspect the SQL.

I created a simple model:

Orders

OrderID INT PK
OrderDate DATETIME

OrderHeaders

OrderID INT PK / FK
StatusID INT FK

StatusTypes

StatusID INT PK
Description NVARCHAR(50)

From the model, an order can have 0 or 1 Order Header. A header will have 1 Status Type.

I created the following query:

var orders = from o in db.Orders
             where o.OrderID == 1
             select new
             {
                 Order = o,
                 Status = o.OrderHeader.Status
             };

The SQL that was generated by this looked like:

SELECT 
    [Extent1].[OrderID] AS [OrderID], 
    [Extent1].[OrderDate] AS [OrderDate], 
    [Extent4].[StatusID] AS [StatusID], 
    [Extent4].[Description] AS [Description]
FROM    [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN [dbo].[OrderHeaders] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]
LEFT OUTER JOIN [dbo].[OrderHeaders] AS [Extent3] ON [Extent2].[OrderID] = [Extent3].[OrderID]
LEFT OUTER JOIN [dbo].[StatusTypes] AS [Extent4] ON [Extent3].[OrderID] = [Extent4].[OrderID]
WHERE 1 = [Extent1].[OrderID]

As you can see, there are two unnecessary left joins in the query. Why is the SQL being generated like this? Am I querying this wrong? Am I not supposed to use the navigation properties in the query?

Do I need to resort to writing joins in the query itself so it doesn't generate extra joins for me? When I write the query using joins then the generated SQL doesn't have any extra JOINs, but the LINQ to Entities query is a lot uglier/more verbose:

var orders = from o in db.Orders
             join h in db.OrderHeaders on o.OrderID equals h.OrderID into orderHeader
             from h in orderHeader.DefaultIfEmpty()
             join s in db.StatusTypes on h.StatusID equals s.StatusID into statusType
             from s in statusType.DefaultIfEmpty()
             where o.OrderID == 1
             select new
             {
                 o,
                 s
             };

This generates SQL without extra joins but it is a lot uglier to look at from the C# side.

Does anybody know how to fix this?

Answer 1

The SQL generated by Entity Framework is OK in many cases, but there will certainly be cases where you will frown upon the resulting SQL.

First of all, do you really care? Does it cause a performance problem (probably not), or do you have a DBA behind your back who will shoot you if you put this in production? :-)

If you can live with it, don't bother. Otherwise yes, you will have to monitor the SQL generated for your queries and either adjust your linq queries, or resort to stored procedures or other ways of data access (or maybe switch ORMs...).

EDIT: this particular case is still obviously a bug in EF 4 of course...

Answer 2

Yep. I've noticed the same thing. Hopefully the superfluous join won't kill your performance too badly. Hopefully EF5 will clean that up.

See:

Too Many Left Outer Joins in Entity Framework 4?