名表网站,开发一个网站一般需要多少钱,网站调用优酷视频去除广告,美妆网页设计代码大全概要
本文主要介绍EF Core 5.0中一个新特性#xff0c;就是Include方法支持带过滤器的委托参数#xff0c;并且说明一下该特性在实际使用中的一个大坑#xff0c;希望读者在未来的开发中避免踩坑。
本文使用的是Dotnet 6.0和EF Core 7.0。
代码和实现
案例介绍
本文主要…概要
本文主要介绍EF Core 5.0中一个新特性就是Include方法支持带过滤器的委托参数并且说明一下该特性在实际使用中的一个大坑希望读者在未来的开发中避免踩坑。
本文使用的是Dotnet 6.0和EF Core 7.0。
代码和实现
案例介绍
本文主要使用一个图书和作者的案例。
一个作者Author有多本自己写的的图书Book一本图书Book有一个发行商Publisher一个作者Author是一个系统用户User
我们需要查找写书最多的前两名作家该作家需要年龄在20岁以上国籍是法国。需要他们的FirstName LastName EmailUserName以及在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] NFrance 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] NFrance 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的数据后将序列转换成IEnumerableEF将不会为返回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] NFrance 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] NFrance 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 ListBook Books { get; set; } new ListBook();}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 ListUserRole UserRoles { get; set; } new ListUserRole();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 ListUserRole UserRoles { get; set; } new ListUserRole();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 ListBookWeb 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; }}
