In this article, I will focus on the enhancements to LINQ query capabilities in EF Core 6.
This is the third article in a summary of the new features of EF Core 6:
1 Better support for GroupBy queries
EF Core 6.0 对 GroupBy 查询有更好的支持。
- 翻译
GroupBy后面的FirstOrDefault - 在
GroupBy之后使用ThenBy - Support selecting the top N results from a group
using var context = new ExampleContext();
var query = context.People
.GroupBy(p => p.FirstName)
.Select(g => g.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.FirstOrDefault())
.ToQueryString();
Console.WriteLine(query);
class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public int LastName { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6GroupBy");
}
Translated SQL:
SELECT[t0].[Id], [t0].[FirstName], [t0].[LastName]
FROM (
SELECT[p].[FirstName]
FROM [People] AS [p]
GROUP BY [p].[FirstName]
) AS[t]
LEFT JOIN(
SELECT[t1].[Id], [t1].[FirstName], [t1].[LastName]
FROM (
SELECT[p0].[Id], [p0].[FirstName], [p0].[LastName],
ROW_NUMBER() OVER(PARTITION BY [p0].[FirstName]
ORDER BY [p0].[FirstName], [p0].[LastName]) AS[row]
FROM[People] AS[p0]
) AS[t1]
WHERE[t1].[row] <= 1
) AS[t0] ON[t].[FirstName] = [t0].[FirstName]
2 String.Concat translation of three or four parameters
以前 EF Core 翻译 string.Concat 时只有两个参数。EF Core 6.0 支持翻译三个和四个参数的 string.Concat。
using var context = new ExampleContext();
string fullName = "SamuelLanghorneClemens";
var query = context.Blogs
.Where(b => string.Concat(b.FirstName, b.MiddleName, b.LastName) == fullName)
.ToQueryString();
Console.WriteLine(query);
class Blog
{
public int Id { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6StringConcat");
}
Translated SQL:
DECLARE @__fullName_0 nvarchar(4000) = N'SamuelLanghorneClemens';
SELECT[b].[Id], [b].[FirstName], [b].[LastName], [b].[MiddleName]
FROM[Blogs] AS[b]
WHERE(COALESCE([b].[FirstName], N'') + (COALESCE([b].[MiddleName], N'') +COALESCE([b].[LastName], N ''))) = @__fullName_0
3 EF. Functions. FreeText supports binary columns
以前，尽管 SQL FreeText 函数支持二进制列，但你不能在二进制列上使用 EF.Functions.FreeText 方法。EF Core 6.0 解决了这个问题。
using var context = new ExampleContext();
var query = context.Posts
.Where(p => EF.Functions.FreeText(EF.Property<string>(p, "Content"), "Searching text"))
.ToQueryString();
Console.WriteLine(query);
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public byte[] Content { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Post> Posts { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Post>()
.Property(x => x.Content)
.HasColumnType("varbinary(max)");
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6FlexibleTextSearch");
}
Translated SQL:
SELECT "p"."Id", "p"."Name", "p"."PhoneNumber"
FROM "People" AS "p"
WHERE CAST("p"."PhoneNumber" AS TEXT) LIKE '%368%'
4 EF.Functions.Random
EF Core 6.0 引入了一个新的 EF.Functions.Random 方法。它映射了 SQL 函数 RAND()。已经实现了对 SQL Server、SQLite 和 Cosmos 的翻译。
using var context = new ExampleContext();
var query = context.Posts
.Where(p => p.Rating == (int)(EF.Functions.Random() * 5.0) + 1)
.ToQueryString();
Console.WriteLine(query);
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public int Rating { get; set; }
}
class ExampleContext : DbContext
{
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6Random");
}
Translated SQL:
SELECT[p].[Id], [p].[Rating], [p].[Title]
FROM[Posts] AS[p]
WHERE[p].[Rating] = (CAST((RAND() * 5.0E0) AS int) + 1)
5 Improved translation of IsNullOrWhitespace for SQL Server
以前，EF Core 将 string.IsNullOrWhiteSpace 翻译成在判断前将值进行 trim 操作。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");
}
Previously translated SQL:
SELECT [e].[Id], [e].[Property]
FROM [Entities] AS[e]
WHERE [e].[Property] IS NULL OR (LTRIM(RTRIM([e].[Property])) = N'')
SQL now translated:
SELECT [e].[Id], [e].[Property]
FROM [Entities] AS[e]
WHERE [e].[Property] IS NULL OR ([e].[Property] = N'')
6 Defining queries for in-memory databases
在 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");
}
7 Single-parameter Substring Translation
以前，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");
}
Translated SQL:
SELECT SUBSTRING([p].[Name], 1 + 1, LEN([p].[Name])) AS [Name]
FROM [People] AS [p]
8 Split queries for non-navigational sets
EF Core supports splitting a LINQ query into multiple SQL queries. EF Core 6.0 can split a LINQ query with non-navigation collection attributes included in the query projection.
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");
}
Single SQL query (without AsSplitQuery):
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]
Multiple SQL queries (using AsSplitQuery):
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]
9 Delete the last ORDER BY clause
When connecting related entities, EF Core adds an ORDER BY clause to ensure that all related entities for a given entity are grouped. However, the last clause is not required and can have a performance impact. EF Core 6.0 removed it.
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");
}
SQL translated by EF Core 5.0:
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]
SQL translated by EF Core 6.0:
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]
10 Mark queries with file name and line number
Starting with EF Core 2.2, you can add a label to your queries for better debugging purposes. EF Core 6.0 takes it one step further and now you can mark queries with the file name and line number of LINQ code.
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");
}
Translated 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]
11 Own optional affiliation processing
EF Core 6.0 changes some of the handling of optional affiliations. When a model has its own optional affiliations, EF Core warns you of all missing attributes when you save it.
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");
}
Warning Log:
EF Core will not allow you to create models when you have nested optional affiliations.
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");
}
Exceptions will be thrown after the model is created.
These changes force you to avoid this situation. You can solve these problems in the following ways.
- Make affiliation necessary.
- There is at least one required attribute in the affiliation.
- Create your own tables for optional dependencies rather than sharing them with the principal.
End of 12
本文所有代码示例都可以在我的 GitHub 中找到：https://github.com/okyrylchuk/dotnet6_features/tree/main/EF%20Core%206#linq-query-enhancements
Original: https://www.example.com
Author: Oleg Kyrylchuk
Translation: Exquisite Code Farmer