In this article, I will focus on the enhancements to LINQ query features in EF Core 6.
This is the third article in the series summarizing new features in EF Core 6:
- EF Core 6 New Features Summary (Part 1)
- EF Core 6 New Features Summary (Part 2)
- EF Core 6 New Features Summary (Part 3)
1 Better support for GroupBy queries
EF Core 6.0 provides better support for GroupBy queries.
- Translates
FirstOrDefaultafterGroupBy - Supports
ThenByafterGroupBy - Supports selecting 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 Translation of String.Concat with three or four arguments
Previously, EF Core only translated string.Concat with two arguments. EF Core 6.0 supports translating string.Concat with three and four arguments.
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
Previously, even though the SQL FreeText function supports binary columns, you could not use the EF.Functions.FreeText method on binary columns. EF Core 6.0 addresses this issue.
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 introduces a new EF.Functions.Random method. It maps to the SQL function RAND(). Translations have been implemented for SQL Server, SQLite, and 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
Previously, EF Core translated string.IsNullOrWhiteSpace by trimming the value before checking. EF Core 6.0 no longer does this.
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 queries for in-memory database
In EF Core 6.0, you can define a query for an in-memory database using a new method ToInMemoryQuery. This is most useful for creating views on an in-memory database.
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 Translation of single-argument Substring
Previously, EF Core only translated the two-argument overload of string.Substring. EF Core 6.0 supports translating the single-argument 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-navigation collections
EF Core supports splitting a LINQ query into multiple SQL queries. EF Core 6.0 can split a LINQ query where non-navigation collection properties 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 (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 (with 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 Remove trailing ORDER BY clause
When joining related entities, EF Core adds an ORDER BY clause to ensure all related entities for a given entity are grouped. However, the last clause is not necessary and can impact performance. EF Core 6.0 removes 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 Tag queries with filename and line number
Starting from EF Core 2.2, you could add a tag to your query for better debugging. EF Core 6.0 goes further: now you can tag queries with the filename and line number of the 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 Owned optional dependent handling
EF Core 6.0 changes some handling of owned optional dependents. When a model has its own optional dependent, EF Core will warn you about all missing properties 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 owned optional dependents, EF Core will not allow creating the model.
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 creating the model.
These changes force you to avoid this situation. You can resolve these issues by:
- Making the dependent required.
- Having at least one required property in the dependent.
- Creating your own table for the optional dependent instead of sharing it with the principal.
12 Conclusion
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
Original: https://blog.okyrylchuk.dev/linq-enhancements-in-entity-framework-core-6
Author: Oleg Kyrylchuk
Translation: refined coder