EF CodeFirst TPH does not produces Inner Join?

Question

Simple LINQ with include produce huge SQL code with LEFT OUTER JOIN instead of INNER JOIN.

I suppose to see INNER JOIN because two tables are connected via foreign key and column TransportPointID is not null.

Any ideas how to force EF to use INNER JOIN in this case?

var stops = context.TransportObjects.OfType<Stop>()
                                    .Include(s => s.Points).ToList();

SELECT   [Project1].[TransportObjectID]  AS [TransportObjectID],
         [Project1].[Type]               AS [Type],
         [Project1].[Name]               AS [Name],
         [Project1].[InternalName]       AS [InternalName],
         [Project1].[C1]                 AS [C1],
         [Project1].[Type1]              AS [Type1],
         [Project1].[TransportPointID]   AS [TransportPointID],
         [Project1].[TransportObjectID1] AS [TransportObjectID1],
         [Project1].[Name1]              AS [Name1],
         [Project1].[StandName]          AS [StandName]
FROM     (SELECT [Extent1].[TransportObjectID] AS [TransportObjectID],
                 [Extent1].[Name]              AS [Name],
                 [Extent1].[InternalName]      AS [InternalName],
                 [Extent1].[Type]              AS [Type],
                 [Extent2].[TransportPointID]  AS [TransportPointID],
                 [Extent2].[TransportObjectID] AS [TransportObjectID1],
                 [Extent2].[Name]              AS [Name1],
                 [Extent2].[StandName]         AS [StandName],
                 [Extent2].[Type]              AS [Type1],
                 CASE 
                   WHEN ([Extent2].[TransportPointID] IS NULL) THEN CAST(NULL AS int)
                   ELSE 1
                 END AS [C1]
          FROM   [CentralDatabase].[TransportObjects] AS [Extent1]
                 LEFT OUTER JOIN [CentralDatabase].[TransportPoints] AS [Extent2]
                   ON ([Extent2].[Type] IN (CAST('2' AS smallint),CAST('1' AS smallint)))
                      AND ([Extent1].[TransportObjectID] = [Extent2].[TransportObjectID])
          WHERE  [Extent1].[Type] IN (CAST('1' AS smallint),CAST('2' AS smallint))) AS [Project1]
ORDER BY [Project1].[TransportObjectID] ASC,
         [Project1].[C1] ASC

Model

public enum TransportObjectType : short
{
    Stop = 1,
    ReferenceObject = 2
}

public abstract class TransportObject
{
    public int TransportObjectID { get; set; }
    public string Name { get; set; }
    public virtual List<TransportPoint> Points { get; set; }
}

public class Stop : TransportObject
{
    public string InternalName { get; set; }
}

public enum TransportPointType
{
    StopPoint = 1,
    ReferencePoint = 2
}

public abstract class TransportPoint
{
    public int TransportPointID { get; set; }
    public int TransportObjectID { get; set; }
    public virtual TransportObject TransportObject { get; set; }
    public string Name { get; set; }
}

public class StopPoint : TransportPoint
{
    public string StandName { get; set; }
}

Mapping (Just important parts)

public class TransportObjectMap : EntityTypeConfiguration<TransportObject>
{
    public TransportObjectMap()
    {
        this.Map<Stop>(m => m.Requires("Type").HasValue((short)TransportObjectType.Stop))
            .Map<ReferenceObject>(m => m.Requires("Type").HasValue((short)TransportObjectType.ReferenceObject));
    }
}

public class TransportPointMap : EntityTypeConfiguration<TransportPoint>
{
    public TransportPointMap()
    {
        this.Map<StopPoint>(m => m.Requires("Type").HasValue((short)TransportPointType.StopPoint))
            .Map<ReferencePoint>(m => m.Requires("Type").HasValue((short)TransportPointType.ReferencePoint));

        // Add not nullable FK
        this.HasRequired(o => o.TransportObject)
            .WithMany(p => p.Points)
            .HasForeignKey(p => p.TransportObjectID);
    }
}

Database is perfectly correct.

Similar question answered here http://stackoverflow.com/questions/11606044/include-and-where-predicate-cause-left-join-instead-of-inner-join — Satish, Apr 05 '13 at 20:59

Carlos Corral Carvajal · Accepted Answer · 2013-04-16T10:18:19.050

0

I think that it needs the left outer join because if you have a TransportObject without any TransportPoint, the TransportObject wont be included in the result, and according to your linq query you want all TransportObjects in a list.

edited Apr 16 '13 at 10:18

answered Apr 05 '13 at 18:34

Carlos Corral Carvajal

526
4
14

EF CodeFirst TPH does not produces Inner Join?

1 Answers1