Why entity framework core rc2 lambda expressions select query load all properties that not exists in select

[MohammadAkbari]

Entity framework core rc2 generates bad sql query in sql profiler, Models and DbContext are given below, The reason for this issue is ApplicationUser inherits from IdentityUser, And this issue also true for other case that use inheritance.

public class Book
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string UserId { get; set; }
    public virtual ApplicationUser User { get; set; }
    public string CourseId { get; set; }
    public virtual Course Course { get; set; }
}
public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}
public class ApplicationUser : IdentityUser
{
    public bool Deleted { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
    {
    }
    public DbSet<Book> Books { get; set; }
    public DbSet<Course> Courses { get; set;}
    protected override void OnModelCreating(ModelBuilder builder)
    {
        foreach (var entity in builder.Model.GetEntityTypes())
        {
            entity.Relational().TableName = entity.DisplayName();
        }
        base.OnModelCreating(builder);
    }
}

When use query like this:

var list = _dbContext.Books.Select(e => new {
    e.Name,
    e.User.UserName
}).ToList();`

Generated sql select all prpoerties of User entity, while only need UserName property from user in result as below:

SELECT [e].[Id], [e].[CourseId], [e].[CourseId1], [e].[Name], [e].[UserId],
[e.User].[Id], [e.User].[AccessFailedCount], [e.User].[ConcurrencyStamp],
[e.User].[Deleted], [e.User].[Email], [e.User].[EmailConfirmed], [e.User].[LockoutEnabled], 
[e.User].[LockoutEnd], [e.User].[NormalizedEmail], [e.User].[NormalizedUserName], 
[e.User].[PasswordHash], [e.User].[PhoneNumber], [e.User].[PhoneNumberConfirmed], 
[e.User].[SecurityStamp], [e.User].[TwoFactorEnabled], [e.User].[UserName]
FROM [Book] AS [e]
LEFT JOIN [AspNetUsers] AS [e.User] ON [e].[UserId] = [e.User].[Id]
ORDER BY [e].[UserId]

By Join query result is as I expect, query is like this:

var list2 = _dbContext.Books
    .Join(_dbContext.Users,
          entryPoint => entryPoint.UserId,
          entry => entry.Id,
          (entryPoint, entry) => new { entryPoint.Name, entry.UserName }).ToList();

But I prefer use first query due to greater readability.

This problem has also been raised in http://stackoverflow.com/questions/37920608/why-entity-framework-core-rc2-lambda-expressions-select-query-load-all-propertie

[rowanmiller]

Related to #4588

[maumar]

this issue is tracked here: #6647

[MohammadAkbari]

The Issue still exist in 1.1.0 preview 1 version.

inheriting from IdentityDbContext< ApplicationUser > causes to project all properties of an entity.

it doesn't happen when entities are not inhering from IdentityDbContext< ApplicationUser >

instead of selecting only name and username in the following query all properties are projected.

var list = _dbContext.Books.Select(e => new {
    e.Name,
    e.User.UserName
}).ToList();`

SELECT [e].[Id], [e].[CourseId], [e].[CourseId1], [e].[Name], [e].[UserId],
[e.User].[Id], [e.User].[AccessFailedCount], [e.User].[ConcurrencyStamp],
[e.User].[Deleted], [e.User].[Email], [e.User].[EmailConfirmed], [e.User].[LockoutEnabled], 
[e.User].[LockoutEnd], [e.User].[NormalizedEmail], [e.User].[NormalizedUserName], 
[e.User].[PasswordHash], [e.User].[PhoneNumber], [e.User].[PhoneNumberConfirmed], 
[e.User].[SecurityStamp], [e.User].[TwoFactorEnabled], [e.User].[UserName]
FROM [Book] AS [e]
LEFT JOIN [AspNetUsers] AS [e.User] ON [e].[UserId] = [e.User].[Id]
ORDER BY [e].[UserId]

[smitpatel]

@MohammadAkbari - It happens only in the case, when FK property is nullable (in this case, ApplicationUser.Id is string type which is nullable). Regardless of what property you project out, due to group join, we need to fetch whole related entity. Improvements are tracked at #6647 which is not resolved yet.