EntityFramework: Discrepency between Includes with Navigation Property vs. Includes with Select?

Question

This is more of a syntax question than an actual bug or error, as I finally got what I wanted working. But I want to understand and perhaps improve upon my current solution.

Schema

Let's assume I have a Users table, with a one-to-many relationship to a table Posts, and a further one-to-one relationship table of Authors - one for each Post.

I want to write a custom repository function to get all Users, with all Posts, with each Author per Post.

Attempt #1 (Doesn't Work)

I thought I could do something like:

public IQueryable<User> GetUsersWithPostsAndAuthors()
{
    var query = GetAll();

    // include all details on user object
    return query
        .Include(user => user.Posts.Select(x => x.Author));
}

it doesn't seem to include the Author entity. Actually, I was getting the following error:

Lambda expression used inside Include is not valid.

Attempt #2 (Also Doesn't Work)

Then I thought that maybe those Posts need to be in the query first, so I tried this:

public IQueryable<User> GetUsersWithPostsAndAuthors()
{
    var query = GetAll();

    // include all details on user object
    return query
        .Include(user => user.Posts)
        .Include(user => user.Posts.Select(x => x.Author)
}

Unfortunately, I got the same error:

Lambda expression used inside Include is not valid.

Attempt #3 (Works!)

However, if I use the version of Include where you can provide a string navigationPropertyPath (which actually I don't like since it's just a hardcoded string), with something like this:

public IQueryable<User> GetUsersWithPostsAndAuthors()
{
    var query = GetAll();

    // include all details on user object
    return query
        .Include(user => user.Posts)
        .Include("Posts.Author");
}

The query works as expected!

What is going on here? I thought the Select projection would do the same as Include. (And there seem to be some answers on Stackoverflow suggesting that.)

More importantly, is there a way to accomplish what I want without hardcoding the Posts.Author in the Include call? I'd like to have static type checks here.

Answer 1

What is going on here?

No offense, but nothing more than not quite understanding what Include is for. It's only for including navigation properties, not for projections.

The syntax is quite clear:

• Include for navigation properties off of the root of the query:

  .Include(user => user.Posts)
  

• ThenInclude for navigation properties off of included navigation properties:

  .Include(user => user.Posts).ThenInclude(p => p.Author)
  

The latter example is equivalent to .Include("Posts.Author"), but the lambda syntax is preferred because of compile-time checking. In the old EF6 version there was no ThenInclude and the syntax for including more levels was as you wrote: .Include(user => user.Posts.Select(x => x.Author)).

A projection is a Select in the LINQ query, not inside an Include statement. For example:

return query.Select(u => new { u.Id, u.Name });

Projections and Includes exclude one another. In the projection there's nothing in which a navigation property can be included. A query like:

return query
    .Include(u => u.Posts)
    .Select(u => new 
    {
        u.Id, 
        u.Name,
        Posts = u.Posts.Select(p => p.Title)
    });

will completely ignore the Include. There's no trace of it in the generated SQL: only Post.Title will be queried, not all Post fields, as an Include would do.