c# - EF Core：在相关的一对多实体中查询多层次数据的正确方法

问题描述

当必须基于多个连接（包括通过连接表进行多对多连接）获取数据时，我正在尝试学习编写高效的实体框架查询。在下面的示例中，我想获取包含特定书籍的所有状态。

让我们使用具有以下表格/实体的模型，所有这些都通过导航属性链接：State、City、Library、Book、LibraryBook（图书馆和图书之间的多对多关系的连接表。）

每个州有 1 个或多个城市
每个城市有 1 个或多个图书馆
每个图书馆都有很多书，每本书可能存在于多个图书馆。

我怎样才能最好地返回包含特定书籍的所有州？我倾向于认为单独的查询可能比 1 个大查询更好，但我不确定最好的实现是什么。我认为首先在单独的查询中从多对多关系中获取 LibraryId 可能是一个很好的开始方式。

所以为此：

var bookId = 12;
var libraryIds = _context.LibraryBook.Where(l => l.BookId == bookId).Select(s => s.LibraryId);

如果这是第一个，我不确定如何最好地查询下一个数据以获得包含每个 LibraryIds 的城市。我可以使用 foreach：

var cities = new List<City>;
foreach(var libraryId in libraryIds)
{
    var city = _context.City.Where(c => c.Library = libraryId)
    cities.Add(city);
}

但是，我必须为包含城市的州再做一次foreach，这一切都增加了很多单独的 SQL 查询！

这真的是解决这个问题的唯一方法吗？如果没有，有什么更好的选择？

提前致谢！

标签： c#.netentity-frameworklinqentity-framework-core

解决方案

Database management systems are extremely optimized in combining tables and selecting columns from the result. The transport of the selected data is the slower part.

Hence it is usually better to limit the data that needs to be transported: let the DBMS do all the joining and selecting.

For this, you don't need to put everything in one big LINQ statement that is hard to understand (and thus hard to test, reuse, maintain). As long as your LINQ statements remain IQuerayble<...>, the query is not executed. Concatenating several of these LINQ statements is not costly.

Back to your question

If you followed the entity framework conventions, your one-to-many relations and your many-to-many will have resulted in classes similar to the following:

class State
{
    public int Id {get; set;}
    public string Name {get; set;}
    ...

    // every State has zero or more Cities (one-to-many)
    public virtual ICollection<City> Cities {get; set;}
}

class City
{
    public int Id {get; set;}
    public string Name {get; set;}
    ...

    // Every City is a City in exactly one State, using foreign key:
    public int StateId {get; set;}
    public virtual State State {get; set;}

    // every City has zero or more Libraries (one-to-many)
    public virtual ICollection<Library> Libraries {get; set;}
}

Library and Books: many-to-many:

class Library
{
    public int Id {get; set;}
    public string Name {get; set;}
    ...

    // Every Library is a Library in exactly one City, using foreign key:
    public int CityId {get; set;}
    public virtual City City {get; set;}

    // every Library has zero or more Books (many-to-many)
    public virtual ICollection<Book> Books {get; set;}
}

class Book
{
    public int Id {get; set;}
    public string Title {get; set;}
    ...

    // Every Book is a Book in zero or more Libraries (many-to-many)
    public virtual ICollection<Book> Books {get; set;}
}

This is all that entity framework needs to know to recognize your tables, the columns in the tables and the relations between the tables.

You will only need attributes or fluent API if you want to deviate from the conventions: different identifiers for columns or tables, non-default types for decimals, non default behaviour for cascade on delete, etc.

In entity framework, the columns in the tables are represented by the non-virtual properties; the virtual properties represent the relations between the tables.

The foreign key is an actual column in the table, hence it is non-virtual. The one-to-many has virtual ICollection<Type> on the "one" side and virtual Type on the "many" side. The many-to-many has virtual ICollection<...> on both sides.

There is no need to specify the junction table. Entity framework recognizes the many-to-many and creates the junction table for you. If you use database first, you might need to use fluent API to specify the junction table.

But how am I supposed to do the joins without a junction table?

Answer: don't do the (group-)joins yourself, use the virtual ICollections!

How can I best return all of the States that contain a particular Book?

int bookId = ...
var statesWithThis = dbContext.States
    .Where(state => state.Cities.SelectMany(city => city.Libraries)
                                .SelectMany(library => library.Books)
                                .Select(book => book.Id)
                                .Contains(bookId);

In words: you have a lot of States. From every State, get all Books that are in all Libraries that are in all Cities in this State. Use SelectMany to make this one big sequence of Books. From every Book Select the Id. The result is one big sequence of Ids (of Books that are in Libraries that are in Cities that are in the State). Keep only those States that have at least one Book.

Room for Optimization

If you regularly need to do similar questions, like: "Give me all States that have a Book from a certain Author", or "Give me all Libraries that have a Book with a certain title", consider to create extension methods for this. This way you can concatenate them as any LINQ method. The extension method creates the query, it will not execute them, so this won't be a performance penalty.

Advantages of the extension method: simpler to understand, reusable, easier to test and easier to change.

If you are not familiar with extension methods, read Extension Methods Demystified

// you need to convert them to IQueryable with the AsQueryable() method, if not
// you get an error since the receiver asks for an IQueryable
// and a ICollection was given
public static IQueryable<Book> GetBooks(this IQueryable<Library> libraries)
{
    return libraries.SelectMany(library => library.AsQueryable().Books);
}

public static IQueryable<Book> GetBooks(this IQueryable<City> cities)
{
    return cities.SelectMany(city => city.Libraries.AsQueryable().GetBooks());
}

Usage:

Get all states that have a book by Karl Marx:

string author = "Karl Marx";
var statesWithCommunistBooks = dbContext.States.
    .Where(state => state.GetBooks()
                         .Select(book => book.Author)
                         .Contains(author));

Get all Cities without a bible:

string title = "Bible";
var citiesWithoutBibles = dbContext.Cities
    .Where(city => !city.GetBooks()
                       .Select(book => book.Title)
                       .Contains(title));