Select specific properties from include ones in entity framework core

Question

I use Entity Framework Core 2.0 and have this 2 classes:

News

public class News
{
    public int Id { get; set; }
    public string Title{ get; set; }
    public string Text { get; set; }
    public DateTime ReleaseDate{ get; set; }
    public int AuthorID{ get; set; }
    public Author Author{ get; set; }
}

Author

public class Author 
{
    public Author ()
    {
     News = new List<News>();
    }

    public int Id { get; set; }
    public string Username{ get; set; }
    public string Firstname{ get; set; }
    public string Lastname{ get; set; }
    public ICollection<News> News {get;set;}
}

I want to load the news with the author's username only because I don't need the other information like author's first and lastname.

If I execute this:

static void Main(string[] args)
{
  using (var ctx = new DbContext())
  {
   var result= ctx.News.Include(x => x.Author).Select(news => new
   {
     news= news ,
     Username= pr.Author.Username
   }).ToList();
  }
}

Entity Framework Core 2.0 generates a database query like:

SELECT [x].[ID], [x].[Text], [x].[Title], [x].[AuthorId], [x].[ReleaseDate], [x.Author].[ID], [x.Verfasser].[Username] AS [Username], [x.Verfasser].[Firstname], [x.Verfasser].[Lastname]
FROM [News] AS [x]
INNER JOIN [Author] AS [x.Author] ON [x].[AuthorId] = [x.Author].[ID]

Is there a way to achieve my expected behaviour?

Answer 1

If I understand correctly, you don't want to load the Author entity (because there is no way to load entity with just some non navigation properties populated).

Then the question is why did you specifically add Include(x => x.Author) which is requesting EF to load the Author. Include / ThenInclude methods support eager loading of the related data entities. They are not needed when you use projection (Select).

Interestingly, all EF (Core) versions before EFC 2.0 were ignoring includes for projection type queries. Even the current EFC documentation states that the projection queries fall into Ignored Includes category. However, as you've noticed EFC 2.0 is not ignoring it! So it's either implementation bug and will be fixed, or the documentation bug and will be updated.

For now, simply don't use Include in projection queries. If you execute this:

var result = ctx.News.Select(news => new
{
    news = news,
    Username = news.Author.Username
}).ToList();

the EFC generated SQL query now is as expected:

SELECT [news].[Id], [news].[AuthorID], [news].[ReleaseDate], [news].[Text], [news].[Title], [news.Author].[Username]
FROM [News] AS [news]
INNER JOIN [Authors] AS [news.Author] ON [news].[AuthorID] = [news.Author].[Id]