EF Core 8 Preview 1: Raw, lazy, and on-time

Arthur Vickers

The first preview of Entity Framework Core (EF Core) 8 is available on NuGet today!

Basic information

EF Core 8, or just EF8, is the successor to EF Core 7, and is scheduled for release in November 2023, at the same time as .NET 8.

EF8 currently targets .NET 6. This will likely be updated to .NET 8 as we near release.

EF8 will align with .NET 8 as a long-term support (LTS) release. See the .NET support policy for more information.

EF8 themes

Before looking at the new features in preview 1, let’s take a quick look at the plan for large investments in data access for .NET 8. These investments are covered by five themes:

Highly requested features

  • JSON columns Build on EF7 JSON support to further power the document/relational hybrid pattern.
  • Value objects Applications can use DDD-style value objects in EF models.
  • SQL queries for unmapped types Applications can execute more types of SQL query without dropping down to ADO.NET or using third-party libraries.

Cloud native and devices

Performance

  • Woodstar Fast, fully managed access to SQL Server and Azure SQL for .NET applications.

Visual Tooling

Developer experience

  • Make EF Core better Improve the developer experience be making many small improvements to EF Core

Find out more and give feedback

Your feedback on planning is important. Please comment on GitHub Issue #29853 with any feedback or general suggestions about the plan. The best way to indicate the importance of an issue is to vote (đź‘Ť) for that issue on GitHub. This data will then feed into the planning process for the next release.

New in EF8 preview 1

The following sections give an overview of three particular enhancements available in EF8 preview 1: Raw SQL queries for unmapped types, lazy-loading for no-tracking queries, and DateOnly/TimeOnly support for SQL Server/Azure SQL. In total, EF8 preview 1 ships with more than 60 improvements, both bug fixes and enhancements.

TIP Full details of all new EF8 features can be found in the What’s New in EF8 documentation. All the code is available in runnable samples on GitHub.

Raw SQL queries for unmapped types

EF7 introduced raw SQL queries returning scalar types. This is enhanced in EF8 to include raw SQL queries returning any mappable CLR type, without including that type in the EF model.

TIP The code shown here comes from RawSqlSample.cs.

Queries using unmapped types are executed using SqlQuery or SqlQueryRaw. The former uses string interpolation to parameterize the query, which helps ensure that all non-constant values are parameterized. For example, consider the following database table:

CREATE TABLE [Posts] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NOT NULL,
    [Content] nvarchar(max) NOT NULL,
    [PublishedOn] date NOT NULL,
    [BlogId] int NOT NULL,
);

SqlQuery can be used to query this table and return instances of a BlogPost type with properties corresponding to the columns in the table:

For example:

public class BlogPost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateOnly PublishedOn { get; set; }
    public int BlogId { get; set; }
}

For example:

var start = new DateOnly(2022, 1, 1);
var end = new DateOnly(2023, 1, 1);
var postsIn2022 =
    await context.Database
        .SqlQuery<BlogPost>($"SELECT * FROM Posts as p WHERE p.PublishedOn >= {start} AND p.PublishedOn < {end}")
        .ToListAsync();

When using SQL Server, this query is parameterized and executed as:

SELECT * FROM Posts as p WHERE p.PublishedOn >= @p0 AND p.PublishedOn < @p1

The type used for query results can contain common mapping constructs supported by EF Core, such as parameterized constructors and mapping attributes. For example:

public class BlogPost
{
    public BlogPost(string blogTitle, string content, DateOnly publishedOn)
    {
        BlogTitle = blogTitle;
        Content = content;
        PublishedOn = publishedOn;
    }

    public int Id { get; private set; }

    [Column("Title")]
    public string BlogTitle { get; set; }

    public string Content { get; set; }
    public DateOnly PublishedOn { get; set; }
    public int BlogId { get; set; }
}

NOTE Types used in this way do not have keys defined and cannot have relationships to other types. Types with relationships must be mapped in the model.

One nice feature of SqlQuery is that it returns an IQueryable which can be composed on using LINQ. For example, a ‘Where’ clause can be added to the query above:

var summariesIn2022 =
    await context.Database.SqlQuery<PostSummary>(
            @$"SELECT b.Name AS BlogName, p.Title AS PostTitle, p.PublishedOn
               FROM Posts AS p
               INNER JOIN Blogs AS b ON p.BlogId = b.Id")
        .Where(p => p.PublishedOn >= cutoffDate && p.PublishedOn < end)
        .ToListAsync();

This is executed as:

SELECT [n].[BlogName], [n].[PostTitle], [n].[PublishedOn]
FROM (
         SELECT b.Name AS BlogName, p.Title AS PostTitle, p.PublishedOn
         FROM Posts AS p
                  INNER JOIN Blogs AS b ON p.BlogId = b.Id
     ) AS [n]
WHERE [n].[PublishedOn] >= @__cutoffDate_1 AND [n].[PublishedOn] < @__end_2

At this point it is worth remembering that all of the above can be done completely in LINQ without the need to write any SQL. This includes returning instances of an unmapped type like PostSummary. For example, the preceding query can be written in LINQ as:

var summariesByLinq =
    await context.Posts.Select(
            p => new PostSummary
            {
                BlogName = p.Blog.Name,
                PostTitle = p.Title,
                PublishedOn = p.PublishedOn,
            })
        .Where(p => p.PublishedOn >= start && p.PublishedOn < end)
        .ToListAsync();

Which translates to much cleaner SQL:

SELECT [b].[Name] AS [BlogName], [p].[Title] AS [PostTitle], [p].[PublishedOn]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
WHERE [p].[PublishedOn] >= @__start_0 AND [p].[PublishedOn] < @__end_1

TIP EF is able to generate cleaner SQL when it is responsible for the entire query than it is when composing over user-supplied SQL because, in the former case, the full semantics of the query are available to EF.

So far, all the queries have been executed directly against tables. SqlQuery can also be used to return results from a view without mapping the view type in the EF model. For example:

var summariesFromView =
    await context.Database.SqlQuery<PostSummary>(
            @$"SELECT * FROM PostAndBlogSummariesView")
        .Where(p => p.PublishedOn >= cutoffDate && p.PublishedOn < end)
        .ToListAsync();

Likewise, SqlQuery can be used for the results of a function:

var summariesFromFunc =
    await context.Database.SqlQuery<PostSummary>(
            @$"SELECT * FROM GetPostsPublishedAfter({cutoffDate})")
        .Where(p => p.PublishedOn < end)
        .ToListAsync();

The returned IQueryable can be composed upon when it is the result of a view or function, just like it can be for the result of a table query. Stored procedures can be also be executed using SqlQuery, but most databases do not support further composition. For example:

var summariesFromStoredProc =
    await context.Database.SqlQuery<PostSummary>(
            @$"exec GetRecentPostSummariesProc")
        .ToListAsync();

Lazy-loading for no-tracking queries

EF8 adds support for lazy-loading of navigations on entities that are not being tracked by the DbContext. This means a no-tracking query can be followed by lazy-loading of navigations on the entities returned by the no-tracking query.

TIP The code for the lazy-loading examples shown below comes from LazyLoadingSample.cs.

For example, consider a no-tracking query for blogs:

var blogs = await context.Blogs.AsNoTracking().ToListAsync();

If Blog.Posts is configured for lazy-loading, for example, using lazy-loading proxies, then accessing Posts will cause it to load from the database:

Console.WriteLine();
Console.Write("Choose a blog: ");
if (int.TryParse(ReadLine(), out var blogId))
{
    Console.WriteLine("Posts:");
    foreach (var post in blogs[blogId - 1].Posts)
    {
        Console.WriteLine($"  {post.Title}");
    }
}

EF8 also reports whether or not a given navigation is loaded for entities not tracked by the context. For example:

foreach (var blog in blogs)
{
    if (context.Entry(blog).Collection(e => e.Posts).IsLoaded)
    {
        Console.WriteLine($" Posts for blog '{blog.Name}' are loaded.");
    }
}

There are a few important considerations when using lazy-loading in this way:

  • Lazy-loading will only succeed until the DbContext used to query the entity is disposed.
  • Entities queried in this way a reference to their DbContext, even though they are not tracked by it. Care should be taken to avoid memory leaks if the entity instances will have long lifetimes.
  • Explicitly detaching the entity by setting its state to EntityState.Detached severs the reference to the DbContext and lazy-loading will no longer work.
  • Remember that all lazy-loading uses synchronous I/O, since there is no way to access a property in an asynchronous manner.

Lazy-loading from untracked entities works for both lazy-loading proxies and lazy-loading without proxies.

DateOnly/TimeOnly supported on SQL Server

The System.DateOnly and System.TimeOnly types were introduced in .NET 6 and have been supported for several database providers (e.g. SQLite, MySQL, and PostgreSQL) since their introduction. For SQL Server, the recent release of a Microsoft.Data.SqlClient package targeting .NET 6 has allowed ErikEJ to add support for these types at the ADO.NET level. This in turn paved the way for support in EF8 for DateOnly and TimeOnly as properties in entity types.

TIP DateOnly and TimeOnly can be used in EF Core 6 and 7 using the ErikEJ.EntityFrameworkCore.SqlServer.DateOnlyTimeOnly community package from @ErikEJ.

For example, consider the following EF model for British schools:

public class School
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public DateOnly Founded { get; set; }
    public List<Term> Terms { get; } = new();
    public List<OpeningHours> OpeningHours { get; } = new();
}

public class Term
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public DateOnly FirstDay { get; set; }
    public DateOnly LastDay { get; set; }
    public School School { get; set; } = null!;
}

[Owned]
public class OpeningHours
{
    public OpeningHours(DayOfWeek dayOfWeek, TimeOnly? opensAt, TimeOnly? closesAt)
    {
        DayOfWeek = dayOfWeek;
        OpensAt = opensAt;
        ClosesAt = closesAt;
    }

    public DayOfWeek DayOfWeek { get; private set; }
    public TimeOnly? OpensAt { get; set; }
    public TimeOnly? ClosesAt { get; set; }
}

TIP The code shown here comes from DateOnlyTimeOnlySample.cs.

NOTE This model represents only British schools and stores times as local (GMT) times. Handling different timezones would complicate this code significantly. Note that using DateTimeOffset would not help here, since opening and closing times have different offsets depending whether daylight saving time is active or not.

These entity types map to the following tables when using SQL Server or Azure SQL. Notice that the DateOnly properties map to date columns, and the TimeOnly properties map to time columns.

CREATE TABLE [Schools] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [Founded] date NOT NULL,
    CONSTRAINT [PK_Schools] PRIMARY KEY ([Id]));

CREATE TABLE [OpeningHours] (
    [SchoolId] int NOT NULL,
    [Id] int NOT NULL IDENTITY,
    [DayOfWeek] int NOT NULL,
    [OpensAt] time NULL,
    [ClosesAt] time NULL,
    CONSTRAINT [PK_OpeningHours] PRIMARY KEY ([SchoolId], [Id]),
    CONSTRAINT [FK_OpeningHours_Schools_SchoolId] FOREIGN KEY ([SchoolId]) REFERENCES [Schools] ([Id]) ON DELETE CASCADE);

CREATE TABLE [Term] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [FirstDay] date NOT NULL,
    [LastDay] date NOT NULL,
    [SchoolId] int NOT NULL,
    CONSTRAINT [PK_Term] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Term_Schools_SchoolId] FOREIGN KEY ([SchoolId]) REFERENCES [Schools] ([Id]) ON DELETE CASCADE);

Queries using DateOnly and TimeOnly work in the expected manner. For example, the following LINQ query finds schools that are currently open:

openSchools = await context.Schools
    .Where(
        s => s.Terms.Any(
                 t => t.FirstDay <= today
                      && t.LastDay >= today)
             && s.OpeningHours.Any(
                 o => o.DayOfWeek == dayOfWeek
                      && o.OpensAt < time && o.ClosesAt >= time))
    .ToListAsync();

This query translates to the following SQL, as shown by calling ToQueryString() on the IQueryable object:

DECLARE @__today_0 date = '2023-02-07';
DECLARE @__dayOfWeek_1 int = 2;
DECLARE @__time_2 time = '19:53:40.4798052';

SELECT [s].[Id], [s].[Founded], [s].[Name], [o0].[SchoolId], [o0].[Id], [o0].[ClosesAt], [o0].[DayOfWeek], [o0].[OpensAt]
FROM [Schools] AS [s]
LEFT JOIN [OpeningHours] AS [o0] ON [s].[Id] = [o0].[SchoolId]
WHERE EXISTS (
    SELECT 1
    FROM [Term] AS [t]
    WHERE [s].[Id] = [t].[SchoolId] AND [t].[FirstDay] <= @__today_0 AND [t].[LastDay] >= @__today_0) AND EXISTS (
    SELECT 1
    FROM [OpeningHours] AS [o]
    WHERE [s].[Id] = [o].[SchoolId] AND [o].[DayOfWeek] = @__dayOfWeek_1 AND [o].[OpensAt] < @__time_2 AND [o].[ClosesAt] >= @__time_2)
ORDER BY [s].[Id], [o0].[SchoolId]

How to get EF8 preview 1

EF8 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 8.0.0-preview.1.23111.4

Installing the EF8 Command Line Interface (CLI)

The dotnet-ef tool must be installed before executing EF8 Core migration or scaffolding commands.

To install the tool globally, use:

dotnet tool install --global dotnet-ef --version 8.0.0-preview.1.23111.4

If you already have the tool installed, you can upgrade it with the following command:

dotnet tool update --global dotnet-ef --version 8.0.0-preview.1.23111.4

The .NET Data Community Standup

The .NET data access team is now live streaming every other Wednesday at 10am Pacific Time, 1pm Eastern Time, or 18:00 UTC. Join the stream learn and ask questions about many .NET Data related topics.

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 links are provided for easy reference and access.

3 comments

Comments are closed. Login to edit/delete your existing comments

  • AndrĂ© R. 0

    Given .Net’s goal to be a open source and cross platform project it still surprises me to not see plans to add first party / native support for MySQL (including forks), PostgresSQL and Oracle like O/RM’s in other languages / frameworks have by default.

    The providers of these platforms would probably gladly contribute what they have today and help continue to support it together with EF team like they presently do on a range of open source languages and frameworks out there.

    The option to run .Net on linux + open source SQL variants would provide a way to run .Net projects at much lower operations cost which would benefit it in many ways and broaden its reach to new markets. And it will fit well with Microsoft’s Azure complimentary offering which already provide these SQL database options.

    It’s a win-win.

    • Arthur VickersMicrosoft employee 2

      @André R EF Core already has high-quality community-driven providers for PostgreSQL, MySQL, and their variants. See Pomelo.EntityFrameworkCore.MySql and Npgsql Entity Framework Core Provider. The data access team at Microsoft work with the maintainers of these providers when designing new functionality. Both these providers are used extensively in many different kinds of application. The data access team also communicate with the developers at Oracle who have their own EF Core provider.

  • MgSam 0

    I’m surprised the lack of DateOnly support in Convert.ChangeType/IConvertible is not causing problems for ADO.NET support of DateOnly. When patching in our own DateOnly support this caused major problems.

    Would love to learn more about WoodStar. Rewriting ADO.NET from the ground up is long overdue. I strongly urge you guys to consider integrating Dapper’s functionality directly into the framework as you do so. If only the DataFrame were ready for primetime, I’d urge you to make a new version of SqlBulkCopy that works with it- but alas.

    Since you guys are officially using T4 again- might I hope that it might actually be adopted by your team or another at MS and get some new development? It is an awesome technology but desperately needs a few updates: a) in-box syntax highlighting support b) Runtime T4 templates have generated .cs files which contain comments indicating the filepath of the T4 file they came from- this file path is absolute, which causes a lot of merge conflicts when different team members have repos checked out in different folders. c) Razor syntax being support in T4 would be amazing. Happy to make issues for these requests if there is a repo to do it in.

Feedback usabilla icon