Include after select not load navigation property in Entity Framework core

Question

I use entity framework core 1.1.

I have a query like below, and I expect to users who have UserProfile by using Include, load UserProfile. But this query always return UserProfile null .

Query:

var user = dbContext.UserMappers
    .Where(e => e.OldUserId == id)
    .Select(e => e.User)
    .Include(e=>e.UserProfile)
    .FirstOrDefault();

Models:

public class UserMapper
{
    [Key, ForeignKey(nameof(User))]
    public string UserId { get; set; }
    public User User { get; set; }
    public int OldUserId { get; set; }
}

public class User : IdentityUser
{
    public bool Suspended { get; set; }
    public string Nickname { get; set; }
    public virtual UserProfile UserProfile { get; set; }
}

public class UserProfile
{
    [Key, ForeignKey(nameof(User))]
    public string UserId { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
    public string Telephone { get; set; }
}

Answer 1

From the EF Core documentation - Loading Related Data - Ignored includes section (highlight is mine):

If you change the query so that it no longer returns instances of the entity type that the query began with, then the include operators are ignored.

This is different from EF6 where Include works on the final query entity type. I don't know if this is a current limitation or "by design", but for now you have to start your queries with the entity requiring includes.

In your case, it should be something like this:

var user = dbContext.Users
    // if you don't have inverse navigation property
    .Where(e => dbContext.UserMappers.Any(um => um.UserId == e.Id && um.OldUserId == id))
    // if you have inverse collection navigation property
    //.Where(e => e.UserMappers.Any(um.OldUserId == id))
    // if you have inverse reference navigation property
    //.Where(e => e.UserMapper.OldUserId == id)
    .Include(e => e.UserProfile)
    .FirstOrDefault();