EF Core Pt. 4 - EF Core - New in 7 and upcoming 8

EF Core 7

For each release of EF it will be faster and contain more features. Some are not that well known and some are wanted since time of birth.

So lets get started with some of the features that I think will help you in your work.

Batch update and delete

In my performance article I already talked about this feature. A welcome one that I feel requires repeating.

In EF Core 7 they included support for batch handling on update and delete.
This means that you don't need to get all data from the database, iterate and do modifications and finally pass all data back to the database.
This action is quite powerful and will be more or less be an update or delete statement sent to the database. One thing to mention is that these calls are not included in the state change management, so no need to call SaveChanges. Also it will not be included in the transaction that encapsulate all changes that is executed with SaveChanges.

To make a batch update the syntax is like this example

var result = await dbContext.Customers

    .Where(x => x.DeletedAt == null && x.Id == id)

    .ExecuteUpdateAsync(s => s.SetProperty(x => x.Name, request.Name)

        .SetProperty(x => x.Phone, request.Phone)

        .SetProperty(x => x.Fax, request.Fax)

        .SetProperty(x => x.Description, request.Description)

    );

And the delete like this (will purge a soft deleted customer)

var result = await dbContext.Customers

    .Where(x => x.Id == id && x.DeletedAt != null)

    .ExecuteDeleteAsync();

JsonValue property

Even though we are working with relational data sometimes (or often) we need to store structured data into one column in SQL.
Normally we serialize the data to a string and store it, and on retrieval we deserialize it back.
Doing some querying on that data requires always some extra.
In EF Core 7 they added the support of having Json data inside a column.

So say for example we have an entity structure like this 

public sealed class CustomerEntity

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

    public AddressEntity Address { get; set; }

    public int AddressId { get; set; }

}

public sealed class AddressEntity

{

    public int Id { get; set; }

    public string StreetAddress1 { get; set; }

    public string StreetAddress2 { get; set; }

    public string PostalCode { get; set; }

    public string City { get; set; }

    public string Country { get; set; }

    public int CustomerId { get; set; }

    public CustomerEntity Customer { get; set; }

}

 

public void Configure(EntityTypeBuilder<CustomerEntity> entity)

{

    entity.HasOne(x => x.Address)

        .WithOne(x => x.Customer)

        .HasForeignKey<AddressEntity>(x => x.CustomerId);

}

This will generate a customers table, and a addresses table with foreign constraints and navigation properties.

Instead we are now able to configure it like this:

public sealed class CustomerWithJsonEntity

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

    public Address Address { get; set; }

}

 

public sealed class Address

{

    public string StreetAddress1 { get; set; }

    public string StreetAddress2 { get; set; }

    public string PostalCode { get; set; }

    public string City { get; set; }

    public string Country { get; set; }

}

 

public void Configure(EntityTypeBuilder<CustomerWithJsonEntity> entity)

{

    entity.OwnsOne(x => x.Address).ToJson();

}

EF Core will now add a string column storing Address and it will be included without using an Include statement.

Filtering data will be simple, just do the filter as you normally do

return await dbContext.CustomersWithJson

    .AsNoTracking()

    .Where(x => x.Address.City == "South Annetta")

    .ToListAsync();

And doing updates are simple as well

var customerEntity = await dbContext.CustomersWithJson

                         .Where(x => x.DeletedAt == null)

                         .FirstOrDefaultAsync(x => x.Id == id)

                     ?? throw new ArgumentException("Customer [{id}] - Not found", id.ToString());

customerEntity.Address.StreetAddress2 = streetAddress;

await dbContext.SaveChangesAsync();

Will make the following SQL update

UPDATE [CustomersWithJson] SET [Address] = JSON_MODIFY([Address], 'strict $.StreetAddress2', JSON_VALUE(@p0, '$[0]'))

Store Procedure mappings

Even though I always try using EF Core without using stored procedures there are circumstances when you need to do it. One of them if you migrating from an old solution that are using stored procedures for insert, update and delete. In these cases you are now able to define the use of these.

During the modeling of your data model you know are able to define as following:

modelBuilder.Entity<Customer>()

    .InsertUsingStoredProcedure(

        "Customer_Insert",

        storedProcedureBuilder =>

        {

            storedProcedureBuilder.HasParameter(a => a.Name);

            storedProcedureBuilder.HasResultColumn(a => a.Id);

        })

    .UpdateUsingStoredProcedure(

        "Customer_Update",

        storedProcedureBuilder =>

        {

            storedProcedureBuilder.HasOriginalValueParameter(a=> a.Id);

            storedProcedureBuilder.HasParameter(person => a.Name);

            storedProcedureBuilder.HasRowsAffectedResultColumn();

        })

    .DeleteUsingStoredProcedure(

        "Customer_Delete",

        storedProcedureBuilder =>

        {

            storedProcedureBuilder.HasOriginalValueParameter(a => a.Id);

            storedProcedureBuilder.HasRowsAffectedResultColumn();

        });

Note: In this simple example above not all properties on Customer is defined, but of course the should.

If you only have one of them you of course only need to set that one, the other ones will use the normal behavior. 

Temporal Tables

In SQL Server temporal tables was introduced in 2016 version. Temporal tables are history tables and SQL Server will automatically move old data to the history table when an update/delete occurs.

When creating a temporal table it will look like this

As you see it is marked as System-versioned and has a history table inside it.

The feature to handle temporal tables was introduced in EF Core 6 but is enhanced in EF Core 7 when working with owned tables (commonly used when coding DDD)

To create a temporal table you create it as this example:

modelBuilder

    .Entity<Employee>()

    .ToTable("Employees", b => b.IsTemporal());

The properties PeriodStart and PeriodEnd will automatically be added and are EF shadow properties.

In EF you are now able to query data with the following operators (from Microsoft EF Core 6, whats new):

• TemporalAsOf: Returns rows that were active (current) at the given UTC time. This is a single row from the current table or history table for a given primary key.
• TemporalAll: Returns all rows in the historical data. This is typically many rows from the history table and/or the current table for a given primary key.
• TemporalFromTo: Returns all rows that were active between two given UTC times. This may be many rows from the history table and/or the current table for a given primary key.
• TemporalBetween: The same as TemporalFromTo, except that rows are included that became active on the upper boundary.
• TemporalContainedIn: Returns all rows that started being active and ended being active between two given UTC times. This may be many rows from the history table and/or the current table for a given primary key.

When working with temporal table, note that it is only possible to include references on the current active item.

Raw SQL queries for scalar types 

You have been able to use the FromSql to get data to a defined entity model, now you can use the SqlQuery to run raw sql that returns scalar types.

var ids = context.Database

    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")

    .ToList();

EF Core 8

Raw SQL queries for unmapped types

The SqlQuery introduced in EF Core 7 now supports returning mapped types without using the EF Model. This will have great performance gain on read queries.

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();

The objects returned from this query will not have keys defined and relationships to other types.

DateOnly/TimeOnly

The types DateOnly/TimeOnly introduced in .Net 6 is now finally supported in EF Core.
The DateOnly will be translated into the SQL datatype date and TimeOnly to the datatype time.

HierarchyId support

Azure SQL and SQL Server have a special data type called hierarchyid that is used to store hierarchical data. In this case, "hierarchical data" essentially means data that forms a tree structure, where each item can have a parent and/or children. Examples of such data are:

• An organizational structure
• A file system
• A set of tasks in a project
• A taxonomy of language terms
• A graph of links between Web pages

This support has been around via an nuget package EntityFrameworkCore.SqlServer.HierarchyId but is now implemented in the core. 
You are able to query for siblings, parents or items on a specific level in the tree.

Summary

In EF Core 7 there are more features and changes added with a good release notes, that you can find here: What's new in EF Core 7.0

EF Core 8 is still in preview and new things might be added. For reading more about the it you can find it here: What's new in EF Core 8