none
Why EF adds same LEFT JOIN 3 times? RRS feed

  • Question

  • Hello,

    I just found this question on StackOverflow. I'm very interested in some explanation. Suppose that we have three db tables:

    CREATE TABLE [dbo].[Orders]
    (
     [OrderId] [int] IDENTITY(1,1) NOT NULL,
     [OrderDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderId] ASC)
    )
    
    CREATE TABLE [dbo].[OrderHeaders]
    (
     [OrderId] [int] NOT NULL,
     [StatusId] [int] NOT NULL,
     [Name] [varchar](50) NULL,
     CONSTRAINT [PK_OrderHeaders] PRIMARY KEY CLUSTERED ([OrderId] ASC)
    )
    
    CREATE TABLE [dbo].[StatusTypes]
    (
     [StatusId] [int] IDENTITY(1,1) NOT NULL,
     [Description] [nvarchar](50) NULL,
     CONSTRAINT [PK_StatusTypes] PRIMARY KEY CLUSTERED ([StatusId] ASC)
    )
    
    ALTER TABLE [dbo].[OrderHeaders] 
    ADD CONSTRAINT [FK_OrderHeaders_Orders] 
    FOREIGN KEY([OrderId])
    REFERENCES [dbo].[Orders] ([OrderId])
    
    ALTER TABLE [dbo].[OrderHeaders] 
    ADD CONSTRAINT [FK_OrderHeaders_StatusTypes] 
    FOREIGN KEY([StatusId])
    REFERENCES [dbo].[StatusTypes] ([StatusId])
    <br/>
    

     

    When EDMX is created from this DB it adds three entities: Order, OrderHeader and StatusType with 1:0..1 relation between Order and OrderHeader and 1:N between StatusType and OrderHeader.

    Now the problem: Why is this Linq query:

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

    Transformed to this SQL (LEFT JOIN to OrderHeaders is included 3 times)?:

    SELECT 
     [Extent1].[OrderId] AS [OrderId], 
     [Extent3].[OrderId] AS [OrderId1], 
     [Extent3].[Name] AS [Name],
     [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]
    

    Best regards,
    Ladislav

     

    Monday, February 21, 2011 11:06 PM

Answers