[译] 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 查询增强功能。

更好地支持 GroupBy 查询

EF Core 6.0 更好地支持 GroupBy 查询。

在组上翻译 GroupBy 后跟 FirstOrDefault
在 GroupBy 之后展开导航
支持从组中选择前 N 个结果

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

翻译后的 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]

带有 3 个和 4 个参数的 String.Concat 翻译

以前 EF Core仅使用两个参数翻译string.Concat。EF Core 6.0使用3和4个参数转换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");
}

翻译后的 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

EF.Functions.FreeText 支持二进制列

以前，您不能在二进制列上使用EF.Functions.FreeText方法，尽管 SQL FreeText 函数支持它们。EF Core 6.0 解决了这个问题。

这里给一个官方链接防止大家不理解

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/freetext-transact-sql?view=sql-server-ver15

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

翻译后的 SQL

SELECT[p].[Id], [p].[Content], [p].[Title]
FROM[Posts] AS[p]
WHERE FREETEXT([p].[Content], N'Searching text')

在 SQLite 上翻译 ToString

从 EF Core 5.0 开始，添加了 SQL Server 的ToString翻译。EF Core 6.0 还为 SQLite 数据库提供程序转换ToString 。它对非字符串列的文本搜索很有帮助

using var context = new ExampleContext();
var query = context.People
    .Where(u => EF.Functions.Like(u.PhoneNumber.ToString(), "%368%"))
    .ToQueryString();
Console.WriteLine(query);

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public long PhoneNumber { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Person> People { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=:memory:");
}

翻译后的 SQL：

SELECT "p"."Id", "p"."Name", "p"."PhoneNumber"
FROM "People" AS "p"
WHERE CAST("p"."PhoneNumber" AS TEXT) LIKE '%368%'

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

翻译后的 SQL：

SELECT[p].[Id], [p].[Rating], [p].[Title]
FROM[Posts] AS[p]
WHERE[p].[Rating] = (CAST((RAND() * 5.0E0) AS int) + 1)

结语

联系作者：加群：867095512 @MrChuJiu