In this article, I will focus on the enhancement of LINQ query function in EF Core 6.
This is the third article in the summary of new features of EF Core 6:
-
Summary of new functions of EF Core 6 (III)
1. Better support for GroupBy query
EF Core 6.0 has better support for group by query.
-
Translate "FirstOrDefault" after "GroupBy"
-
Use 'ThenBy' after 'GroupBy'
-
Supports selecting the first 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 with three or four parameters Concat translation
Previously, EF Core translated {string Concat has only two parameters. EF Core 6.0 supports the translation of three and four parameter {strings 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
3EF.Functions.FreeText supports binary columns
Previously, although the SQL # FreeText # function supported binary columns, you could not use # EF on binary columns Functions. FreeText method. EF Core 6.0 solves this problem.
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%'4EF.Functions.Random
EF Core 6.0 introduces a new EF Functions. Random method. It maps the SQL function RAND(). The translation of SQL Server, SQLite and Cosmos has been realized.
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 the translation of IsNullOrWhitespace of SQL Server
Previously, the EF Core used to be a string Isnullorwhitespace translates to "trim" the value before judgment. EF Core 6.0 doesn't do that anymore.
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'')
Now translated SQL:
SELECT [e].[Id], [e].[Property] FROM [Entities] AS[e] WHERE [e].[Property] IS NULL OR ([e].[Property] = N'')
6 define query for memory database
In EF Core 6.0, you can define a query for an in memory database through a new method, ToInMemoryQuery. This is most useful for creating views of in memory databases.
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 Substring translation of single parameter
Previously, EF Core only translated {string with two parameters Substring overload. EF Core 6.0 supports the translation of {string of a single parameter 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 query of non navigation set
EF Core supports splitting a LINQ query into multiple SQL queries. EF Core 6.0 can split a LINQ query, in which non navigation collection attributes are 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 (not 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 of a given entity are grouped. However, the last clause is not necessary and will have an impact on performance. 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 query with file name and line number
Starting with EF Core 2.2, you can add a label to your query for better debugging purposes. EF Core 6.0 goes further. Now you can mark the query 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 self owned optional dependency processing
EF Core 6.0 has changed some of the processing of its own optional dependencies. When a model has its own optional dependencies, EF Core will warn 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:
When you have nested optional dependencies, EF Core will not allow you to create models.
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");
}An exception 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 dependency necessary.
-
There is at least one required attribute in the dependency.
-
Create your own tables for optional dependencies instead of sharing them with principals.
12 end
All code examples in this article can be found in my GitHub:
https://github.com/okyrylchuk/dotnet6_features/tree/main/EF%20Core%206#linq-query-enhancements