How to select many-to-many simulated properties in EFCore with a single SQL query

EFCore does not support many-to-many relationships without creating a binding object. I need to efficiently select a subset of properties from the "other end" of the one-to-many relationship.

I would have sworn that he would already have an answer, but he did not find it.

In these models:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public ICollection<BookCategory> BookCategories { get; set; }
} 

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public string ExtraProperties {get; set; }
    public ICollection<BookCategory> BookCategories { get; set; }
}

public class BookCategory
{
    public int BookId { get; set; }
    public Book Book { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

This question is an extension of a similar, but another question, titled " Select specific properties from the entities included in the kernel.

I am looking for a query that returns List<string> categoryNamesBook categories.

This nested selection, using "projection", leads to several SQL queries:

var result= await _ctx.Books
    .Where(x => x.BookId == id)
    .Select(x => x.BookCategorys
        .Select(y => y.Category.CategoryName )
        .ToList())                
    .FirstOrDefaultAsync();

Any solution with .Include(x => x.BookCategory).ThenInclude(x => Category)will download all data from the Server before applying the selection.

- , ?:

  • 1 SQL
  • 2 ''.
  • List<string> .

Entity Framework Core " " , .

+2
1

, SQL SQL- ORM. EF Core ( 2.0.2), , N + 1 , . 2.1, .

. , SelectMany Select + FirstOrDefault. , EF Core , , . , , . , LINQ SQL-:

var result = await _ctx.Books
    .Where(x => x.BookId == id)
    .SelectMany(x => x.BookCategorys
        .Select(y => y.Category.CategoryName))
    .ToListAsync();
+4

Source: https://habr.com/ru/post/1686578/


All Articles