Filtering a list of results through a join table in EF Core

Question

I am using EF Core version 2.2. I have three tables:

1. User: UserId, FirstName, LastName
2. Book: BookId, BookName
3. UserBook: UserId, BookId <--join table for many-to-many relationship between User and Book

I would like to get a list of Books associated with a particular UserId.

I've tried:

var books = await _context.Books
                .Include(ub => ub.UserBooks.Where(f => f.UserId == id))
                .ToListAsync();

but alas, it doesn't work. I get the error: The Include property lambda expression 'ub => {from UserBook in ub.UserBooks where ([f].UserId == __id_0) select [f]}' is invalid.

How can I get the list of Books associated with a particular UserId? Thank you

Answer 1

You'll need to take the where filter out of the Include - include is used for eager loading, not filtering

A query finding all books which are associated with the given user:

var books = await _context.Books
            .Include(b => b.UserBooks)
            .Where(b => b.UserBooks.Any(ub => ub.UserId == id))
            .ToListAsync();