.include with multiple levels

Topics: EF Runtime
Mar 16, 2013 at 5:43 PM
With EF5 it used to be possible to include multiple levels of related entities, regardless of if the related entity is null like this:
from o in db.directories.Include("Domains").Include("GroupSettings").Include("GroupSettings.Pricelist")
where o.node_id == nodeid
select o
In EF5 this would work even if the included GroupSettings was null. No exceptions would be thrown.
In EF6 .Include("GroupSettings.Pricelist") will cause a System.NullReferenceException if the GroupSettings related entity does not exist. Removing .Include("GroupSettings.Pricelist") fixes the issue.

I wonder if this is an issue or by design?

cheers,
verner
Developer
Mar 19, 2013 at 9:00 PM
Verner,

I just tested this and was unable to reproduce the null ref exception. There isn't any intended change here in EF6--it should still work. Would you mind providing the full stack trace of the exception and the Code First model that you are using so that we can attempt to reproduce and fix the problem?

Thanks,
Arthur
Mar 19, 2013 at 11:41 PM
Hello,

I havent really been using Code First, but I can give that a shot and see if I can replicate this with it.

I did however try this with an isolated model first test version and noticed that the resulting query seems to differ between EF5 and EF6.
(changed the db a bit for testing)
var user = (from o in db.UserSet.Include("MobileDataSettings").Include("MobileDataSettings.PriceList")
                        where o.Id == 1
                        select o).SingleOrDefault();
produces this with EF6:
SELECT 
[Limit1].[Id1] AS [Id], 
[Limit1].[Name] AS [Name], 
[Extent3].[Id] AS [Id1], 
[Extent3].[SomeProperty] AS [SomeProperty], 
[Extent5].[Id] AS [Id2], 
[Extent5].[CurrencyId] AS [CurrencyId], 
[Extent6].[User_Id] AS [User_Id]
FROM      (SELECT TOP (2) [Extent1].[Id] AS [Id1], [Extent1].[Name] AS [Name]
    FROM  [dbo].[UserSet] AS [Extent1]
    LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent2] ON [Extent1].[Id] = [Extent2].[User_Id]
    WHERE 1 = [Extent1].[Id] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent3] ON [Limit1].[Id1] = [Extent3].[User_Id]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent4] ON [Limit1].[Id1] = [Extent4].[User_Id]
INNER JOIN [dbo].[PriceListSet] AS [Extent5] ON [Extent4].[PriceList_Id] = [Extent5].[Id]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent6] ON [Limit1].[Id1] = [Extent6].[User_Id]
and this with EF5:
SELECT 
[Limit1].[Id1] AS [Id], 
[Limit1].[Name] AS [Name], 
[Extent3].[Id] AS [Id1], 
[Extent3].[SomeProperty] AS [SomeProperty], 
[Extent5].[Id] AS [Id2], 
[Extent5].[CurrencyId] AS [CurrencyId], 
[Extent6].[User_Id] AS [User_Id]
FROM      (SELECT TOP (2) [Extent1].[Id] AS [Id1], [Extent1].[Name] AS [Name]
    FROM  [dbo].[UserSet] AS [Extent1]
    LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent2] ON [Extent1].[Id] = [Extent2].[User_Id]
    WHERE 1 = [Extent1].[Id] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent3] ON [Limit1].[Id1] = [Extent3].[User_Id]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent4] ON [Limit1].[Id1] = [Extent4].[User_Id]
LEFT OUTER JOIN [dbo].[PriceListSet] AS [Extent5] ON [Extent4].[PriceList_Id] = [Extent5].[Id]
LEFT OUTER JOIN [dbo].[MobileDataSettingsSet] AS [Extent6] ON [Limit1].[Id1] = [Extent6].[User_Id]
EF5 returns the user, but EF6 returns null because of the INNER JOIN [dbo].[PriceListSet].
Removing the includes gets rid of the inner join and the problem, but will of course make it lazy load the other entities = 3 queries instead of 1.

The test db only contains one single user with Id = 1 and nothing else
Image

Hope I made at least some sense

cheers,
verner
Mar 20, 2013 at 1:27 AM
I wonder if this is not the same problem as this one: http://entityframework.codeplex.com/workitem/960
Mar 20, 2013 at 8:43 AM
Yep, seems so
Developer
Mar 22, 2013 at 2:35 AM
This is a regression introduced with the fix for #655, in the join elimination logic, and should be fixed starting with build Alpha3-20321.