Announcing Entity Framework Core EFCore 5.0 Preview 6

Jeremy Likness

Jeremy

Today, the Entity Framework Core team announces the sixth preview release of EF Core 5.0. This release includes split queries for related collections, a new “index” attribute, improved exceptions related to query translations, IP address mapping, exposing transaction id for correlation, and much more.

Prerequisites

EF Core 5.0 will not run on .NET Standard 2.0 platforms, including .NET Framework.

The previews of EF Core 5.0 require .NET Standard 2.1. This means that EF Core 5.0 will run on .NET Core 3.1 and does not require .NET 5. To summarize: EF Core 5.0 runs on platforms that support .NET Standard 2.1.

The plan is to maintain .NET Standard 2.1 compatibility through the final release.

Be a part of .NET 5

The .NET documentation team is reorganizing .NET content to better match the workloads you build with .NET. This includes a new .NET Data landing page that will link out to data-related topics ranging from EF Core to APIs, Big Data, and Machine learning. The planning and execution will be done completely in the open on GitHub. This is your opportunity to help shape the hierarchy and content to best fit your needs as a .NET developer. We look forward to your contributions!


How to get EF Core 5.0 previews

EF Core is distributed exclusively as a set of NuGet packages.
For example, to add the SQL Server provider to your project, you can use the following command using the dotnet tool:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 5.0.0-preview.6.20312.4

This following table links to the preview 6 versions of the EF Core packages and describes what they are used for.

PackagePurpose
Microsoft.EntityFrameworkCoreThe main EF Core package that is independent of specific database providers
Microsoft.EntityFrameworkCore.SqlServerDatabase provider for Microsoft SQL Server and SQL Azure
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuiteSQL Server support for spatial types
Microsoft.EntityFrameworkCore.SqliteDatabase provider for SQLite that includes the native binary for the database engine
Microsoft.EntityFrameworkCore.Sqlite.CoreDatabase provider for SQLite without a packaged native binary
Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuiteSQLite support for spatial types
Microsoft.EntityFrameworkCore.CosmosDatabase provider for Azure Cosmos DB
Microsoft.EntityFrameworkCore.InMemoryThe in-memory database provider
Microsoft.EntityFrameworkCore.ToolsEF Core PowerShell commands for the Visual Studio Package Manager Console; use this to integrate tools like scaffolding and migrations with Visual Studio
Microsoft.EntityFrameworkCore.DesignShared design-time components for EF Core tools
Microsoft.EntityFrameworkCore.ProxiesLazy-loading and change-tracking proxies
Microsoft.EntityFrameworkCore.AbstractionsDecoupled EF Core abstractions; use this for features like extended data annotations defined by EF Core
Microsoft.EntityFrameworkCore.RelationalShared EF Core components for relational database providers
Microsoft.EntityFrameworkCore.AnalyzersC# analyzers for EF Core

We also published the 5.0 preview 6 release of the Microsoft.Data.Sqlite.Core provider for ADO.NET.

Installing the EF Core Command Line Interface (CLI)

As with EF Core 3.0 and 3.1, the EF Core CLI is no longer included in the .NET Core SDK. Before you can execute EF Core migration or scaffolding commands, you’ll have to install this package as either a global or local tool.

dotnet-ef

To install the preview tool globally, first uninstall any existing version with:

dotnet tool uninstall --global dotnet-ef

Then install with:

dotnet tool install --global dotnet-ef --version 5.0.0-preview.6.20312.4

It’s possible to use this new version of the EF Core CLI with projects that use older versions of the EF Core runtime.


What’s New in EF Core 5 Preview 6

We maintain documentation covering new features introduced into each preview.

Some of the highlights from preview 6 are called out below. This preview also includes several bug fixes.

Starting with EF Core 3.0, EF Core always generates a single SQL query for each LINQ query. This ensures consistency of the data returned within the constraints of the transaction mode in use. However, this can become very slow when the query uses Include or a projection to bring back multiple related collections.

EF Core 5.0 now allows a single LINQ query including related collections to be split into multiple SQL queries. This can significantly improve performance, but can result in inconsistency in the results returned if the data changes between the two queries. Transactions can be used to mitigate this, at least within the constraints of the transaction mode in use.

Split queries with Include

For example, consider a query that pulls in two levels of related collections using Include:

var artists = context.Artists
    .Include(e => e.Albums).ThenInclude(e => e.Tags)
    .ToList();

By default, EF Core will generate the following SQL when using the SQLite provider:

SELECT "a"."Id", "a"."Name", "t0"."Id", "t0"."ArtistId", "t0"."Title", "t0"."Id0", "t0"."AlbumId", "t0"."Name"
FROM "Artists" AS "a"
LEFT JOIN (
    SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "t"."Id" AS "Id0", "t"."AlbumId", "t"."Name"
    FROM "Album" AS "a0"
    LEFT JOIN "Tag" AS "t" ON "a0"."Id" = "t"."AlbumId"
) AS "t0" ON "a"."Id" = "t0"."ArtistId"
ORDER BY "a"."Id", "t0"."Id", "t0"."Id0"

The new AsSplitQuery API can be used to change this behavior. For example:

var artists = context.Artists
    .AsSplitQuery()
    .Include(e => e.Albums).ThenInclude(e => e.Tags)
    .ToList();

AsSplitQuery is available for all relational database providers and can be used anywhere in the query, just like AsNoTracking. EF Core will now generate the following three SQL queries:

SELECT "a"."Id", "a"."Name"
FROM "Artists" AS "a"
ORDER BY "a"."Id"

SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "a"."Id"
FROM "Artists" AS "a"
INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId"
ORDER BY "a"."Id", "a0"."Id"

SELECT "t"."Id", "t"."AlbumId", "t"."Name", "a"."Id", "a0"."Id"
FROM "Artists" AS "a"
INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId"
INNER JOIN "Tag" AS "t" ON "a0"."Id" = "t"."AlbumId"
ORDER BY "a"."Id", "a0"."Id"

All operations on the query root are supported. This includes OrderBy/Skip/Take, Join operations, FirstOrDefault and similar single result selecting operations.

Note that filtered Includes with OrderBy/Skip/Take are not supported in preview 6, but are available in the daily builds and will be included in preview 7.

Split queries with collection projections

AsSplitQuery can also be used when collections are loaded in projections. For example:

context.Artists
    .AsSplitQuery()
    .Select(e => new
    {
        Artist = e,
        Albums = e.Albums,
    }).ToList();

This LINQ query generates the following two SQL queries when using the SQLite provider:

SELECT "a"."Id", "a"."Name"
FROM "Artists" AS "a"
ORDER BY "a"."Id"

SELECT "a0"."Id", "a0"."ArtistId", "a0"."Title", "a"."Id"
FROM "Artists" AS "a"
INNER JOIN "Album" AS "a0" ON "a"."Id" = "a0"."ArtistId"
ORDER BY "a"."Id"

Note that only materialization of the collection is supported. Any composition after e.Albums in above case won’t result in a split query. Improvements in this area are tracked by #21234.

IndexAttribute

The new IndexAttribute can be placed on an entity type to specify an index for a single column. For example:

[Index(nameof(FullName), IsUnique = true)]
public class User
{
    public int Id { get; set; }

    [MaxLength(128)]
    public string FullName { get; set; }
}

For SQL Server, Migrations will then generate the following SQL:

CREATE UNIQUE INDEX [IX_Users_FullName]
    ON [Users] ([FullName])
    WHERE [FullName] IS NOT NULL;

IndexAttribute can also be used to specify an index spanning multiple columns. For example:

[Index(nameof(FirstName), nameof(LastName), IsUnique = true)]
public class User
{
    public int Id { get; set; }

    [MaxLength(64)]
    public string FirstName { get; set; }

    [MaxLength(64)]
    public string LastName { get; set; }
}

For SQL Server, this results in:

CREATE UNIQUE INDEX [IX_Users_FirstName_LastName]
    ON [Users] ([FirstName], [LastName])
    WHERE [FirstName] IS NOT NULL AND [LastName] IS NOT NULL;

Documentation is tracked by issue #2407.

Improved query translation exceptions

We are continuing to improve the exception messages generated when query translation fails. For example, this query uses the unmapped property IsSigned:

var artists = context.Artists.Where(e => e.IsSigned).ToList();

EF Core will throw the following exception indicating that translation failed because IsSigned is not mapped:

Unhandled exception. System.InvalidOperationException: The LINQ expression ‘DbSet()
.Where(a => a.IsSigned)’ could not be translated. Additional information: Translation of member ‘IsSigned’ on entity type ‘Artist’ failed. Possibly the specified member is not mapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Similarly, better exception messages are now generated when attempting to translate string comparisons with culture-dependent semantics. For example, this query attempts to use StringComparison.CurrentCulture:

var artists = context.Artists
    .Where(e => e.Name.Equals("The Unicorns", StringComparison.CurrentCulture))
    .ToList();

EF Core will now throw the following exception:

Unhandled exception. System.InvalidOperationException: The LINQ expression ‘DbSet()
.Where(a => a.Name.Equals(
value: “The Unicorns”,
comparisonType: CurrentCulture))’ could not be translated. Additional information: Translation of ‘string.Equals’ method which takes ‘StringComparison’ argument is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Specify transaction ID

This feature was contributed from the community by @Marusyk. Many thanks for the contribution!

EF Core exposes a transaction ID for correlation of transactions across calls. This is typically set by EF Core when a transaction is started. If the application starts the transaction instead, then this feature allows the application to explicitly set the transaction ID so it is correlated correctly everywhere it is used. For example:

using (context.Database.UseTransaction(myTransaction, myId))
{
   ...
}

IPAddress mapping

This feature was contributed from the community by @ralmsdeveloper. Many thanks for the contribution!

The standard .NET IPAddress class is now automatically mapped to a string column for databases that do not already have native support. For example, consider mapping this entity type:

public class Host
{
    public int Id { get; set; }
    public IPAddress Address { get; set; }
}

On SQL Server, this will result in Migrations creating the following table:

CREATE TABLE [Host] (
    [Id] int NOT NULL,
    [Address] nvarchar(45) NULL,
    CONSTRAINT [PK_Host] PRIMARY KEY ([Id]));

Entities can then be added in the normal way:

context.AddRange(
    new Host { Address = IPAddress.Parse("127.0.0.1")},
    new Host { Address = IPAddress.Parse("0000:0000:0000:0000:0000:0000:0000:0001")});

And the resulting SQL will insert the normalized IPv4 or IPv6 address:

Executed DbCommand (14ms) [Parameters=[@p0='1', @p1='127.0.0.1' (Size = 45), @p2='2', @p3='::1' (Size = 45)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Host] ([Id], [Address])
      VALUES (@p0, @p1), (@p2, @p3);

Exclude OnConfiguring when scaffolding

When a DbContext is scaffolded from an existing database, EF Core by default creates an OnConfiguring overload with a connection string so that the context is immediately usable. However, this is not useful if you already have a partial class with OnConfiguring, or if you are configuring the context some other way.

To address this, the scaffolding commands can now be instructed to ommit generation of OnConfiguring. For example:

dotnet ef dbcontext scaffold "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook" Microsoft.EntityFrameworkCore.SqlServer --no-onconfiguring

Or in the Package Manager Console:

Scaffold-DbContext 'Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook' Microsoft.EntityFrameworkCore.SqlServer -NoOnConfiguring

Note that we recommend using a named connection string and secure storage like User Secrets.

Translations for FirstOrDefault on strings

This feature was contributed from the community by @dvoreckyaa. Many thanks for the contribution!

FirstOrDefault and similar operators for characters in strings are now translated. For example, this LINQ query:

context.Customers.Where(c => c.ContactName.FirstOrDefault() == 'A').ToList();

Will be translated to the following SQL when using SQL Server:

SELECT [c].[Id], [c].[ContactName]
FROM [Customer] AS [c]
WHERE SUBSTRING([c].[ContactName], 1, 1) = N'A'

Simplify case blocks

EF Core now generates better queries with CASE blocks. For example, this LINQ query:

context.Weapons
    .OrderBy(w => w.Name.CompareTo("Marcus' Lancer") == 0)
    .ThenBy(w => w.Id)

Was on SQL Server formally translated to:

SELECT [w].[Id], [w].[AmmunitionType], [w].[IsAutomatic], [w].[Name], [w].[OwnerFullName], [w].[SynergyWithId]
FROM [Weapons] AS [w]
ORDER BY CASE
    WHEN (CASE
        WHEN [w].[Name] = N'Marcus'' Lancer' THEN 0
        WHEN [w].[Name] > N'Marcus'' Lancer' THEN 1
        WHEN [w].[Name] < N'Marcus'' Lancer' THEN -1
    END = 0) AND CASE
        WHEN [w].[Name] = N'Marcus'' Lancer' THEN 0
        WHEN [w].[Name] > N'Marcus'' Lancer' THEN 1
        WHEN [w].[Name] < N'Marcus'' Lancer' THEN -1
    END IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [w].[Id]");

But is now translated to:

SELECT [w].[Id], [w].[AmmunitionType], [w].[IsAutomatic], [w].[Name], [w].[OwnerFullName], [w].[SynergyWithId]
FROM [Weapons] AS [w]
ORDER BY CASE
    WHEN ([w].[Name] = N'Marcus'' Lancer') AND [w].[Name] IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [w].[Id]");

Daily builds

EF Core previews are aligned with .NET 5 previews. These previews tend to lag behind the latest work on EF Core. Consider using the daily builds instead to get the most up-to-date EF Core features and bug fixes.

As with the previews, the daily builds do not require .NET 5; they can be used with GA/RTM release of .NET Core 3.1.


The EF Core Community Standup

The EF Core team is now live streaming every other Wednesday at 10am Pacific Time, 1pm Eastern Time, or 17:00 UTC. Join the stream to ask questions about the EF Core topic of your choice, including the latest preview release.

Documentation and feedback

The starting point for all EF Core documentation is docs.microsoft.com/ef/.

Please file issues found and any other feedback on the dotnet/efcore GitHub repo.


The following short links are provided for easy reference and access.

Main documentation:
https://aka.ms/efdocs

Issues and feature requests for EF Core:
https://aka.ms/efcorefeedback

Entity Framework Roadmap:
https://aka.ms/efroadmap

What’s new in EF Core 5.x?
https://aka.ms/efcore5


Thank you from the team

A big thank you from the EF team to everyone who has used EF over the years!

ajcvickers
Arthur Vickers
AndriySvyryd
Andriy Svyryd

Brice Lambson
JeremyLikness
Jeremy Likness
lajones
lajones
maumar
Maurycy Markowski
roji
Shay Rojansky
smitpatel
Smit Patel

Thank you to our contributors!

A big thank you to the following community members who have already contributed code or documentation to the EF Core 5 release! (List is in chronological order of first contribution to EF Core 5).

aevitas
aevitas
alaatm
Alaa Masoud
aleksandar-manukov
Aleksandar Manukov
amrbadawy
Amr Badawy
AnthonyMonterrosa
Anthony Monterrosa
bbrandt
Ben Brandt
benmccallum
Ben McCallum
ccjx
Clarence Cai
CGijbels
Christophe Gijbels
cincuranet
Jiri Cincura
Costo
Vincent Costel
dshuvaev
Dmitry Shuvaev
EricStG
Eric St-Georges
ErikEJ
Erik Ejlskov Jensen
gravbox
Christopher Davis
ivaylokenov
Ivaylo Kenov
jfoshee
Jacob Foshee
jmzagorski
Jeremy Zagorski
jviau
Jacob Viau
knom
Max K.
lohoris-crane
lohoris-crane
loic-sharma
Loïc Sharma
lokalmatador
lokalmatador
mariusGundersen
Marius Gundersen
Marusyk
Roman Marusyk
matthiaslischka
Matthias Lischka
MaxG117
MaxG117
MHDuke
MHDuke
mikes-gh
Mike Surcouf
Muppets
Neil Bostrom
nmichels
Nícolas Michels
OOberoi
Obi Oberoi
orionstudt
Josh Studt
ozantopal
Ozan Topal
pmiddleton
Paul Middleton
prog-rajkamal
Raj
ptjhuang
Peter Huang
ralmsdeveloper
Rafael Almeida Santos
redoz
Patrik Husfloen
rmarskell
Richard Marskell
sguitardude
sguitardude
SimpleSamples
Sam Hobbs
svengeance
Sven
VladDragnea
Vlad
vslee
vslee
WeihanLi
liweihan
Youssef1313
Youssef Victor
1iveowl
1iveowl
thomaslevesque
Thomas Levesque
akovac35
Aleksander Kovač
leotsarev
Leonid Tsarev
kostat
Konstantin Triger
sungam3r
Ivan Maximov
dzmitry-lahoda
Dzmitry Lahoda
Logerfo
Bruno Logerfo
witheej
Josh Withee
FransBouma
Frans Bouma
IGx89
Matthew Lieder
paulomorgado
Paulo Morgado
mderriey
Mickaël Derriey
LaurenceJKing
Laurence King
oskarj
Oskar Josefsson
bdebaere
bdebaere
BhargaviAnnadevara-MSFT
Bhargavi Annadevara
AlexanderTaeschner
Alexander Täschner
Jesse-Hufstetler
Jesse Hufstetler
ivarlovlie
Ivar Løvlie
cucoreanu
cucoreanu
serpent5
Kirk Larkin
sdanyliv
Svyatoslav Danyliv
twenzel
Toni Wenzel
manvydasu
manvydasu
brandongregoryscott
Brandon Scott
uncheckederror
Thomas Ryan
rocke97
Aaron Gunther

9 comments

Leave a comment

  • Avatar
    Marcel

    The split queries API seems extremely confusing and potentially misguiding.

    When you say ‘and can be used anywhere in the query’, does this mean it has different semantics if placed at different points in the query?
    This also seems like unnecessary leakage of abstraction of how the query is executed. If there are performance/correctness trade-offs for these perhaps the API should be called .SetExecutionMode(QueryMode.Performance) VS .SetExecutionMode(QueryMode.DataIntegrity) – with the documentation of each mode outlining what differences COULD happen in one VS the other.

    The way it is currently defined, it’s confusing as to what happens if a query doesn’t have multiple variants (ie: always a single query) – or what if the single query version would actually be faster than the multi-query? Is there ALWAYS a correlation where single query is slower than its multi-query variant? If not, this seems like something the query execution / mapping should be figuring out on its own, unless this is ALWAYS dependent on each application and thus needs to be measured/optimized for each query on production data.

    Also what happens if the queries are already performed within a transaction (similar to what Ian asked about above)? Does the multi-query approach still suffer from potential correctness on the returned results?

    Please provide details on these various aspects/considerations, as this change is very much PROCESS-breaking (ie: introduces a new step to the development process on EF-backed applications), namely to decide for each query how to specify its execution mode rather than having the framework figure it out for itself.

  • Avatar
    John King

    When will EFCore Support .AsSubQuery() to map a sql query as a “View” like block ,
    forexample :

    SubQuery query = db.Blog.Join(db.Author, b=>b.AuthorId, a=>a.Id, (b,a)=> new { a.Name, a.Id }).Where(b=>b.id= id).AsSubQuery() ; // SubQuery  is IQueryalbe
    db.Post.Where(p=> query.Select(x=>x.AuthId).Contains( p.AuthId)).ToList();
    
    // translate
    
    SELECT p.[Id], ....   FROM POST
    WHERE  p.AuthId in (SELECT AuthorId FROM (SELECT * FROM  Blog as b INNER JOIN Author as a on b.AuthorId in a.Id  ))   //  SubQUery always generate it's own sql  and  use it  in a ( sql )  ,  this can make generate complex sql easier