[译] LINQ Enhancements in Entity Framework Core 6 - 下
介绍
接下来我将给大家重点介绍一下.Net 6 之后的一些新的变更，文章都是来自于外国大佬的文章，我这边进行一个翻译，并加上一些自己的理解和解释。
源作者链接：https://blog.okyrylchuk.dev/linq-enhancements-in-entity-framework-core-6
正文
在这篇文章中，我将重点介绍 Entity Framework Core 6 中的 LINQ 查询增强功能。
改进了 IsNullOrWhitespace 的 SQL Server 翻译
以前，EF Core 在检查它是否为空之前将string.IsNullOrWhiteSpace转换为值的修整。EF Core 6.0 不再进行修剪。
using var context = new ExampleContext();
var query = context.Entities
.Where(e => string.IsNullOrWhiteSpace(e.Property))
.ToQueryString();
Console.WriteLine(query);
class Entity
{
public int Id { get; set; }
public string Property { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Entity> Entities { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6IsNullOrWhiteSpace");
}
之前翻译的SQL：
SELECT [e].[Id], [e].[Property]
FROM [Entities] AS[e]
WHERE [e].[Property] IS NULL OR (LTRIM(RTRIM([e].[Property])) = N'')
现在翻译的SQL：
SELECT [e].[Id], [e].[Property]
FROM [Entities] AS[e]
WHERE [e].[Property] IS NULL OR ([e].[Property] = N'')
为内存提供程序定义查询
在 EF Core 6.0 中，您可以使用新方法ToInMemoryQuery为给定类型定义针对内存数据库的查询。这对于在内存数据库上创建等效视图最有用。
using var context = new ExampleContext();
var blogEn = new Blog
{
Title = "All about .NET",
Language = "English",
Posts = new List<Post>
{
new Post { Title = "Post one", Content = "Some content" },
new Post { Title = "Post two", Content = "Some content" }
}
};
var blogPl = new Blog
{
Title = "Wszystko o .NET",
Language = "Polish",
Posts = new List<Post>
{
new Post { Title = "Pierwszy post", Content = "Treść" }
}
};
context.Blogs.Add(blogEn);
context.Blogs.Add(blogPl);
await context.SaveChangesAsync();
var postsByLanguages = context.PostsByLanguages.ToList();
postsByLanguages
.ForEach(p => Console.WriteLine($"{p.PostCount} posts in {p.Language}"));
// Output:
// 2 posts in English
// 1 posts in Polish
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
}
class Blog
{
public int Id { get; set; }
public string Title { get; set; }
public string Language { get; set; }
public ICollection<Post> Posts { get; set; }
}
class PostsByLanguage
{
public string Language { get; set; }
public int PostCount { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Post> Posts { get; set; }
public DbSet<Blog> Blogs { get; set; }
public DbSet<PostsByLanguage> PostsByLanguages { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<PostsByLanguage>()
.HasNoKey()
.ToInMemoryQuery(
() => Blogs
.GroupBy(c => c.Language)
.Select(
g =>
new PostsByLanguage
{
Language = g.Key,
PostCount = g.Sum(b => b.Posts.Count)
}));
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseInMemoryDatabase("ToInMemoryQuery");
}
带有单个参数的子字符串翻译
以前 EF Core 只翻译了带有两个参数的string.Substring重载。EF Core 6.0使用单个参数转换string.Substring 。
using var context = new ExampleContext();
context.People.Add(new Person { Name = "John" });
context.People.Add(new Person { Name = "Bred" });
context.People.Add(new Person { Name = "Ron" });
await context.SaveChangesAsync();
var result = await context.People
.Select(a => new { Name = a.Name.Substring(1) })
.ToListAsync();
result.ForEach(p => Console.WriteLine(p.Name));
// Output:
// ohn
// red
// on
class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6Substring");
}
翻译后的 SQL：
SELECT SUBSTRING([p].[Name], 1 + 1, LEN([p].[Name])) AS [Name]
FROM [People] AS [p]
非导航集合的拆分查询
EF Core 支持将单个 LINQ 查询拆分为多个 SQL 查询。EF Core 6.0 可以拆分查询投影中包含非导航集合的 LINQ 查询.
AsSplitQuery 的解释
https://docs.microsoft.com/zh-cn/dotnet/api/microsoft.entityframeworkcore.relationalqueryableextensions.assplitquery?view=efcore-5.0
using var context = new ExampleContext();
var blog = new Blog { Name = ".NET Blog"};
blog.Posts.Add(new Post { Title = "First .NET post" });
blog.Posts.Add(new Post { Title = "Second Java post" });
blog.Posts.Add(new Post { Title = "Third .NET post" });
context.Blogs.Add(blog);
await context.SaveChangesAsync();
var blogsWithDotnetPosts = await context.Blogs
.Select(b => new
{
b,
Posts = b.Posts.Where(p => p.Title.Contains(".NET")),
})
.AsSplitQuery()
.ToListAsync();
class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts { get; set; } = new List<Post>();
}
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public Blog Blog { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6SplitQueries");
}
单条 SQL 查询：
SELECT [b].[Id], [b].[Name], [t].[BlogId], [t].[Title]
FROM [Blogs] AS [b]
LEFT JOIN (
SELECT [p].[Id], [p].[BlogId], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[Title] LIKE N'%.NET%'
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id]
多个 SQL 查询：
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
ORDER BY [b].[Id]
SELECT [t].[Id], [t].[BlogId], [t].[Title], [b].[Id]
FROM [Blogs] AS [b]
INNER JOIN (
SELECT [p].[Id], [p].[BlogId], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[Title] LIKE N'%.NET%'
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id]
删除 Last ORDER BY 子句
加入相关实体时，EF Core 添加 ORDER BY 子句以确保给定实体的所有相关实体都分组在一起。但是，最后一个子句不是必需的，可能会对性能产生影响。EF Core 6.0 将其删除。
using var context = new ExampleContext();
var query = context.Blogs
.Include(b => b.Posts.Where(p => p.Rating > 3))
.ToQueryString();
Console.WriteLine(query);
class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts { get; set; }
}
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public int Rating { get; set; }
public Blog Blog { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6RemoveLastOrderByClause");
}
EF Core 5.0 翻译的 SQL：
SELECT [b].[Id], [b].[Name], [t].[Id], [t].[BlogId], [t].[Rating], [t].[Title]
FROM [Blogs] AS [b]
LEFT JOIN (
SELECT [p].[Id], [p].[BlogId], [p].[Rating], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[Rating] > 3
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id], [t].[Id]
EF Core 6.0 翻译的 SQL：
SELECT [b].[Id], [b].[Name], [t].[Id], [t].[BlogId], [t].[Rating], [t].[Title]
FROM [Blogs] AS [b]
LEFT JOIN (
SELECT [p].[Id], [p].[BlogId], [p].[Rating], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[Rating] > 3
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id]
使用文件名和行号标记查询
从 EF Core 2.2 开始，您可以将标签添加到查询中以便更好地进行调试。EF Core 6.0 更进一步，现在您可以使用 LINQ 代码的文件名和行号标记查询。
using var context = new ExampleContext();
var query = context.Blogs
.TagWithCallSite()
.OrderBy(b => b.CreationDate)
.Take(10)
.ToQueryString();
Console.WriteLine(query);
class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime CreationDate { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6TagWithCallSite");
}
翻译后的 SQL：
DECLARE @__p_0 int = 10;
--File: D:\EFCore6\TagWithCallSite\TagWithCallSite\Program.cs:6
SELECT TOP(@__p_0) [b].[Id], [b].[CreationDate], [b].[Name]
FROM[Blogs] AS[b]
ORDER BY[b].[CreationDate]
对拥有的可选依赖处理的更改
EF Core 6.0 为拥有的可选依赖处理引入了一些更改。当模型拥有可选依赖项时，EF Core 会在您保存它时发出警告，其中包含所有缺失的属性。
using var context = new ExampleContext();
var person = new Person
{
FirstName = "Oleg",
LastName = "Kyrylchuk",
Address = new Address()
};
context.People.Add(person);
await context.SaveChangesAsync();
class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Address Address { get; set; }
}
class Address
{
public string City { get; set; }
public string Street { get; set; }
public string PostalCode { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Person>()
.OwnsOne(p => p.Address);
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.EnableSensitiveDataLogging()
.LogTo(Console.WriteLine)
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6OwnedDependentHandling");
}
日志中的警告：
The entity of type Address"with primary key values (Personid: -2147482647) is an optional dependent using table sharin. re. Update)The entitt have any property with a non-default value to identify whether the entity exists. This means that whent is queried no object instance will be creatstead of an instanith all properties set to default values. Any nesteddependents will also be lost. Either dont save any instance with only default values or mark the incoming navigation as required in the model
当您嵌套了拥有的可选依赖项时，EF Core 将根本不允许创建模型。
using var context = new ExampleContext();
var person = new Person
{
FirstName = "Oleg",
LastName = "Kyrylchuk",
ContactInfo = new ContactInfo()
};
context.People.Add(person);
await context.SaveChangesAsync();
class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public ContactInfo ContactInfo { get; set; }
}
class ContactInfo
{
public string Phone { get; set; }
public Address Address { get; set; }
}
class Address
{
public string City { get; set; }
public string Street { get; set; }
public string PostalCode { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Person>()
.OwnsOne(p => p.ContactInfo)
.OwnsOne(p => p.Address);
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6OwnedDependentHandling");
}
Entity type Contactinfo is an optIf all nullable properties contain a null value in database then an objectinstance wont be created in the query causing nested dependent s values to be lost Add a required property to create instances with null valuesfor other properties or mark the incoming navigation as required to always create an instance
这些更改迫使您避免此类情况。您可以通过以下方式修复它们：
使依赖成为必需，
在依赖项中创建至少一个必需属性，
为可选依赖项创建自己的表，而不是与主体共享它们。
结语
联系作者：加群：867095512 @MrChuJiu
