CODEWITHSUNDEEP
ef-core-3.1
resp78
resp78

Reputation: 1534

Query many-to-many ef core relationship

I have following classes

    public class Book
    {
        public string BookId { get; set; }
        public string Title { get; set; } = string.Empty;
        public string Description { get; set; } = string.Empty;

        // Relationships
        public ICollection<BookCategory> CategoriesLink { get; set; }
    }

    public class BookCategory
    {
        public string BookId { get; set; }
        public string CategoryId { get; set; }

        public Book Book { get; set; }
        public Category Category { get; set; }
    }

    public class Category
    {
        public string CategoryId { get; set; }
        public string Name { get; set; }
        public ICollection<BookCategory> BooksLink { get; set; } 
    }

I am not sure how to get all the Books for the given Category. I am using EFCore 3.1. I tried following, but not sure if it is efficient.

    return context.Categories
        .Include(x => x.BooksLink)
        .ThenInclude(x => x.Book)
        .Where(x => x.CategoryId == category)
        .SelectMany(x=>x.BooksLink.Select(y=>y.Book))
        .ToList();

Upvotes: 0

Views: 35

Answers (1)

S&#225;ndor Jankovics
S&#225;ndor Jankovics

Reputation: 898

Due to performance reasons it is not adviced to use the .Include method.

For a better query I would use this linq

from book in context.book
 join bookCatJoin in context.BookCategory on book.id equals bookCatJoin.BookId
 join category in context.Category.Where(e=>e.Name=="crime") on bookCatJoin.categoryId equals category.CategoryId
 select book;

This query works with better performance than includes. Because each include makes a left join with the whole table. https://learn.microsoft.com/en-us/ef/core/querying/related-data Docs for .Include highlights this recommendation

Upvotes: 1

Related Questions