Why is LINQ-to-Entities is generating an extra left outer join

Question

Background

I have 2 tables: OrderItem and OrderItemValue. An OrderItem can have 0 or 1 OrderItemPackageValue. The Primary key in both tables is the same and there is a foreign key reference from OrderItems to OrderItemPackageValue

LINQ

var dummy = 
    from orderItem in context.OrderItems
    select new 
    {
        OrderID = orderItem.OrderID,
        LineNumber = orderItem.LineNumber,
        PackageValue = (decimal?)orderItem.OrderItemPackageValue.PackageValue
    };

var dummy2 = dummy.ToArray();

SQL

   SELECT 
   [Extent1].[OrderID] AS [OrderID], 
   [Extent1].[LineNumber] AS [LineNumber], 
   [Extent3].[PackageValue] AS [PackageValue]
   FROM   [dbo].[OrderItems] AS [Extent1]
   LEFT OUTER JOIN [dbo].[OrderItemPackageValues] AS [Extent2] ON ([Extent1].[OrderID] =   [Extent2].[OrderID]) AND ([Extent1].[LineNumber] = [Extent2].[LineNumber])
   LEFT OUTER JOIN [dbo].[OrderItemPackageValues] AS [Extent3] ON ([Extent2].[OrderID] = [Extent3].[OrderID]) AND ([Extent2].[LineNumber] = [Extent3].[LineNumber])

I'm trying to get this via Linq2Entities (MSSQL) and it seems to be generating an extra outer join, and I can't figure out how to get rid of it.

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

This is a bug; see here and here.

Workaround: Try changing your relationship from 1 : Many to 0..1 : Many

edited May 23 '17 at 12:30

Community

1
1

answered Jun 22 '11 at 17:08

BlueRaja - Danny Pflughoeft

84,206
33
197
283

The relationship is 1 to 0..1 – Adam Tegen Jun 22 '11 at 17:12
@Adam: Try `0..1 : 0..1`, if that's even possible? Or `Many : 0..1`? Again, this is just a workaround, as this should be fixed in the next version. – BlueRaja - Danny Pflughoeft Jun 22 '11 at 17:14

Why is LINQ-to-Entities is generating an extra left outer join

1 Answers1