"Include" not working after SelectMany + Select in Entity Framework Core

Question

I have this query using Entity Framework Core (v2), but the Include/ThenInclude don't work as I expected. This is the query:

 var titlesOwnedByUser = context.Users
                   .Where(u => u.UserId == userId)
                   .SelectMany(u => u.OwnedBooks)
                   .Select(b => b.TitleInformation)
                   .Include(ti => ti.Title)
                   .ThenInclude(n => n.Translations);

The query works, but the titles I get come with Title set to null.

Just for clarification the classes are these

class User 
{
     public int Id { get; set; }
     public List<BookUser> OwnedBooks { get; set; }
}

class Book 
{
    public int Id { get; set; }
    public TitleInformation TitleInformation { get; set; }
    public List<BookUser> Owners { get; set; }
}

class BookUser 
{
     public int BookId { get; set; }
     public int UserId { get; set; }
     public Book Book { get; set; }
     public User User { get; set; }
}

class MyContext
{
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
        modelBuilder.Entity<BookUser>()
            .HasOne(x => x.User)
            .WithMany(x => x.OwnedBooks)
            .HasForeignKey(x => x.UserId);

        modelBuilder.Entity<BookUser>()
            .HasOne(x => x.Book)
            .WithMany(x => x.Owners)
            .HasForeignKey(x => x.BookId);
     }
}

class TitleInformation
{
    public int Id { get; set; }
    public Title Title { get; set; }
    public Title Subtitle { get; set; }
}

class Title
{
     public int Id { get; set; }
     public string OriginalTitle { get; set; }
     public List<Translation> Translations { get; set; }
}

What do I have to do to make the Translations load in the returned queryable?

Answer 1

This is current EF Core limitation described in the Loading Related Data - Ignored includes:

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.

According to that, you need to start the query from context.Set<TitleInformation>(). But in order to produce the desired filtering, you'll need inverse navigation property from TitleInformation to Book which currently is missing in your model:

class TitleInformation
{
    // ...
    public Book Book { get; set; } // add this and map it properly with fluent API
}

Once you have it, you could use something like this:

var titlesOwnedByUser = context.Set<TitleInformation>()
    .Include(ti => ti.Title)
        .ThenInclude(n => n.Translations)
    .Where(ti => ti.Book.Owners.Any(bu => bu.UserId == userId));

Or, in case the relationship between TitleInformation and Book is one-to-many (the above is for one-to-one):

class TitleInformation
{
    // ...
    public List<Book> Books { get; set; }
}

and respectively:

var titlesOwnedByUser = context.Set<TitleInformation>()
    .Include(ti => ti.Title)
        .ThenInclude(n => n.Translations)
    .Where(ti => ti.Books.SelectMany(b => b.Owners).Any(bu => bu.UserId == userId));