Entity Framework 4 Query with .Include() doesn’t work with Join
I was quite surprised when this code doesn’t work as what I expected. The [UserProfile] and [Roles] didn’t get included at all!
var user = (from u in DataContext.Users.Include("UserProfile").Include("Roles") join o in DataContext.UserOpenIds on u.UserId equals o.UserId where o.OpenIdKey == openId select u).FirstOrDefault();
This SQL query it generated, the FK tables didn’t get included at all
{SELECT [Extent1].[UserProfileId] AS [UserProfileId], [Extent1].[UserId] AS [UserId], [Extent1].[Email] AS [Email], [Extent1].[Password] AS [Password], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[LastLoginOn] AS [LastLoginOn], [Extent1].[UserOpenId_OpenId] AS [UserOpenId_OpenId] FROM [Users] AS [Extent1] INNER JOIN [UserOpenIds] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId] WHERE [Extent2].[OpenIdKey] = @p__linq__0}
I do be able to get FK table included by explicitly add them in
DataContext.Entry(user).Reference("UserProfile").Load(); DataContext.Entry(user).Collection("Roles").Load();
This is no ideal, too much code and not straightforward.
For comparison, here’s a simpler query that .Include() does work.
var user = (from u in DataContext.Users.Include("UserProfile").Include("Roles") select u).FirstOrDefault(); //And SQL query is {SELECT [Project1].[UserProfileId] AS [UserProfileId], [Project1].[UserId] AS [UserId], [Project1].[Email] AS [Email], [Project1].[Password] AS [Password], [Project1].[CreatedOn] AS [CreatedOn], [Project1].[LastLoginOn] AS [LastLoginOn], [Project1].[UserProfileId1] AS [UserProfileId1], [Project1].[FirstName] AS [FirstName], [Project1].[LastName] AS [LastName], [Project1].[Mobile] AS [Mobile], [Project1].[Status] AS [Status], [Project1].[CreatedOn1] AS [CreatedOn1], [Project1].[UpdatedOn] AS [UpdatedOn], [Project1].[UserOpenId_OpenId] AS [UserOpenId_OpenId], [Project1].[C1] AS [C1], [Project1].[RoleId] AS [RoleId], [Project1].[RoleName] AS [RoleName], [Project1].[Description] AS [Description] FROM ( SELECT [Extent1].[UserId] AS [UserId], [Extent1].[UserProfileId] AS [UserProfileId], [Extent1].[Email] AS [Email], [Extent1].[Password] AS [Password], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[LastLoginOn] AS [LastLoginOn], [Extent1].[UserOpenId_OpenId] AS [UserOpenId_OpenId], [Extent2].[UserProfileId] AS [UserProfileId1], [Extent2].[FirstName] AS [FirstName], [Extent2].[LastName] AS [LastName], [Extent2].[Mobile] AS [Mobile], [Extent2].[Status] AS [Status], [Extent2].[CreatedOn] AS [CreatedOn1], [Extent2].[UpdatedOn] AS [UpdatedOn], [Join2].[RoleId] AS [RoleId], [Join2].[RoleName] AS [RoleName], [Join2].[Description] AS [Description], CASE WHEN ([Join2].[Role_RoleId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [Users] AS [Extent1] INNER JOIN [UserProfiles] AS [Extent2] ON [Extent1].[UserProfileId] = [Extent2].[UserProfileId] LEFT OUTER JOIN (SELECT [Extent3].[Role_RoleId] AS [Role_RoleId], [Extent3].[User_UserId] AS [User_UserId], [Extent4].[RoleId] AS [RoleId], [Extent4].[RoleName] AS [RoleName], [Extent4].[Description] AS [Description] FROM [RoleUsers] AS [Extent3] INNER JOIN [Roles] AS [Extent4] ON [Extent3].[Role_RoleId] = [Extent4].[RoleId] ) AS [Join2] ON [Extent1].[UserId] = [Join2].[User_UserId] WHERE cast('b15fcecb-cf50-4baa-960d-e9ec35f65b74' as uniqueidentifier) = [Extent1].[UserId] ) AS [Project1] ORDER BY [Project1].[UserId] ASC, [Project1].[UserProfileId1] ASC, [Project1].[C1] ASC}
Problem Found
Include() is an operator of ObjectQuery
(http://msdn.microsoft.com/en-us/library/bb738708%28v=VS.100%29.aspx), and the must be an entity type! As soon as you used JOIN the query produced ObjectQuery. Thus the Include() is no longer applicable, in a SILENT way! You won’t get an error or anything, it just stopped working.
So in order to get it work, we have to cast it back to ObjectQuery
var user = (from u in DataContext.Users join o in DataContext.UserOpenIds on u.UserId equals o.UserId join p in DataContext.UserProfiles on u.UserProfileId equals p.UserProfileId where o.OpenIdKey == openId select u).Cast<User>().Include(x=>x.UserProfile).Include(x=>x.Roles).FirstOrDefault();
And yes do you get an extract benefit that now you can use lambda expression for the referenced entity types!