Using a Select loading query with one-to-one relationship included produces inefficient SQL

[JonPSmith]

I am writing about using the .Select command in EF Core LINQ queries. If I include properties from an one-to-one relationship then the SQL produced is inefficient, i.e. it includes all the columns in the first table rather then just the few I wanted. If I don't include the properties from an one-to-one relationship then the .Select produces the correct SQL, i.e. only the columns I asked for.

Obviously this isn't urgent, but I though you might like to know about it. Also, if there is a way around it then please do let me know.

The LINQ query that produces inefficient SQL is:

var dtos = context.Books.Select(p => new BookListDto
{
    BookId = p.BookId,
    Title = p.Title,
    Price = p.Price,
    PublishedOn = p.PublishedOn,
    ActualPrice = p.Promotion == null
                        ? p.Price
                        : p.Promotion.NewPrice,
}

The produced SQL is shown below - it loads ALL the columns from the Books table rather than the three I selected

Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[BookId], [p].[Description], [p].[ImageUrl], [p].[Price], 
[p].[PublishedOn], [p].[Publisher], [p].[Title], [p.Promotion].[BookId], 
[p.Promotion].[NewPrice], [p.Promotion].[PromotionalText]
FROM [Books] AS [p]
LEFT JOIN [PriceOffers] AS [p.Promotion] ON [p].[BookId] = [p.Promotion].[BookId]
ORDER BY [p].[BookId]

The LINQ query that produces efficient SQL is:

var dtos = context.Books.Select(p => new BookListDto
{
    BookId = p.BookId,
    Title = p.Title,
    Price = p.Price,
    PublishedOn = p.PublishedOn
}

The produced SQL is shown below, which seems right to me.

Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[BookId], [p].[Title], [p].[Price], [p].[PublishedOn]
FROM [Books] AS [p]

The two classes are given below:

public class Book                                   
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime PublishedOn { get; set; }
    public string Publisher { get; set; }
    public decimal Price { get; set; }
    public string ImageUrl { get; set; }

    //----------------------------------------------
    //relationships

    public PriceOffer Promotion { get; set; }       
}
------------------------------------------------------
public class PriceOffer                        
{
    public int BookId { get; set; }            
    public decimal NewPrice { get; set; }
    public string PromotionalText { get; set; }
}

My DbContext is as follows:

public class EfCoreContext : DbContext
{
    public DbSet<Book> Books { get; set; }           
    public DbSet<PriceOffer> PriceOffers { get; set; 

    public EfCoreContext(                            
        DbContextOptions<EfCoreContext> options)     
        : base(options) {}                           

    protected override void
        OnModelCreating(ModelBuilder modelBuilder)   
    {
        modelBuilder.Entity<PriceOffer>()            
            .HasKey(x => x.BookId);                  
    }
}

For the Unit Tests the database is created by context.Database.EnsureCreated.

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: e.g. Visual Studio 2015, update 3

[smitpatel]

dupe of #6647