https://docs.microsoft.com/zh-cn/ef/core/querying/
1. 查询sql的生成机制
一般来说,ef会将linq表达式生成为只需要在数据库端执行的sql。但是有些linq表达式没法生成完全由数据库处理的sql,如:
//StandardizeUrl是一个方法
var blogs = context.Blogs
.OrderByDescending(blog => blog.Rating)
.Select(
blog => new { Id = blog.BlogId, Url = StandardizeUrl(blog.Url) })
.ToList();
这种情况下ef provider不了解StandardizeUrl
方法的生成,就没法将其转换为对应的sql。所以select
方法之前的部分会生成sql,然后在数据库端执行,select
方法会在客户端执行。
但是客户端执行有时会带来性能问题:
var blogs = context.Blogs
.Where(blog => StandardizeUrl(blog.Url).Contains("dotnet"))
.ToList();
这个就相当于把全表数据查询了。
如果需要强制让客户端执行某些查询,则可以使用ToList
(把全部数据放到内存中)或AsEnumerable
(流式访问数据)方法:
var blogs = context.Blogs
.AsEnumerable()
.Where(blog => StandardizeUrl(blog.Url).Contains("dotnet"))
.ToList();
2. 跟踪查询与非跟踪查询
ef默认使用的是跟踪查询,会把查询出来的实体实例信息记录在跟踪器中。当调用SaveChanges
方法时,会把更改的部分保存到数据库。
注意:使用了
[Keyless]
特性的实体永远不会被跟踪
var blog = context.Blogs.SingleOrDefault(b => b.BlogId == 1);
blog.Rating = 5;
context.SaveChanges();
当返回结果时,如果ef检查到跟踪器中已经存在BlogId==1
的实例了,则会把这个实例直接返回(还是会执行一遍sql),而且不会用数据库中的值覆盖现有实例的值。所以以下代码可能会出乎你的意料:
//-------------------------------------
var user =await _context.SysUsers.FindAsync(id);
user.UserName = "11111";
user = await _context.SysUsers.FindAsync(id);//此时user的name还是11111,可以使用AsNoTracking解决
//--------------------------------------
var orders = context.Orders.Where(o => o.Id > 1000).ToList();
//此时查询出来的是order的id大于1000的数据,而不是大于5000的数据。因为在跟踪查询的情况下,filter里的导航属性会被认为已经加载完成
var filtered = context.Customers.Include(c => c.Orders.Where(o => o.Id > 5000)).ToList();
如果要使用非跟踪查询,则调用AsNoTracking
方法,可以提高性能:
var blogs = context.Blogs
.AsNoTracking()
.ToList();
//或者全局级别进行设置
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
如果查询之后返回的不是实体是匿名对象,ef只会跟踪匿名对象中的实体类型:
- 会跟踪
Blog
对象
var blog = context.Blogs.Select(b =>new { Blog = b, PostCount = b.Posts.Count() });
- 会跟踪
Blog
和Post
,因为post来自于linq
var blog = context.Blogs.Select(
b =>new { Blog = b, Post = b.Posts.OrderBy(p => p.Rating).LastOrDefault() });
- 不执行任何跟踪
var blog = context.Blogs.Select(b =>new { Id = b.BlogId, b.Url });
3. 加载导航属性的数据
支持三种加载方式:
- Eager loading(预加载):查询父实体时使用
Include
方法,一条sql语句直接从数据库一次性把关联子实体数据读出来,即尽可能的早读取。性能相对较好,但也需结合实际业务,如果关联实体较多,注意优化linq。 - Explic loading(显式加载):当需要时,手动调用方法从数据库读数据。通常会产生多次查询。
- Lazy loading(懒加载):遍历导航属性时才从数据库读数据(需要进行一番配置),即尽可能的晚读取。每次访问导航属性都会向数据库查询。
3.1 预加载,使用 Include
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.Include(blog => blog.Owner)
.ToList();
}
注意:因涉及到联表查询,所以可能会有性能问题。而且ef会根据之前已经加载到context中的实例自动填充导航属性,所以有时候你没写
include
也可能会在导航属性中发现值。
多层级的include
逐级包含Include
、ThenInclude
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.ThenInclude(post => post.Author)//也把Post下的Author给查出来
.ThenInclude(author => author.Photo)
.Include(blog => blog.Owner)
.ToList();
}
也可以使用单个Include
方法加载多个导航,以节省代码:
var blogs = context.Blogs
.Include(blog => blog.Owner.AuthoredPosts)//自动把owner查出来
.ThenInclude(post => post.Blog.Owner.Photo)
.ToList();
对Include
里的导航属性进行过滤
可以使用Where
, OrderBy
, OrderByDescending
, ThenBy
, ThenByDescending
, Skip
, Take
using (var context = new BloggingContext())
{
var filteredBlogs = context.Blogs
.Include(
blog => blog.Posts
.Where(post => post.BlogId == 1)
.OrderByDescending(post => post.Title)
.Take(5))
.ToList();
}
但是Include
只支持同一个过滤操作:
using (var context = new BloggingContext())
{
var filteredBlogs = context.Blogs
.Include(blog => blog.Posts.Where(post => post.BlogId == 1))
.ThenInclude(post => post.Author)
.Include(blog => blog.Posts)//无效,因为与blog.Posts.Where(post => post.BlogId == 1)不一样
.ThenInclude(post => post.Tags.OrderBy(postTag => postTag.TagId).Skip(3))
.ToList();
}
注意:Include与ThenInclude的命名空间是:Microsoft.EntityFrameworkCore
3.2 显式加载,使用 context.Entry()
查出来数据之后,通过调用DbContext.Entry(....)
方法,手动加载导航属性的数据。
using (var context = new BloggingContext())
{
//先查出来,此时blog的导航属性没有值
var blog = context.Blogs.Single(b => b.BlogId == 1);
context.Entry(blog)
.Collection(b => b.Posts)//获取导航属性的值
.Load();
context.Entry(blog)
.Reference(b => b.Owner)//获取导航属性的值
.Load();
context.Entry(blog)
.Collection(b => b.Posts)
.Query()
.Where(p => p.Rating > 3)//还可以进一步执行筛选
.ToList();
}
Collection
和Reference
的区别:一个用来获取集合,一个用来获取单个对象。
3.3 懒加载(延迟加载),使用 virtual 关键字
使用懒加载最简单的方式是安装nuget包:Microsoft.EntityFrameworkCore.Proxies
,然后调用UseLazyLoadingProxies
启用该包。
//可在dbcontext中配置
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseLazyLoadingProxies()
.UseSqlServer(myConnectionString);
//或在startup里配置
.AddDbContext<BloggingContext>(
b => b.UseLazyLoadingProxies()
.UseSqlServer(myConnectionString));
然后将导航属性定义为virtual:
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
//设置可以懒加载
public virtual ICollection<Post> Posts { get; set; }
}
然后当你访问导航数据里的数据时才会触发数据库查询操作。
3.4 修复可能出现的循环引用导致json序列化失败
如果你使用的是Newtonsoft.Json
,则这么配置就好了
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc()
.AddJsonOptions(
options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);
}
如果你使用的是System.Text.Json
截止发稿时的最新版本(EF Core 5.0)则可以这么配置:
services.AddControllers()
.AddJsonOptions(opt =>
{
opt.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.Preserve;
});
但是这种情况下,返回的json字符串会出现额外的一些如$id
、$values
之类的元数据节点,目前还没找到方式怎么去除,但是不会影响你的反序列结果。
4. 拆分查询
一般查询导航属性的数据时都会使用join进行连表查询,如果查询一对多关系时linq
涉及到多个表A\B\C\D。那么连表之后产生的数据量就是ABC*D, 随着加载更多的一对多关系就可能产生“笛卡尔爆炸”(cartesian explosion)问题。为了解决这个潜在的问题,可以使用AsSplitQuery
方法将一个查询sql拆分多个查询sql。当然什么使用视自己的linq查询情况而定,如果只是join两个表,就算了。
4.1 AsSplitQuery
AsSplitQuery
要配合Include
使用,而且只对一对多的关系进行查询时才生效,多对一和一对一不会拆分。
对于一个Role包含多个User,一个User包含多个Child类似这种结构来说,如果有如下查询
//where语句放在include前面或AsSplitQuery后面是无所谓的,不会影响sql的实际生成
_context.Roles.Include(r=>r.Users).ThenInclude(u=>u.MyChildren).AsSplitQuery().Where(r => r.Id == 1).ToList();
会生成类似以下3个sql语句:
[17:16:25 INF] Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "s"."Id", "s"."RoleName"
FROM "SysRole" AS "s"
WHERE "s"."Id" = 1
ORDER BY "s"."Id"
[17:16:26 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "s0"."Id", "s0"."CreateTime", "s0"."Pwd", "s0"."RoleId", "s0"."UserName", "s"."Id"
FROM "SysRole" AS "s"
INNER JOIN "SysUser" AS "s0" ON "s"."Id" = "s0"."RoleId"
WHERE "s"."Id" = 1
ORDER BY "s"."Id", "s0"."Id"
[17:16:26 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "s1"."Id", "s1"."Name", "s1"."SysUserId", "s"."Id", "s0"."Id"
FROM "SysRole" AS "s"
INNER JOIN "SysUser" AS "s0" ON "s"."Id" = "s0"."RoleId"
INNER JOIN "SysChild" AS "s1" ON "s0"."Id" = "s1"."SysUserId"
WHERE "s"."Id" = 1
ORDER BY "s"."Id", "s0"."Id"
如果不做拆分,则sql是:
[17:17:39 INF] Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "s"."Id", "s"."RoleName", "t"."Id", "t"."CreateTime", "t"."Pwd", "t"."RoleId", "t"."UserName", "t"."Id0", "t"."Name", "t"."SysUserId"
FROM "SysRole" AS "s"
LEFT JOIN (
SELECT "s0"."Id", "s0"."CreateTime", "s0"."Pwd", "s0"."RoleId", "s0"."UserName", "s1"."Id" AS "Id0", "s1"."Name", "s1"."SysUserId"
FROM "SysUser" AS "s0"
LEFT JOIN "SysChild" AS "s1" ON "s0"."Id" = "s1"."SysUserId"
) AS "t" ON "s"."Id" = "t"."RoleId"
WHERE "s"."Id" = 1
ORDER BY "s"."Id", "t"."Id", "t"."Id0"
显然拆分之后可以减少中间表的数据量。
对于多对一或一对一的实体关系,不管是否使用拆分,查询sql都只是一个,因为对性能没什么影响:
_context.Children.Where(c=>c.Id==1).Include(c => c.SysUser).ThenInclude(u => u.Role).AsSplitQuery().ToList()
[17:24:11 INF] Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "s"."Id", "s"."Name", "s"."SysUserId", "s0"."Id", "s0"."CreateTime", "s0"."Pwd", "s0"."RoleId", "s0"."UserName", "s1"."Id", "s1"."RoleName"
FROM "SysChild" AS "s"
INNER JOIN "SysUser" AS "s0" ON "s"."SysUserId" = "s0"."Id"
INNER JOIN "SysRole" AS "s1" ON "s0"."RoleId" = "s1"."Id"
WHERE "s"."Id" = 1
4.2 启用全局拆分
所以一对多的查询都将启用拆分查询
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(
@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;ConnectRetryCount=0",
o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}
启用了全局拆分也可以使用AsSingleQuery
针对某一个查询进行合并查:
using (var context = new SplitQueriesBloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.AsSingleQuery()
.ToList();
}
4.3 拆分查询的缺点
- 因为是拆分成了多个sql,所以当执行查询时更改数据,就无法保证数据的一致性。
- 如果数据库延迟很高,多次查询会降低性能
5. 执行原始sql
有时候你可能认为ef生成的sql不够优美,想让ef直接执行自己的sql。可以通过:
FromSqlRaw
:在DbSet<T>
上使用,使用类似{0},{1}
占位符进行参数化查询。FromSqlInterpolated
:在DbSet<T>
上使用,使用类似{name},{age}
插值字符串进行参数化查询。ExecuteSqlRaw
:在Database
上使用。ExecuteSqlInterpolated
:在Database
上使用。
如果是自行拼接的sql需注意sql注入攻击。
//执行原始sql
var blogs = context.Blogs
.FromSqlRaw("SELECT * FROM dbo.Blogs")
.ToList();
//执行存储过程
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
.ToList();
//防止sql注入的方式一,类似于string.format, 但是提供的值会包装在DbParameter中
var user = "johndoe";
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
.ToList();
//防止sql注入方式二
var user = "johndoe";
var blogs = context.Blogs
.FromSqlInterpolated($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
.ToList();
//防止sql注入方式三,使用$插值方法
var user = "johndoe";
var blogs = context.Blogs
.FromSqlInterpolated($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
.ToList();
//防止sql注入方式四,直接构造DbParameter
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
.ToList();
也可以在数据库层面执行sql:
context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");
5.1 原始sql与linq的结合
我们可以在执行完自己的sql之后,继续进行linq查询,原始sql会被视为子查询:
var searchTerm = "Lorem ipsum";
var blogs = context.Blogs
.FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
.Where(b => b.Rating > 3)
.OrderByDescending(b => b.Rating)
.ToList();
生成的sql如下:
SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC
也可以配合include
使用。
5.2 change tracking
FromSqlRaw
或 FromSqlInterpolated
方法返回的结果如果是实体类型,则一样会进行更改跟踪。后续章节会详细讲解工作原理。
5.3 缺点
- 返回的数据必须包含实体类中所有的数据。
- sql中返回的列名必须与实体类中属性上定义的列名匹配。
- 没法返回关联实体,关联实体的读取必须后续使用
include
方法
6. 全局查询过滤器
使用场景:软删除,在大多数查询情况下都不要查询软删除掉的数据。
当有这种场景时不需要每次都在linq里进行过滤,可直接在OnModelCreating
里进行全局配置。
modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
以后每次查询post时都不会查询到被软删除的数据了(包括导航属性)。
6.1 导航属性配置全局过滤
导航属性上的过滤是递归的,对某个实体设置的过滤也会被应用到导航上(有点链式过滤的感觉)。
初始化数据如下:
db.Blogs.Add(
new Blog
{
Url = "http://sample.com/blogs/fish",
Posts = new List<Post>
{
new Post { Title = "Fish care 101" },
new Post { Title = "Caring for tropical fish" },
new Post { Title = "Types of ornamental fish" }
}
});
db.Blogs.Add(
new Blog
{
Url = "http://sample.com/blogs/cats",
Posts = new List<Post>
{
new Post { Title = "Cat care 101" },
new Post { Title = "Caring for tropical cats" },
new Post { Title = "Types of ornamental cats" }
}
});
考虑以下代码:
modelBuilder.Entity<Blog>().HasMany(b => b.Posts).WithOne(p => p.Blog);
modelBuilder.Entity<Blog>().HasQueryFilter(b => b.Posts.Count > 0);
modelBuilder.Entity<Post>().HasQueryFilter(p => p.Title.Contains("fish"));
因为对Blog
实体应用了b.Posts.Count > 0
过滤来查Post
的数量,而Post
实体又会应用p.Title.Contains("fish"))
过滤。所以当查询所有的blog db.Blogs.ToList()
时,会产生以下sql:
SELECT [b].[BlogId], [b].[Name], [b].[Url]
FROM [Blogs] AS [b]
WHERE (
SELECT COUNT(*)
FROM [Posts] AS [p]
WHERE ([p].[Title] LIKE N'%fish%') AND ([b].[BlogId] = [p].[BlogId])) > 0
结果只会有一个Blog实例返回。如果将上述的b.Posts.Count > 0
改为b.Url.Contains("com")
,因为过滤链被打断了,所以会返回两个Blog实例。
注意:ef目前无法检测过滤链,所以定义过滤的时候需要小心避免出现死循环。
6.2 一些可能导致结果异常的查询骚操作
- 配置关系时设置了
IsRequired
我们对Blog
实体配置了过滤而且使用了IsRequried
方法要求两个实体间必须存在关联。
modelBuilder.Entity<Blog>().HasMany(b => b.Posts).WithOne(p => p.Blog).IsRequired();
modelBuilder.Entity<Blog>().HasQueryFilter(b => b.Url.Contains("fish"));
考虑有以下代码:
var allPosts = db.Posts.ToList();
var allPostsWithBlogsIncluded = db.Posts.Include(p => p.Blog).ToList();
第一行代码返回6个结果(默认情况不会Include
),第二行返回3个结果。原因是第二行代码使用Include
方法加载Blog
实体数据,会应用Blog
实体上的过滤,实际产生的sql用的是inner join
。
如果想让第二行代码也返回6条数据,将上面的代码改为.IsRequired(false)
(默认情况下就是false
),此时产生的sql是left join
.
在设置了IsRequeird
的情况下,想让第一行代码也返回3条数据,则:
modelBuilder.Entity<Blog>().HasMany(b => b.Posts).WithOne(p => p.Blog).IsRequired();
modelBuilder.Entity<Blog>().HasQueryFilter(b => b.Url.Contains("fish"));
//对Post应用过滤,也会链式启用对Blog的过滤
modelBuilder.Entity<Post>().HasQueryFilter(p => p.Blog.Url.Contains("fish"));
这个
IsRequried
的行为有点难以捉摸,在使用之前请多多搜索用法https://docs.microsoft.com/en-us/ef/core/querying/filters#accessing-entity-with-query-filter-using-required-navigation,避免掉到坑里。
6.3 禁用全局过滤
blogs = db.Blogs
.Include(b => b.Posts)
.IgnoreQueryFilters()
.ToList();
这样即使设置了Post
实体的全局过滤也不会生效。
7. 查询时属性值为null时的处理
因为sql数据库中等于/不等于里的null是单独拿出来比较的,与clr的等于/不等于的比较不一样。所以如果某些属性值为null,则翻译成sql时会进行一定的处理。考虑有以下数据类型和查询:
public class NullSemanticsEntity
{
public int Id { get; set; }
public int Int { get; set; }
public int? NullableInt { get; set; }
public string String1 { get; set; }
public string String2 { get; set; }
}
//不涉及到null值处理,生成的sql直接就是(where Id=Int)即可
var query1 = context.Entities.Where(e => e.Id == e.Int);
//涉及到null处理,但是使用的是==运算,所以生成(where Id=NullableInt)
var query2 = context.Entities.Where(e => e.Id == e.NullableInt);
//涉及到null处理。生成(WHERE ([e].[Id] <> [e].[NullableInt]) OR [e].[NullableInt] IS NULL)
var query3 = context.Entities.Where(e => e.Id != e.NullableInt);
//null处理。WHERE ([e].[String1] = [e].[String2]) OR ([e].[String1] IS NULL AND [e].[String2] IS NULL)
var query4 = context.Entities.Where(e => e.String1 == e.String2);
//null处理。WHERE (([e].[String1] <> [e].[String2]) OR ([e].[String1] IS NULL OR [e].[String2] IS NULL)) AND ([e].[String1] IS NOT NULL OR [e].[String2] IS NOT NULL)
var query5 = context.Entities.Where(e => e.String1 != e.String2);
7.1 编写高性能的查询
- 非null的比较比可为null的比较要快,所以尽量将实体的属性上填写
[Required]
特性。针对上述query4生成的sql变为WHERE ([e].[String1] = [e].[String2])
- 尽量检查是否相等(
==
)避免检查不相等(!=
)。因为==
有时不用区分是否为null。 - 查询时显示指定某些属性不能为
null
,下面第二行代码要快于第一行:
var query1 = context.Entities.Where(e => e.String1 != e.String2 || e.String1.Length == e.String2.Length);
var query2 = context.Entities.Where(
e => e.String1 != null && e.String2 != null && (e.String1 != e.String2 || e.String1.Length == e.String2.Length));