国产一级片内射视频播放蘑菇,大香蕉伊人在线视屏,最近中文字幕免费mv第一季歌词強上 ,国产91在线视频,久久艹影院,黄色操逼网,肏逼免费视频,无码翔田千里88A∨

在這篇文章中，我將重點介紹 EF Core 6 中 LINQ 查詢功能的增強。

這是 EF Core 6 新功能匯總的第三篇文章：

EF Core 6 新功能匯總（一）
EF Core 6 新功能匯總（二）
EF Core 6 新功能匯總（三）

1對 GroupBy 查詢的更好支持

EF Core 6.0 對 GroupBy 查詢有更好的支持。

翻譯 GroupBy 后面的 FirstOrDefault
在 GroupBy 之后使用 ThenBy
支持從一個組中選擇前 N 個結(jié)果

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 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]

2三四個參數(shù)的 String.Concat 翻譯

以前 EF Core 翻譯 string.Concat 時只有兩個參數(shù)。EF Core 6.0 支持翻譯三個和四個參數(shù)的 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 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

3EF.Functions.FreeText 支持二進制列

以前，盡管 SQL FreeText 函數(shù)支持二進制列，但你不能在二進制列上使用 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 Posts { get; set; }
    protected?override?void?OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .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"."Name", "p"."PhoneNumber"
FROM "People" AS "p"
WHERE?CAST("p"."PhoneNumber" AS TEXT) LIKE?'%368%'

4EF.Functions.Random

EF Core 6.0 引入了一個新的 EF.Functions.Random 方法。它映射了 SQL 函數(shù) RAND()。已經(jīng)實現(xiàn)了對 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 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)

5改進了 SQL Server 的 IsNullOrWhitespace 的翻譯

以前，EF Core 將 string.IsNullOrWhiteSpace 翻譯成在判斷前將值進行 trim 操作。EF Core 6.0 已經(jīng)不這么做了。

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 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'')

現(xiàn)在翻譯的 SQL：

SELECT [e].[Id], [e].[Property]
FROM [Entities] AS[e]
WHERE [e].[Property] IS?NULL?OR ([e].[Property] = N'')

6為內(nèi)存數(shù)據(jù)庫定義查詢

在 EF Core 6.0 中，你可以通過一個新的方法 ToInMemoryQuery 來定義一個針對內(nèi)存數(shù)據(jù)庫的查詢。這對于創(chuàng)建內(nèi)存數(shù)據(jù)庫的視圖是最有用的。

using?var context = new ExampleContext();
var blogEn = new Blog
{
    Title = "All about .NET",
    Language = "English",
    Posts = new List
        {
            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
        {
            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 Posts { get; set; }
}
class?PostsByLanguage
{
    public?string Language { get; set; }
    public?int PostCount { get; set; }
}
class?ExampleContext : DbContext
{
    public DbSet Posts { get; set; }
    public DbSet Blogs { get; set; }
    public DbSet PostsByLanguages { get; set; }
    protected?override?void?OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity()
            .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單參數(shù)的 Substring 翻譯

以前，EF Core 只翻譯有兩個參數(shù)的 string.Substring 重載。EF Core 6.0 支持翻譯單個參數(shù)的 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 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]

8非導(dǎo)航集合的分割查詢

EF Core 支持將一個 LINQ 查詢拆分成多個 SQL 查詢。EF Core 6.0 可以分割一個 LINQ 查詢，其中非導(dǎo)航集合屬性包含在查詢投影中。

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();
}
class?Post
{
    public?int Id { get; set; }
    public?string Title { get; set; }
    public Blog Blog { get; set; }
}
class?ExampleContext : DbContext
{
    public DbSet Blogs { get; set; }
    public DbSet Posts { get; set; }
    protected?override?void?OnConfiguring(DbContextOptionsBuilder options)
        => options
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6SplitQueries");
}

單個 SQL 查詢（不用 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]

多個 SQL 查詢（使用了 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刪除最后的 ORDER BY 子句

當(dāng)連接相關(guān)實體時，EF Core 添加了 ORDER BY 子句，以確保給定實體的所有相關(guān)實體被分組。然而，最后一個子句并不是必須的，而且會對性能產(chǎn)生影響。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 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 Blogs { get; set; }
    public DbSet 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]

10用文件名和行號標(biāo)記查詢

從 EF Core 2.2 開始，你可以給你的查詢添加一個標(biāo)簽，以達到更好的調(diào)試目的。EF Core 6.0 更進一步，現(xiàn)在你可以用 LINQ 代碼的文件名和行號來標(biāo)記查詢。

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 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]

11自有可選從屬關(guān)系處理

EF Core 6.0 改變了一些對自有可選從屬關(guān)系的處理。當(dāng)一個模型有自己的可選從屬關(guān)系時，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 People { get; set; }
    protected?override?void?OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity()
            .OwnsOne(p => p.Address);
    }
    protected?override?void?OnConfiguring(DbContextOptionsBuilder options)
        => options
        .EnableSensitiveDataLogging()
        .LogTo(Console.WriteLine)
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6OwnedDependentHandling");
}

警告日志：

當(dāng)你有嵌套自有可選從屬關(guān)系時，EF Core 將不允許創(chuàng)建模型。

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 People { get; set; }
   protected?override?void?OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder
           .Entity()
           .OwnsOne(p => p.ContactInfo)
           .OwnsOne(p => p.Address);
   }
   protected?override?void?OnConfiguring(DbContextOptionsBuilder options)
       => options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6OwnedDependentHandling");
}

創(chuàng)建模型后將會拋出異常。

這些變化迫使你避免這種情況。你可以通過以下方式解決這些問題。

使從屬關(guān)系成為必需的。
在從屬關(guān)系中至少有一個必需屬性。
為可選的從屬關(guān)系創(chuàng)建自己的表，而不是與主體共享它們。

12結(jié)尾

本文所有代碼示例都可以在我的 GitHub 中找到：

https://github.com/okyrylchuk/dotnet6_features/tree/main/EF%20Core%206#linq-query-enhancements

原文：bit.ly/32DqXnu
作者：Oleg Kyrylchuk
翻譯：精致碼農(nóng)

EF Core 6 新功能匯總（三）

1對 GroupBy 查詢的更好支持

2三四個參數(shù)的 String.Concat 翻譯

3EF.Functions.FreeText 支持二進制列

4EF.Functions.Random

5改進了 SQL Server 的 IsNullOrWhitespace 的翻譯

6為內(nèi)存數(shù)據(jù)庫定義查詢

7單參數(shù)的 Substring 翻譯

8非導(dǎo)航集合的分割查詢

9刪除最后的 ORDER BY 子句

10用文件名和行號標(biāo)記查詢

11自有可選從屬關(guān)系處理

12結(jié)尾