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?

jeroenh · Accepted Answer · 2011-02-22T07:34:06.633

4

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

edited Feb 22 '11 at 07:34

answered Feb 21 '11 at 22:04

jeroenh

26,362
10
73
104

1

I guess that's what my question really boils down to. Is the SQL Optimizer smart enough to not care that you join 3 times? What about a more complex query where it puts in the same join 10 times? – Dismissile Feb 21 '11 at 22:18

score 1 · Answer 2 · edited May 23 '17 at 12:21

1

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?

edited May 23 '17 at 12:21

Community

1
1

answered Feb 21 '11 at 22:10

Adam Rackis

82,527
56
270
393

score 0 · Answer 3 · edited May 23 '17 at 11:58

For me it looks like a bug. If you simply run:

  var orders = from o in context.Orders
               where o.OrderId == 1
               select o.OrderHeader;

It will produce this:

SELECT 
[Extent1].[OrderId] AS [OrderId], 
[Extent3].[OrderId] AS [OrderId1], 
[Extent3].[Name] AS [Name], -- I added  this column to OrderHeaders
[Extent4].[StatusId] AS [StatusId]
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].[OrderHeaders] AS [Extent4] ON [Extent2].[OrderId] = [Extent4].[OrderId]
WHERE 1 = [Extent1].[OrderId]

Edit:

I want to know some explanation of this behavior so I reposted it on MSDN forum. I hope, we will get some answer.

Edit 2:

Check this answer. It is confirmed bug which will be solved in the next EF version.

Entity Framework - Navigation Properties Generating Extra JOINs

Orders

OrderHeaders

StatusTypes

3 Answers3