EF Core中带过滤器参数的Include方法

过滤器参数的Include方法"/>

EF Core中带过滤器参数的Include方法

概要

本文主要介绍EF Core 5.0中一个新特性，就是Include方法支持带过滤器的委托参数，并且说明一下该特性在实际使用中的一个大坑，希望读者在未来的开发中避免踩坑。

本文使用的是Dotnet 6.0和EF Core 7.0。

代码和实现

案例介绍

本文主要使用一个图书和作者的案例。

• 一个作者Author有多本自己写的的图书Book
• 一本图书Book有一个发行商Publisher
• 一个作者Author是一个系统用户User

我们需要查找写书最多的前两名作家，该作家需要年龄在20岁以上，国籍是法国。需要他们的FirstName， LastName， Email，UserName以及在1900年以前他们发行的图书信息，包括书名Name和发行日期Published。

实体类定义详见附录。

基于新特性的代码实现

 using var dbContext = new AppDbContext();var date = new DateTime(1900, 1, 1);var authors = dbContext.Authors.AsNoTracking().Include(x => x.Books.Where(b => b.Published < date)).Include(x => x.User).Where(x => x.Country == "France" && x.Age >= 20).OrderByDescending(x => x.BooksCount).Take(2).ToList();

执行上述代码，生成的SQL如下：

      SELECT [t].[Id], [t].[Age], [t].[BooksCount], [t].[Country], [t].[NickName
], [t].[UserId], [u].[Id], [t0].[Id], [t0].[AuthorId], [t0].[ISBN], [t0].[Name],[t0].[Published], [t0].[PublisherId], [u].[Created], [u].[Email], [u].[EmailCon
firmed], [u].[FirstName], [u].[LastActivity], [u].[LastName], [u].[UserName]FROM (SELECT TOP(@__p_1) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Country
], [a].[NickName], [a].[UserId]FROM [Authors] AS [a]WHERE [a].[Country] = N'France' AND [a].[Age] >= 20ORDER BY [a].[BooksCount] DESC) AS [t]INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]LEFT JOIN (SELECT [b].[Id], [b].[AuthorId], [b].[ISBN], [b].[Name], [b].[Published], [b].[PublisherId]FROM [Books] AS [b]WHERE [b].[Published] < @__date_0) AS [t0] ON [t].[Id] = [t0].[AuthorId]ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]

从执行结果来看Include(x => x.Books.Where(b => b.Published < date))生效了，LEFT JOIN 在左联[Books]表时候，确实先进行了过滤。

需求变更，过滤特性失效

上例返回的是Author对象，但是实际需要将其转换成AuthorWeb对象，再返回给前端，代码我们调整如下：

using var dbContext = new AppDbContext();var date = new DateTime(1900, 1, 1);var authors = dbContext.Authors.AsNoTracking().Include(x => x.Books.Where(b => b.Published < date)).Include(x => x.User).Where(x => x.Country == "France" && x.Age >= 20).OrderByDescending(x => x.BooksCount).Select(x => new AuthorWeb{                                     UserFirstName = x.User.FirstName,UserLastName = x.User.LastName,UserEmail = x.User.Email,UserName = x.User.UserName,BooksCount = x.BooksCount,AllBooks = x.Books.Select(y => new BookWeb{Name = y.Name,Published = y.Published,}).ToList(),AuthorAge = x.Age,AuthorCountry = x.Country,}).ToList().Take(2).ToList();return authors;

执行上述代码，生成的SQL如下：

      SELECT [u].[FirstName], [u].[LastName], [u].[Email], [u].[UserName], [a].[
BooksCount], [a].[Id], [u].[Id], [b].[Name], [b].[Published], [b].[Id], [a].[Age
], [a].[Country]FROM [Authors] AS [a]INNER JOIN [Users] AS [u] ON [a].[UserId] = [u].[Id]LEFT JOIN [Books] AS [b] ON [a].[Id] = [b].[AuthorId]WHERE [a].[Country] = N'France' AND [a].[Age] >= 20ORDER BY [a].[BooksCount] DESC, [a].[Id], [u].[Id]

从生成的SQL，我们可以看到，在左联[Books]表时候，没有进行过滤，Include方法的过滤器特性失效。

失效的原因带过滤器参数的Include和Select两个方法无法共存，Select会让Include的过滤器失效。上面的例子如果我们一定要用Include的新特性，请使用下面的代码：

 using var dbContext = new AppDbContext();var date = new DateTime(1900, 1, 1);var authors = dbContext.Authors.AsNoTracking().Include(x => x.Books.Where(b => b.Published < date)).Include(x => x.User).Where(x => x.Country == "France" && x.Age >= 20).OrderByDescending(x => x.BooksCount).Take(2)                 .AsEnumerable().Select(x => new AuthorWeb{UserFirstName = x.User.FirstName,UserLastName = x.User.LastName,UserEmail = x.User.Email,UserName = x.User.UserName,BooksCount = x.BooksCount,AllBooks = x.Books.Select(y => new BookWeb                                                            {Name = y.Name,Published = y.Published,}).ToList(),AuthorAge = x.Age,AuthorCountry = x.Country,}).ToList();

在从数据库获取到Author和Book的数据后，将序列转换成IEnumerable，EF将不会为返回IEnumerable的LINQ生成SQL。
所以最后生成的SQL如下：

      SELECT [t].[Id], [t].[Age], [t].[BooksCount], [t].[Country], [t].[NickNam
], [t].[UserId], [u].[Id], [t0].[Id], [t0].[AuthorId], [t0].[ISBN], [t0].[Name][t0].[Published], [t0].[PublisherId], [u].[Created], [u].[Email], [u].[EmailCo
firmed], [u].[FirstName], [u].[LastActivity], [u].[LastName], [u].[UserName]FROM (SELECT TOP(@__p_1) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Countr
], [a].[NickName], [a].[UserId]FROM [Authors] AS [a]WHERE [a].[Country] = N'France' AND [a].[Age] >= 20ORDER BY [a].[BooksCount] DESC) AS [t]INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]LEFT JOIN (SELECT [b].[Id], [b].[AuthorId], [b].[ISBN], [b].[Name], [b].[Publish
d], [b].[PublisherId]FROM [Books] AS [b]WHERE [b].[Published] < @__date_0) AS [t0] ON [t].[Id] = [t0].[AuthorId]ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]

从SQL语句来看，Include的过滤器特性重新生效。

虽然上面的解决方案可以解决Include的过滤器参数无法和Select共存的问题，但是毕竟需要转换成IEnumerable再进行处理，比较繁琐。

如果我们放弃Include方法，还是使用Select方法并传入过滤器参数，我们查看一下效果。

			using var dbContext = new AppDbContext();var date = new DateTime(1900, 1, 1);var authors = dbContext.Authors.AsNoTracking().Include(x => x.User).Where(x => x.Country == "France" && x.Age >= 20).OrderByDescending(x => x.BooksCount).Take(2)                 .Select(x => new AuthorWeb{UserFirstName = x.User.FirstName,UserLastName = x.User.LastName,UserEmail = x.User.Email,UserName = x.User.UserName,BooksCount = x.BooksCount,AllBooks = x.Books.Where(x => x.Published < date)                                               																									      .Select(y => new BookWeb{Name = y.Name,Published = y.Published,}).ToList(),AuthorAge = x.Age,AuthorCountry = x.Country,}).ToList();

.Where(x => x.Published < date)放到了Select中，同时删掉了Include User的语句，因为Select本身就能更加导航属性自动联表。生成的SQL代码如下：

      SELECT [u].[FirstName], [u].[LastName], [u].[Email], [u].[UserName], [t].[
BooksCount], [t].[Id], [u].[Id], [t0].[Name], [t0].[Published], [t0].[Id], [t].[
Age], [t].[Country]FROM (SELECT TOP(@__p_0) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Country
], [a].[UserId]FROM [Authors] AS [a]WHERE [a].[Country] = N'France' AND [a].[Age] >= 20ORDER BY [a].[BooksCount] DESC) AS [t]INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]LEFT JOIN (SELECT [b].[Name], [b].[Published], [b].[Id], [b].[AuthorId]FROM [Books] AS [b]WHERE [b].[Published] < @__date_1) AS [t0] ON [t].[Id] = [t0].[AuthorId]ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]

从最后生成的代码上看，Select方法加Where过滤器参数和Include方法加Where过滤器参数效果是一样的。

结论

Include方法新增的过滤器特性确实在某些情况下可以使我们的代码更加简便，但是受到不能和Select语句共存的问题影响，而且Select语句本身也能接收Where过滤器参数，再加上Select方法可以兼容EF Core5.0之前的版本，因此还是推荐使用Select。

附录

 public class Author{public int Id { get; set; }public int Age { get; set; }public string Country { get; set; }public int BooksCount { get; set; }public string NickName { get; set; }[ForeignKey("UserId")]public User User { get; set; }public int UserId { get; set; }public virtual List<Book> Books { get; set; } = new List<Book>();}public class Book{public int Id { get; set; }public string Name { get; set; }[ForeignKey("AuthorId")]public Author Author { get; set; }public int AuthorId { get; set; }public DateTime Published { get; set; }public string ISBN { get; set; }[ForeignKey("PublisherId")]public Publisher Publisher { get; set; }public int PublisherId { get; set; }}
public class Publisher{public int Id { get; set; }public string Name { get; set; }public DateTime Established { get; set; }}public class User{public int Id { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string UserName { get; set; }public string Email { get; set; }public virtual List<UserRole> UserRoles { get; set; } = new List<UserRole>();public DateTime Created { get; set; }public bool EmailConfirmed { get; set; }public DateTime LastActivity { get; set; }}public class Role{public int Id { get; set; }public virtual List<UserRole> UserRoles { get; set; } = new List<UserRole>();public string Name { get; set; }}public  class AuthorWeb{public DateTime UserCreated { get; set; }public bool UserEmailConfirmed { get; set; }public string UserFirstName { get; set; }public DateTime UserLastActivity { get; set; }public string UserLastName { get; set; }public string UserEmail { get; set; }public string UserName { get; set; }public int UserId { get; set; }public int AuthorId { get; set; }public int Id { get; set; }public int RoleId { get; set; }public int BooksCount { get; set; }public List<BookWeb> AllBooks { get; set; }public int AuthorAge { get; set; }public string AuthorCountry { get; set; }public string AuthorNickName { get; set; }}public class BookWeb{public int Id { get; set; }public string Name { get; set; }public DateTime Published { get; set; }public int PublishedYear { get; set; }public string PublisherName { get; set; }public string ISBN { get; set; }}