Entity Framework Includes with condition without selecting from includes

Question

I am trying to read ids from a table whose sub-table satisfies a certain condition. I need only columns from the first table. I use Microsoft.EntityFrameworkCore in version 6.0.2 in c#.

I have simplified the data structure a little bit. There are "shelves" with n "movies" in it, and each "movie" has n "actors". Each table has an Id, a name and a reference to his parent table, except for the first one. Something like this:

Shelves	Movies	Actors
Id	Id	Id
Name	Name	Name
	ShelfId	MovieId

I work with the "code first" approach and in my DbContext, there are the following DbSets and the appropriate connection between them in the "OnModelCreating" part:

public DbSet<Shelf> Shelves {get; set; }
public DbSet<Movie> Movies {get; set; }
public DbSet<Actor> Actors {get; set; }


modelBuilder.Entity<Shelf>()
    .HasMany(x => x.Movies)
    .WithOne()
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);
    
modelBuilder.Entity<Movie>()
    .HasMany(x => x.Actors)
    .WithOne()
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

The result i want, is to get all Ids from the shelves table where an actor name meets a certain criterion. The SQL for this is relatively simple

SELECT Shelves.Id FROM Shelves
LEFT JOIN Movies ON Shelves.Id = Movies.ShelfId
LEFT JOIN Actors ON Movies.Id = Actors.MovieId
WHERE Actors.Name = 'y'

I've tried something like

var ids = await DbContext.Shelves
                .Include(shelf => shelf.Movies)
                .ThenInclude(movie => movie.Actors.Where(actor => actor.Name == 'x'))
                .ToListAsync();

but if i run this, he just omits all the sub tables and just does a select on the entire "shelves" table. I've also tried many other things and ended up with my own DbSet<int> from which i execute the above SQL via "FromSqlInterpolated". Not nice.

Answer 1

Include is not for making query, it is only for loading related entities. In your case it is not needed.

await DbContext.Shelves
    .Where(shelf => shelf.Movies.Any(movie => movie.Actors.Where(actor => actor.Name == 'x').Any()))
    .Select(shelf => shelf.Id)
    .ToListAsync();

Or

await DbContext.Shelves
    .Where(shelf => shelf.Movies.SelectMany(movie => movie.Actors).Any(actor => actor.Name == 'x'))
    .Select(shelf => shelf.Id)
    .ToListAsync();