I have Table1 with the following relationships (they are not enforced they only create the relationship for the navigation properties)
Table1 (*)->(1) Table2 Table1 (*)->(1) Table3 Table1 (*)->(1) Table4 Table1 (*)->(1) Table5
Using eager loading code looks like
IQueryable<Table1> query = context.Table1s;
query = query.Include(Table1 => Table1.Table2);
query = query.Include(Table1 => Table1.Table3);
query = query.Include(Table1 => Table1.Table4);
query = query.Include(Table1 => Table1.Table5);
query = query.Where(row => row.Table1Id == table1Id);
query.Single();
Every way I try to organize the Include() statements, the first table included has an Inner Join in its generated TSQL and the remaining are Left Outer Join (I expect Left Outer for all of them). I am not Entity Splitting, they are just plain tables with FKs.
If DefaultIfEmpty() is the only solution, can someone explain the reason why when all but the first table included provide the SQL expected?
My understanding is that default behavior for a Navigation Property is LEFT OUTER but I cannot get ALL properties to generate the default.
Any help would be MUCH appreciated.
Thank you in advance!
—– Created TSQL (modified for brevity but structure the same) ——-
(@p__linq__0 int)SELECT [Limit1].[Table1Id] AS [Table1Id], [Limit1].[OtherData] AS [OtherData] FROM ( SELECT TOP (2) [Extent1].[Table1Id] AS [Table1Id], [Extent1].[OtherData] As [OtherData] FROM [dbo].[Table1] AS [Extent1] INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2Id] = [Extent2].[Table2Id] LEFT OUTER JOIN [dbo].[Table3] AS [Extent3] ON [Extent1].[Table3Id] = [Extent3].[Table3Id] LEFT OUTER JOIN [dbo].[Table4] AS [Extent4] ON [Extent1].[Table4Id] = [Extent4].[Table4Id] LEFT OUTER JOIN [dbo].[Table5] AS [Extent5] ON [Extent1].[Table5Id] = [Extent5].[Table5Id] WHERE [Extent1].[Table1Id] = @p__linq__0 ) AS [Limit1]
Best Answer
EF seems to use
INNER JOIN
for including a required andLEFT OUTER JOIN
for including an optional navigation property. Example:If I define
Customer
as a required property onOrder
......and issue this query...
...I get this SQL:
If I change in the model configuration
.HasRequired(o => o.Customer)
to...... I get exactly the same query except that
INNER JOIN [dbo].[Customers] AS [Extent2]
is replaced by:From model viewpoint it makes sense because you are saying that there can never be an
Order
without aCustomer
if you define the relationship as required. If you circumvent this requirement by removing the enforcement in the database and if you actually have then orders without a customer you violate your own model definition.Only solution is likely to make the relationship optional if you have that situation. I don't think it is possible to control the SQL that is created when you use
Include
.