Geek Answers Handbook

Entity Framework - Navigation Properties Generating Additional JOINs

I noticed some very ugly SQL generated by my Entity Framework queries. Even very simple queries generate a lot of extra JOINs when validating 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, the order can have 0 or 1 order header. The title has 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 this:

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]

, . SQL ? ? ?

, ? , SQL JOIN, LINQ to Entities / :

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
             };

SQL , , #.

- , ?

+5
3

SQL, Entity Framework, , , , , , SQL.

, ? (, ), , , ?: -)

, . , SQL, , linq, (, , ORM...).

EDIT: -, , ​​EF 4, ...

+4

. . , . , EF5 .

:

Entity Framework 4?

+1

. :

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

:

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:

, MSDN . , .

2:

. , EF.

0

More articles:


All Articles