MSDTC

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!

EF4.1 Code-First: One-to-One Foreign Key Associations

public class User
    {
        public Guid UserId { get; set; }
        public int UserProfileId { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
        public string Email { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime LastLoginOn { get; set; }
        [ForeignKey("UserProfileId")]
        public UserProfile UserProfile { get; set; }
        public ICollection<Role> Roles { get; set; }
    }

public class UserProfile
    {
        public int UserProfileId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
        public string Mobile { get; set; }
        public byte Status { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime UpdatedOn { get; set; }
    }

You get

Alternatively

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<User>().HasRequired(u => u.UserProfile)
                .WithMany()
                .HasForeignKey(u => u.UserProfileId);
        }