• 一个Entity Framework Core的性能优化案例

    概要

    本文提供一个EF Core的优化案例，主要介绍一些EF Core常用的优化方法，以及在优化过程中，出现性能反复的时候的解决方法，并澄清一些对优化概念的误解，例如AsNoTracking并不包治百病。

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

    代码及实现

    背景介绍

    本文主要使用一个图书和作者的案例，用于介绍优化过程。

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

    本实例中Author表和Book数据量较大，记录数量全部过万条，其它数据表记录大概都是几十或几百条。具体实体类定义请见附录。

    查询需求

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

    基本优化思路

    本人做EF Core的复杂查询优化，并不推荐直接查看生成的SQL代码，我习惯按照如下方式进行：

    首先，进行EF的LINQ代码检查（初筛），找到明显的错误。

    1. 查看代码中是否有基本错误，主要针对全表载入的问题。例如EF需要每一步的LINQ扩展方法的返回值都是IQueryable类型，不能有IEnumerable类型；
    2. 查看是否有不需要的栏位；
    3. 根据情况决定是否加AsNoTracking，注意这个东西有时候加了也没用；

    其次，找到数据量较大的表，进行代码整理和针对大数据表的优化（精细化调整）

    1. 在操作大数据表时候，先要进行基本的过滤；
    2. 投影操作Select应该放到排序操作后面；
    3. 减少返回值数量，推荐进行分页操作；

    本人推荐一旦出现性能反复的时候或者代码整体基本完成的时候，再去查看生成的SQL代码。

    初始查询代码

    public  List<AuthorWeb> GetAuthors() {
     using var dbContext = new AppDbContext();
     var authors = dbContext.Authors
                 .Include(x => x.User)
                  .ThenInclude(x => x.UserRoles)
                  .ThenInclude(x => x.Role)
                  .Include(x => x.Books)
                  .ThenInclude(x => x.Publisher)
                  .ToList()
                  .Select(x => new AuthorWeb
                  {
                      UserCreated = x.User.Created,
                      UserEmailConfirmed = x.User.EmailConfirmed,
                      UserFirstName = x.User.FirstName,
                      UserLastActivity = x.User.LastActivity,
                      UserLastName = x.User.LastName,
                      UserEmail = x.User.Email,
                      UserName = x.User.UserName,
                      UserId = x.User.Id,
                      RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                      BooksCount = x.BooksCount,
                      AllBooks = x.Books.Select(y => new BookWeb
                      {
                          Id = y.Id,
                          Name = y.Name,
                          Published = y.Published,
                          ISBN = y.ISBN,
                          PublisherName = y.Publisher.Name
                      }).ToList(),
                      AuthorAge = x.Age,
                      AuthorCountry = x.Country,
                      AuthorNickName = x.NickName,
                      Id = x.Id
                  })
                  .ToList()
                  .Where(x => x.AuthorCountry == "France" && x.AuthorAge == 20)
                  .ToList();

     var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().Take(2).ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in orderedAuthors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62

    Benchmark测试后，系统资源使用情况如下：

    在这里插入图片描述

    代码性能非常差，内存消耗很大，一次执行就要消耗190MB，执行时间超过2s。如果是放到WebAPI里面调用，用户会有明显的卡顿感觉；如果面临高并发的情况，很可得会造成服务器资源紧张，返回各种500错误。

    优化代码

    初筛

    按照我们的优化思路，在查看上面的代码后，发现一个严重的问题。

    虽然每次LINQ查询返回都是IQueryable类型，但是源码中有多个ToList()，尤其是第一个，它的意思是将Author， Book，User，Role，Publisher等多个数据表的数据全部载入，前面已经说了，Author， Book两张表的数据量很大，必然影响性能。

    我们需要删除前面多余的ToList()，只保留最后的即可。请参考附录中的方法GetAuthors_RemoveToList()。

    在GetAuthors_RemoveToList()基础上，对照用户的需求，发现查询结果中包含了Role相关的信息和很多Id信息，但是查询结果并不需要这些，因此必须删掉。请参考附录中的方法GetAuthorsOptimized_RemoveColumn()

    在GetAuthorsOptimized_RemoveColumn的基础上，我们再加入AsNoTracking方法。请参考附录中的方法GetAuthorsOptimized_AsNoTracking()

    我们在Benchmark中，测试上面提到的三个方法，直接结果如下：

    在这里插入图片描述

    从Benchmark的测试结果上看，删除多余ToList方法和删除多余的栏位，确实带来了性能的大幅提升。

    但是增加AsNoTracking，性能提反而下降了一点。这也说明了AsNoTracking并不是适用所有场景。Select投影操作生成的AuthorWeb对像，并不是EF管理的，与DbContext无关，它只是作为前端API的返回值。相当于EF做了没有用的事，所以性能略有下降。

    代码进一步调整

    初筛阶段完成后，下面对代码进一步整理

    下面Take和Order操作可以并入基本的查询中，Take可以帮助我们减少返回值的数量。请见 GetAuthorsOptimized_ChangeOrder()方法。

    var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().Take(2).ToList();

    • 1

    在GetAuthorsOptimized_ChangeOrder基础上，对于dbContext.Authors，Author是一张数据量很大的表，我们需要在其进行联表操作前，先过滤掉不需要的内容，所以我们可以把Where前提，还有就是将排序操作放到投影的Select前面完成。请见 GetAuthorsOptimized_ChangeOrder方法。

    上面的两个优化方法的执行结果如下：

    在这里插入图片描述
    可以看到性略能有提升。

    下面我们为了进一步提升性能，可以查看一下生成的SQL代码，看看是否还有优化的空间。

    GetAuthorsOptimized_ChangeOrder方法生成的SQL如下：

          SELECT [u].[FirstName], [u].[LastName], [u].[Email], [u].[UserName], [t].[
BooksCount], [t].[Id], [u].[Id], [b].[Name], [b].[Published], [b].[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] >= 20
          ORDER BY [a].[BooksCount] DESC
      ) AS [t]
      INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]
      LEFT JOIN [Books] AS [b] ON [t].[Id] = [b].[AuthorId]
      ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    从生成SQL来看，Author表在使用之前过滤掉了相关的内容，但是直接Left Join了[Books]这个大表。我们可以按照前面提到的1900年以前的查询要求，在左联之前先过滤一下，请参考 GetAuthorsOptimized_SelectFilter方法。

    该方法执行后，生成的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_1) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Country
], [a].[UserId]
          FROM [Authors] AS [a]
          WHERE [a].[Country] = N'France' AND [a].[Age] >= 20
          ORDER 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_0
      ) AS [t0] ON [t].[Id] = [t0].[AuthorId]
      ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在左联之前，确实进行了过滤，该方法的性能测试如下：

    在这里插入图片描述
    在避免Book表直接进行左联后，性能有所提升。

    最后一个优化点，是在EF Core 5.0里面提供了带Filter功能的Include方法，也可以用于本案例的优化，但是该特性但是存在一些局限性，具体请参考EF Core中带过滤器参数的Include方法

    但是此方法又涉及了将IQueryable转换成IEnumerable的操作，最后要将生成的Author对象全部转换成AuthorWeb对象。代码过于繁琐，而且带来的性能提升也不明显。因此放弃这个点。

    dbContext.Authors
   .AsNoTracking()
   .Include(x => x.Books.Where(b => b.Published < date))
   ......

    • 1
    • 2
    • 3
    • 4

    结论

    从这个优化过程来看，其实对性能提升最大的贡献就是删除多余的ToList()，避免全表载入和删除不需要的栏位两项。其它所谓更精细的优化，性能提升有限。

    附录

    实体类定义

     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; }
     
 }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83

    优化方法

    [Benchmark]
public  List<AuthorWeb> GetAuthors() {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                                 .Include(x => x.User)
                                 .ThenInclude(x => x.UserRoles)
                                 .ThenInclude(x => x.Role)
                                 .Include(x => x.Books)
                                 .ThenInclude(x => x.Publisher)
                                 .ToList()
                                 .Select(x => new AuthorWeb
                                 {
                                     UserCreated = x.User.Created,
                                     UserEmailConfirmed = x.User.EmailConfirmed,
                                     UserFirstName = x.User.FirstName,
                                     UserLastActivity = x.User.LastActivity,
                                     UserLastName = x.User.LastName,
                                     UserEmail = x.User.Email,
                                     UserName = x.User.UserName,
                                     UserId = x.User.Id,
                                     RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                     BooksCount = x.BooksCount,
                                     AllBooks = x.Books.Select(y => new BookWeb
                                     {
                                         Id = y.Id,
                                         Name = y.Name,
                                         Published = y.Published,
                                         ISBN = y.ISBN,
                                         PublisherName = y.Publisher.Name
                                     }).ToList(),
                                     AuthorAge = x.Age,
                                     AuthorCountry = x.Country,
                                     AuthorNickName = x.NickName,
                                     Id = x.Id
                                 })
                                 .ToList()
                                 .Where(x => x.AuthorCountry == "France" && x.AuthorAge >= 20)
                                 .ToList();

     var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().Take(2).ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in orderedAuthors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }

 [Benchmark]
 public List<AuthorWeb> GetAuthors_RemoveToList()
 {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                                 .Include(x => x.User)
                                 .ThenInclude(x => x.UserRoles)
                                 .ThenInclude(x => x.Role)
                                 .Include(x => x.Books)
                                 .ThenInclude(x => x.Publisher)
                               //  .ToList()
                                 .Select(x => new AuthorWeb
                                 {
                                     UserCreated = x.User.Created,
                                     UserEmailConfirmed = x.User.EmailConfirmed,
                                     UserFirstName = x.User.FirstName,
                                     UserLastActivity = x.User.LastActivity,
                                     UserLastName = x.User.LastName,
                                     UserEmail = x.User.Email,
                                     UserName = x.User.UserName,
                                     UserId = x.User.Id,
                                     RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                     BooksCount = x.BooksCount,
                                     AllBooks = x.Books.Select(y => new BookWeb
                                     {
                                         Id = y.Id,
                                         Name = y.Name,
                                         Published = y.Published,
                                         ISBN = y.ISBN,
                                         PublisherName = y.Publisher.Name
                                     }).ToList(),
                                     AuthorAge = x.Age,
                                     AuthorCountry = x.Country,
                                     AuthorNickName = x.NickName,
                                     Id = x.Id
                                 })
                                // .ToList()
                                 .Where(x => x.AuthorCountry == "France" && x.AuthorAge >=20)
                                 .ToList();

     var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().Take(2).ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in orderedAuthors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }
 [Benchmark]
 public  List<AuthorWeb> GetAuthorsOptimized_RemoveColumn()
 {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                              //   .Include(x => x.User)
                                 //.ThenInclude(x => x.UserRoles)
                              //   .ThenInclude(x => x.Role)
                              //   .Include(x => x.Books)
                            //     .ThenInclude(x => x.Publisher)
                                 .Select(x => new AuthorWeb
                                 {
                                  //   UserCreated = x.User.Created,
                                   //  UserEmailConfirmed = x.User.EmailConfirmed,
                                     UserFirstName = x.User.FirstName,
                                  //   UserLastActivity = x.User.LastActivity,
                                     UserLastName = x.User.LastName,
                                     UserEmail = x.User.Email,
                                     UserName = x.User.UserName,
                                //     UserId = x.User.Id,
                                //     RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                     BooksCount = x.BooksCount,
                                     AllBooks = x.Books.Select(y => new BookWeb
                                     {
                                     //    Id = y.Id,
                                         Name = y.Name,
                                         Published = y.Published,
                                  //       ISBN = y.ISBN,
                                //         PublisherName = y.Publisher.Name
                                     }).ToList(),
                                     AuthorAge = x.Age,
                                     AuthorCountry = x.Country,
                                     AuthorNickName = x.NickName,
                                  //   Id = x.Id
                                 })
                                 .Where(x => x.AuthorCountry == "France" && x.AuthorAge >=20)
                                 .ToList();

     var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().Take(2).ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in orderedAuthors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }

[Benchmark]
 public List<AuthorWeb> GetAuthorsOptimized_AsNoTracking()
 {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                                 .AsNoTracking()
                                 // .Include(x => x.User)
                                 //   .ThenInclude(x => x.UserRoles)
                                 //   .ThenInclude(x => x.Role)
                                 //    .Include(x => x.Books)
                                 //   .ThenInclude(x => x.Publisher)
                                 .Select(x => new AuthorWeb
                                 {
                                     //UserCreated = x.User.Created,
                                     //    UserEmailConfirmed = x.User.EmailConfirmed,
                                     UserFirstName = x.User.FirstName,
                                     // UserLastActivity = x.User.LastActivity,
                                     UserLastName = x.User.LastName,
                                     UserEmail = x.User.Email,
                                     UserName = x.User.UserName,
                                     //  UserId = x.User.Id,
                                     //RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                     BooksCount = x.BooksCount,
                                     AllBooks = x.Books.Select(y => new BookWeb
                                     {
                                         // Id = y.Id,
                                         Name = y.Name,
                                         Published = y.Published,
                                         //ISBN = y.ISBN,
                                         //PublisherName = y.Publisher.Name
                                     }).ToList(),
                                     AuthorAge = x.Age,
                                     AuthorCountry = x.Country,
                                     //AuthorNickName = x.NickName,
                                     Id = x.Id
                                 })
                                 .Where(x => x.AuthorCountry == "France" && x.AuthorAge >=20)
                                 .ToList();

      var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in authors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }


 [Benchmark]
 public List<AuthorWeb> GetAuthorsOptimized_Take_Order()
 {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                                 .AsNoTracking()
                                 .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,
                                     AuthorNickName = x.NickName,
                                 })
                                 .Where(x => x.AuthorCountry == "France" && x.AuthorAge >=20)
                                 .OrderByDescending(x => x.BooksCount)
                                 .Take(2)
                                 .ToList();

    // var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in authors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }


 [Benchmark]
 public List<AuthorWeb> GetAuthorsOptimized_ChangeOrder()
 {
     using var dbContext = new AppDbContext();

     var authors = dbContext.Authors
                                  .AsNoTracking()
                                  .Where(x => x.Country == "France" && x.Age >=20)
                                  .OrderByDescending(x => x.BooksCount)
                                 // .Include(x => x.User)
                                 //   .ThenInclude(x => x.UserRoles)
                                 //   .ThenInclude(x => x.Role)
                                 //    .Include(x => x.Books)
                                 //   .ThenInclude(x => x.Publisher)
                                 .Select(x => new AuthorWeb
                                 {
                                     //UserCreated = x.User.Created,
                                     //    UserEmailConfirmed = x.User.EmailConfirmed,
                                     UserFirstName = x.User.FirstName,
                                     // UserLastActivity = x.User.LastActivity,
                                     UserLastName = x.User.LastName,
                                     UserEmail = x.User.Email,
                                     UserName = x.User.UserName,
                                     //  UserId = x.User.Id,
                                     //RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                     BooksCount = x.BooksCount,
                                     AllBooks = x.Books.Select(y => new BookWeb
                                     {
                                         // Id = y.Id,
                                         Name = y.Name,
                                         Published = y.Published,
                                         //ISBN = y.ISBN,
                                         //PublisherName = y.Publisher.Name
                                     }).ToList(),
                                     AuthorAge = x.Age,
                                     AuthorCountry = x.Country,
                                     //AuthorNickName = x.NickName,
                                     Id = x.Id
                                 })                                     
                                 .Take(2)
                                 .ToList();

     // var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().ToList();

     List<AuthorWeb> finalAuthors = new List<AuthorWeb>();
     foreach (var author in authors)
     {
         List<BookWeb> books = new List<BookWeb>();

         var allBooks = author.AllBooks;

         foreach (var book in allBooks)
         {
             if (book.Published.Year < 1900)
             {
                 book.PublishedYear = book.Published.Year;
                 books.Add(book);
             }
         }

         author.AllBooks = books;
         finalAuthors.Add(author);
     }

     return finalAuthors;
 }

//  [Benchmark]
 public List<AuthorWeb> GetAuthorsOptimized_IncludeFilter()
 {
     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)
                                  // .IncludeFilter(x =>x.Books.Where(b =>b.Published.Year < 1900))
                                  .Where(x => x.Country == "France" && x.Age >=20)
                                  .OrderByDescending(x => x.BooksCount)
                              
                                 //   .ThenInclude(x => x.UserRoles)
                                 //   .ThenInclude(x => x.Role)
                                 //    .Include(x => x.Books)
                                 //   .ThenInclude(x => x.Publisher)
                                 .Take(2)
                                 .ToList();

     // var orderedAuthors = authors.OrderByDescending(x => x.BooksCount).ToList().ToList();

     var authorList = authors.AsEnumerable().Select(x => new AuthorWeb
     {
         //UserCreated = x.User.Created,
         //    UserEmailConfirmed = x.User.EmailConfirmed,
         UserFirstName = x.User.FirstName,
         // UserLastActivity = x.User.LastActivity,
         UserLastName = x.User.LastName,
         UserEmail = x.User.Email,
         UserName = x.User.UserName,
         //  UserId = x.User.Id,
         //RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
         BooksCount = x.BooksCount,
         AllBooks = x.Books
                                        //    .Where(b => b.Published < date)
                                        .Select(y => new BookWeb
                                        {
                                            // Id = y.Id,
                                            Name = y.Name,
                                            Published = y.Published,
                                            //ISBN = y.ISBN,
                                            //PublisherName = y.Publisher.Name
                                        }).ToList(),
         AuthorAge = x.Age,
         AuthorCountry = x.Country,
         //AuthorNickName = x.NickName,
         //    Id = x.Id
     }).ToList();

     return authorList;
 }


 [Benchmark]
 public List<AuthorWeb> GetAuthorsOptimized_SelectFilter()
 {
     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))
                                 .Where(x => x.Country == "France" && x.Age >=20)
                                 .OrderByDescending(x => x.BooksCount)
                                 .Select(x => new AuthorWeb
                                  {
                                      //UserCreated = x.User.Created,
                                      //    UserEmailConfirmed = x.User.EmailConfirmed,
                                      UserFirstName = x.User.FirstName,
                                      // UserLastActivity = x.User.LastActivity,
                                      UserLastName = x.User.LastName,
                                      UserEmail = x.User.Email,
                                      UserName = x.User.UserName,
                                      //  UserId = x.User.Id,
                                      //RoleId = x.User.UserRoles.FirstOrDefault(y => y.UserId == x.UserId).RoleId,
                                      BooksCount = x.BooksCount,
                                      AllBooks = x.Books
                                         .Where(b => b.Published < date)
                                        .Select(y => new BookWeb
                                        {
                                            // Id = y.Id,
                                            Name = y.Name,
                                            Published = y.Published,
                                            //ISBN = y.ISBN,
                                            //PublisherName = y.Publisher.Name
                                        }).ToList(),
                                      AuthorAge = x.Age,
                                      AuthorCountry = x.Country,
                                      //AuthorNickName = x.NickName,
                                      //    Id = x.Id
                                  })
                                 .Take(2)
                                 .ToList();
     return authors;
 }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221
    • 222
    • 223
    • 224
    • 225
    • 226
    • 227
    • 228
    • 229
    • 230
    • 231
    • 232
    • 233
    • 234
    • 235
    • 236
    • 237
    • 238
    • 239
    • 240
    • 241
    • 242
    • 243
    • 244
    • 245
    • 246
    • 247
    • 248
    • 249
    • 250
    • 251
    • 252
    • 253
    • 254
    • 255
    • 256
    • 257
    • 258
    • 259
    • 260
    • 261
    • 262
    • 263
    • 264
    • 265
    • 266
    • 267
    • 268
    • 269
    • 270
    • 271
    • 272
    • 273
    • 274
    • 275
    • 276
    • 277
    • 278
    • 279
    • 280
    • 281
    • 282
    • 283
    • 284
    • 285
    • 286
    • 287
    • 288
    • 289
    • 290
    • 291
    • 292
    • 293
    • 294
    • 295
    • 296
    • 297
    • 298
    • 299
    • 300
    • 301
    • 302
    • 303
    • 304
    • 305
    • 306
    • 307
    • 308
    • 309
    • 310
    • 311
    • 312
    • 313
    • 314
    • 315
    • 316
    • 317
    • 318
    • 319
    • 320
    • 321
    • 322
    • 323
    • 324
    • 325
    • 326
    • 327
    • 328
    • 329
    • 330
    • 331
    • 332
    • 333
    • 334
    • 335
    • 336
    • 337
    • 338
    • 339
    • 340
    • 341
    • 342
    • 343
    • 344
    • 345
    • 346
    • 347
    • 348
    • 349
    • 350
    • 351
    • 352
    • 353
    • 354
    • 355
    • 356
    • 357
    • 358
    • 359
    • 360
    • 361
    • 362
    • 363
    • 364
    • 365
    • 366
    • 367
    • 368
    • 369
    • 370
    • 371
    • 372
    • 373
    • 374
    • 375
    • 376
    • 377
    • 378
    • 379
    • 380
    • 381
    • 382
    • 383
    • 384
    • 385
    • 386
    • 387
    • 388
    • 389
    • 390
    • 391
    • 392
    • 393
    • 394
    • 395
    • 396
    • 397
    • 398
    • 399
    • 400
    • 401
    • 402
    • 403
    • 404
    • 405
    • 406
    • 407
    • 408
    • 409
    • 410
    • 411
    • 412
    • 413
    • 414
    • 415
    • 416
    • 417
    • 418
    • 419
    • 420
    • 421
    • 422
    • 423
    • 424
    • 425
    • 426
    • 427
    • 428
    • 429
    • 430
    • 431
    • 432
    • 433
    • 434
    • 435
    • 436
    • 437
    • 438
    • 439
    • 440
    • 441
    • 442
    • 443
    • 444
    • 445
    • 446
    • 447
    • 448
    • 449
    • 450
    • 451
    • 452
    • 453
    • 454
    • 455
    • 456
    • 457
    • 458
    • 459
    • 460
    • 461
    • 462
    • 463
    • 464
    • 465
    • 466
    • 467
    • 468
    • 469
    • 470
    • 471
    • 472
    • 473
    • 474
    • 475
    • 476
  • 相关阅读:
    11.30 WAVE SUMMIT+2022将在深圳举办，官网报名通道正式开启
    MySQL(case when then end, update)
    SAE 2.0，让容器化应用开发更简单
    发布 markdown 小功能：指定图片尺寸
    Unity 向量
    ❤️新版Linux零基础快速入门到精通——第一部分❤️
    无涯教程-JavaScript - COUPNUM函数
    KVM虚拟网络概述
    Bean——IOC（Github上有代码）
    高级数据结构：最小生成树及普里姆算法
  • 原文地址：https://blog.csdn.net/weixin_43263355/article/details/134030134