Breaking changes in EF Core 8.0 (EF8)
This page documents API and behavior changes that have the potential to break existing applications updating to EF Core 8.0.
Summary
Breaking change | Impact |
---|---|
[Contains in LINQ queries may stop working on older SQL Server versions |
High |
SQL Server date and time now scaffold to .NET DateOnly and TimeOnly |
Medium |
SQLite Math methods now translate to SQL |
Low |
High-impact changes
Contains
in LINQ queries may stop working on older SQL Server versions
Old behavior
Previously, when the Contains
operator was used in LINQ queries with a parameterized value list, EF generated SQL that was inefficient but worked on all SQL Server versions.
New behavior
Starting with EF Core 8.0, EF now generates SQL that is more efficient, but is unsupported on SQL Server 2014 and below.
Note that newer SQL Server versions may be configured with an older compatibility level, also making them incompatible with the new SQL. This can also occur with an Azure SQL database which was migrated from a previous on-premises SQL Server instance, carrying over the old compatibility level.
Why
The previous SQL generated by EF Core for Contains
inserted the parameterized values as constants in the SQL. For example, the following LINQ query:
var names = new[] { "Blog1", "Blog2" };
var blogs = await context.Blogs
.Where(b => names.Contains(b.Name))
.ToArrayAsync();
... would be translated to the following SQL:
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] IN (N'Blog1', N'Blog2')
Such insertion of constant values into the SQL creates many performance problems, defeating query plan caching and causing unneeded evictions of other queries. The new EF Core 8.0 translation uses the SQL Server OPENJSON
function to instead transfer the values as a JSON array. This solves the performance issues inherent in the previous technique; however, the OPENJSON
function is unavailable in SQL Server 2014 and below.
For more information about this change, see this blog post.
Mitigations
If your database is SQL Server 2016 (13.x) or newer, or if you're using Azure SQL, check the configured compatibility level of your database via the following command:
SELECT name, compatibility_level FROM sys.databases;
If the compatibility level is below 130 (SQL Server 2016), consider modifying it to a newer value (documentation.
Otherwise, if your database version really is older than SQL Server 2016, or is set to an old compatibility level which you cannot change for some reason, configure EF Core to revert to the older, less efficient SQL as follows:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(120));
Medium-impact changes
SQL Server date
and time
now scaffold to .NET DateOnly
and TimeOnly
Old behavior
Previously, when scaffolding a SQL Server database with date
or time
columns, EF would generate entity properties with types DateTime and TimeSpan.
New behavior
Starting with EF Core 8.0, date
and time
are scaffolded as DateOnly and TimeOnly.
Why
DateOnly and TimeOnly were introduced in .NET 6.0, and are a perfect match for mapping the database date and time types. DateTime notably contains a time component that goes unused and can cause confusion when mapping it to date
, and TimeSpan represents a time interval - possibly including days - rather than a time of day at which an event occurs. Using the new types prevents bugs and confusion, and provides clarity of intent.
Mitigations
This change only affects users which regularly re-scaffold their database into an EF code model ("database-first" flow).
It is recommended to react to this change by modifying your code to use the newly scaffolded DateOnly and TimeOnly types. However, if that isn't possible, you can edit the scaffolding templates to revert to the previous mapping. To do this, set up the templates as described on this page. Then, edit the EntityType.t4
file, find where the entity properties get generated (search for property.ClrType
), and change the code to the following:
var clrType = property.GetColumnType() switch
{
"date" when property.ClrType == typeof(DateOnly) => typeof(DateTime),
"date" when property.ClrType == typeof(DateOnly?) => typeof(DateTime?),
"time" when property.ClrType == typeof(TimeOnly) => typeof(TimeSpan),
"time" when property.ClrType == typeof(TimeOnly?) => typeof(TimeSpan?),
_ => property.ClrType
};
usings.AddRange(code.GetRequiredUsings(clrType));
var needsNullable = Options.UseNullableReferenceTypes && property.IsNullable && !clrType.IsValueType;
var needsInitializer = Options.UseNullableReferenceTypes && !property.IsNullable && !clrType.IsValueType;
#>
public <#= code.Reference(clrType) #><#= needsNullable ? "?" : "" #> <#= property.Name #> { get; set; }<#= needsInitializer ? " = null!;" : "" #>
<#
Low-impact changes
SQLite Math
methods now translate to SQL
Old Behavior
Previously only the Abs, Max, Min, and Round methods on Math
were translated to SQL. All other members would be evaluated on the client if they appeared in the final Select expression of a query.
New behavior
In EF Core 8.0, all Math
methods with corresponding SQLite math functions are translated to SQL.
These math functions have been enabled in the native SQLite library that we provide by default (through our dependency on the SQLitePCLRaw.bundle_e_sqlite3 NuGet package). They have also been enabled in the library provided by SQLitePCLRaw.bundle_e_sqlcipher. If you're using one of these libraries, your application should not be affected by this change.
There is a chance, however, that applications including the native SQLite library by other means may not enable the math functions. In these cases, the Math
methods will be translated to SQL and encounter no such function errors when executed.
Why
SQLite added built-in math functions in version 3.35.0. Even though they're disabled by default, they've become pervasive enough that we decided to provide default translations for them in our EF Core SQLite provider.
We also collaborated with Eric Sink on the SQLitePCLRaw project to enable math functions in all of the native SQLite libraries provided as part of that project.
Mitigations
The simplest way to fix breaks is, when possible, to enable the math function is the native SQLite library by specifying the SQLITE_ENABLE_MATH_FUNCTIONS compile-time option.
If you don't control compilation of the native library, you can also fix breaks by create the functions yourself at runtime using the Microsoft.Data.Sqlite APIs.
sqliteConnection
.CreateFunction<double, double, double>(
"pow",
Math.Pow,
isDeterministic: true);
Alternatively, you can force client-evaluation by splitting the Select expression into two parts separated by AsEnumerable
.
// Before
var query = dbContext.Cylinders
.Select(
c => new
{
Id = c.Id
// May throw "no such function: pow"
Volume = Math.PI * Math.Pow(c.Radius, 2) * c.Height
});
// After
var query = dbContext.Cylinders
// Select the properties you'll need from the database
.Select(
c => new
{
c.Id,
c.Radius,
c.Height
})
// Switch to client-eval
.AsEnumerable()
// Select the final results
.Select(
c => new
{
Id = c.Id,
Volume = Math.PI * Math.Pow(c.Radius, 2) * c.Height
});
Feedback
Submit and view feedback for