EF Core 查询性能优化

一、IEnumerable 和 IQueryable 的区别

1. IEnumerable

1.1 是立即Sql查询执行，除了生成首次的 Where 条件之外，之后的查询条件都是在内存中进行，当数据量很大时，性能就会有问题。

2. IQueryable

2.1 它仅仅是一个查询表达式，只有当真正要使用数据时，它才执行Sql

2.2 当有多个Where条件拼接时，最后只生成一条查询语句，大大提升了性能。

 [HttpGet("price")]
        public async Task<ActionResult<IEnumerable<Book>>> QueryPrice()
        {
            IQueryable<Book> books=_context.Books.Where(m=>m.Price >= 1.1);
            string price = string.Empty;
            books = books.Where(m => m.PubTime.Year == 2021);
            var list = await books.ToListAsync();
            return Ok(list);
        }

 SELECT t.Id, t.AuthorName, t.Price, t.PubTime, t.Title
              FROM T_Book AS t
              WHERE (t.Price >= 1.1000000000000001E0) && (DATEPART(year, t.PubTime) == 2021))

[HttpGet("price")]
        public async Task<ActionResult<IEnumerable<Book>>> QueryPrice()
        {
            IEnumerable<Book> books=_context.Books.Where(m=>m.Price >= 1.1);
            string price = string.Empty;
            books = books.Where(m => m.PubTime.Year == 2021);
            var list =  books.ToList();
            return Ok(list);
        }

 SELECT [t].[Id], [t].[AuthorName], [t].[Price], [t].[PubTime], [t].[Title]
      FROM [T_Book] AS [t]
      WHERE [t].[Price] >= 1.1