Some Overlooked EF Core 7 Changes and Improvements

In the .NET 7 CODE Focus issue (November 2022), I wrote about the big changes that came to EF Core 7 focusing on features that will likely have the most impact for developers, such as performance, simpler ways to update and delete data, EF6 parity, and more.

But there were so many interesting changes in EF7, way more than just those few. Just peruse the long list relayed in the November 24, 2022 bi-weekly updates to get an idea of the broad scope of features affected. Unless you've experienced some of the things fixed or enhanced by these features, reading through the list doesn't always give you a good understanding of the change. I've selected some of the more interesting items from the list and experimented with the features to be sure that I truly understand what the problem was that they were solving and how they work now. This meant building a lot of demos, running them in EF Core 6, running them in EF Core 7, re-reading the discussions in the GitHub issues, reading code from the repository, and, in one case, emailing someone from the EF Core team for further information.

In this article, I'll share what I learned about these issues and hope that you find them as interesting and useful as I have. The relevant projects can be found in my GitHub repository https://github.com/julielerman/CodeMagEFCore72023.

FromSQL: Back to the Future

The EF Core team divided the original FromSQL method into two explicit methods - FromSqlRaw and FromSqlInterpolated - to overcome some issues that could occur if you were expressing your raw SQL as an interpolated string. The original FromSQL method was removed. The same was done for ExecuteSQL, which was replaced by ExecuteSQLRaw, ExecuteSqlInterpolated and ExecuteSqlInterpolatedAsync.

Here's an example of FromSqlRaw that takes a stored procedure with placeholders along with parameters to populate the placeholders:

var authors = _context.Authors.FromSqlRaw(
    "AuthorsPublishedinYearRange {0}, {1}", 2010, 2015).ToList();

The interpolated versions of these methods accept only a FormattableString and that string leverages interpolation to supply the parameters.

int start = 2010;
int end = 2015;
var authors = _context.Authors.FromSqlInterpolated
   ($"AuthorsPublishedinYearRange {start}, {end}").ToList();

But oh boy they are a PIA to type or discuss with your teammates! The EF Core team made a decision to bring back FromSql, ExecuteSql, and ExecuteSqlAsync to the lexicon of EF Core methods, replacing the Interpolated methods. They're identical to the methods they replace.

int start = 2010;
int end = 2015;
var authors = _context.Authors
   .FromSql($"AuthorsPublishedinYearRange {start}, {end}")
   .ToList();

The team considered removing the longer versions but left them in place to avoid breaking changes. Their guidance is to use the new methods.

Use Raw SQL to Query Scalar Values

Here's a wonderful addition to the APIs that I've already benefited from in my own work, as I'm building demos for a new EF Core and Domain-Driven Design course for Pluralsight. A new method of DbContext.Database, called SqlQuery, lets you pass in a raw SQL query to get scalar data directly from the database. You don't need a mapped entity to capture the results. Here's an example where I wanted to retrieve an int value from the database that's mapped to a private field in an entity.

var value = _context.Database.SqlQuery<int>
  (@"SELECT TOP 1 [_hasRevisedSpecSet]
     FROM [ContractVersions]")

This method isn't in the documented list of changes to EF Core 7 but can be found in the section on Raw SQL.

SqlClient Changed Connection String Encryption Defaults

This change is notable because it's a breaking change and the change wasn't to EF Core, but to Microsoft.Data.SqlClient, which is a dependency of EF Core's SQL Server provider. Version 7 of the provider depends on SqlClient 5.0 and it's this version of SqlClient that introduced the change.

Prior to this, the default value of a SQL Server connection string's Encrypt parameter was False. This made life easy during development because it meant that we didn't have to have a valid certificate installed on our development computer. But the team responsible for SqlClient decided it was time to make SqlClient more secure. If you take a look at the GitHub issue for this change (https://github.com/dotnet/SqlClient/pull/1210), you'll see that members of the EF Core team and community shared their concern about the breaking changes this would create - not just for EF Core, but even more broadly. The change went forward and now you really do need to be aware of it.

Encrypt=True requires that the server is configured with a valid certificate and that the client trusts the certificate.

If you're targeting a local SQL Server instance on your development computer and don't happen to have a development certificate installed, you can simply add Encrypt=False to your connection string. Otherwise, you'll get a SqlException telling you that the certificate was issued by an untrusted certificate authority. In most other cases, it's a good thing to have the proper setup that aligns with the encryption. Hopefully, you're using separate connection strings for dev than for production, but do be sure not to send that hack into production.

Orphaned Dependents are Protected from Inadvertent Deletion

Although this is listed as a breaking change for EF Core 7, it's something that was working in EF Core 5, got broken in EF Core 6.0.0, and fixed in 6.0.3. As I spent some time investigating it as an EF Core 7 change, I'll share it with you because it's still notable.

Although it's possible to define a nullable relationship in a few ways, this is specific to the case where you have a nullable foreign key property or a reference in a dependent.

For example, let's consider a system where you have library patrons who borrow books. When a patron returns the book, it's no longer tied to that patron, so in the book type, you have the nullable FK property PatronId:

public int? PatronId { get; set;}

When the book is returned, your code should set PatronId to null.

If a patron with a book moves away and you delete them from your system, moving them to a different system maintaining inactive patrons, by default, EF Core sets the value of that book's PatronId to null. This doesn't make sense because now the book will never get returned. The behavior that the librarian requested is that the book get deleted along with the patron. To achieve that, it's possible to override the default behavior by forcing the OnDelete method on the relationship in OnModelCreating to Cascade.

modelBuilder.Entity<Patron>()
 .HasMany(p => p.Books).WithOne()
 .OnDelete(DeleteBehavior.Cascade);

Unfortunately, there was a side effect created in EF Core 6 that, because of that explicit cascade delete, also deleted a book if you set its PatronId to null.

And that's the scenario that was fixed in 6.0.3 and is listed as a change in 7.0.

Now, if you have a nullable relationship that you've overridden so that deleting the principal cascades and deletes the dependents, simply setting the foreign key to null will just update the FK to null in the database.

Filtered Includes for Hidden Navigation Properties

Here's a feature that improves the experience of using EF Core to persist types that follow Domain-Driven Design guidance. In fact, the community member who requested this capability called out DDD in their request (https://github.com/dotnet/efcore/issues/27493).

The scenario provided was an entity with a one-to-many relationship where the dependents are encapsulated to protect how they are interacted with. This is an important capability. For example, a typical entity might totally expose the dependents for any type of operation whether you are adding new books, removing a book, or editing a book. And this class doesn't at all express the true behavior: that the books are being checked out and returned, not added and removed.

public class Patron
 {
       public int PatronId { get; set; }
       public string? Name { get; set; }
       public List<Book> Books { get; set; }
           = new List<Book>();
 }

Alternatively, you can encapsulate the Books and ensure that the Patron class (as an aggregate root) not only controls how the books are checked out and returned but that the Patron entity better describes what's happening. Expressing behavior is an important part of DDD.

There are different ways to achieve this, but one way is shown in Listing 1, where a _books private field allows special protected access to the books but checking in and out is simply performed on the book in question. At the same time, the class also makes it easy to see the list of checked out titles.

Listing 1: Hidden collection protects the books navigation property

private readonly List<Book> _books = new List<Book>();
public void CheckOutBook(Book bookToCheckout)
{
    if (bookToCheckout.PatronId is null)
    {
        bookToCheckout.PatronId = PatronId;
    }
}
public void ReturnBook(Book bookToReturn)
{
  if (bookToReturn.PatronId == PatronId)
    {
        bookToReturn.PatronId = null;
    }
}
public List<string?> CheckedOutTitles =>
  _books.Select(b => b.Title).ToList();

In a repository or other class you may use for persistence, it's still possible to query for a patron and books if needed. This is made possible by specifying a Books property in the data model with the following mapping in the DbContext:

modelBuilder.Entity<Patron>().HasMany("_books").WithOne();

EF Core knows to tie the _books property to the Books property here because of the naming conventions I used. So now there's a secret Books property that's not exposed in the Patron class but is known to EF Core.

Given that set up, it was already possible to eager load those books using the same string specified in the mapping.

context.Patrons.Include("_books").ToList();

But when filtered includes (i.e., a way to sort or filter the dependent collection being loaded) was introduced in EF Core 6, there was no way to apply this capability to the above pattern using the string parameter.

The solution the team arrived at was to allow the use of EF.Property in an Include method.

That means the above query can also be expressed as:

context.Patrons.Include(p =>
  EF.Property<Book>(p, "_books"))
  .ToList();

And the filtering or sorting methods can be appended to the EF.Property, as long as you first specify that the property is, in fact, a collection:

context.Patrons.Include(p =>
  EF.Property<ICollection<Book>>(p, "_books"))
  .ToList();

Entity Splitting

EF6 allowed us to split the properties of an entity across multiple tables, referred to as “entity splitting” but the capability didn't originally make it into EF Core. If you were to look at the original request in GitHub to support this in EF Core (https://github.com/dotnet/efcore/issues/620), you'd see that the team had to keep punting this change from one version to another. They finally enabled it in EF Core 7 with a new mapping method called SplitToTable.

Here's an example where I've added a property, MobileNumber, to the Patron class:

public string? MobileNumber { get; set; }

You can use the SplitToTable method to not only specify which properties of the type go to the alternate table(s) but you can also specify a name for the column if it differs from the property name, as I've done here.

    modelBuilder.Entity<Patron>()
       .SplitToTable("PatronContactInfo",
         p => p.Property(c => c.MobileNumber)
               .HasColumnName("CellPhone"));

Along with this feature, the ability to specify the column name was also added to mappings for TPT and TPC inheritance where multiple tables are also involved. You can see examples of these mappings in this GitHub issue: https://github.com/dotnet/efcore/issues/19811.

Unidirectional Many-to-Many

Another improvement that lends support to DDD entities is the ability to expose only one side of a many-to-many relationship. Quite often in your domain models, you might have such a relationship but only need to navigate in one direction.

For example, you can have categories for the library books. Each book may have one or more category and each category may have one or more book. However, while getting a list of categories for a book is common, the library told us that it's highly unusual to want to find every book for a single category. So why complicate the category class with an unneeded Books property?

The book class has a Categories property:

public List<Category> Categories { get; set; }
 = new List<Category>();

Yet the Category class has no property for books:

public class Category
{
    public int CategoryId { get; set; }
    public string? Name { get; set; }
}

With no additional help, EF Core assumes that this is a one-to-many relationship. To counter this, I've added a mapping in the DbContext to ensure that the data model is aware that it's a many-to-many:

modelBuilder.Entity<Book>()
 .HasMany(b => b.Categories).WithMany();

The database schema reflects the many-to-many and EF Core respects it as well.

In my code logic, I can add categories to a book but I can't add books to categories. Yet because the many-to-many does exist in the database, I can access the data from other logic in my software as well as reporting solutions.

Enhance Temporal Table Support for Owned Entities

Table splitting is the opposite of entity splitting and has been part of EF Core for a while. It allows mappings where the columns of a single table are split across multiple types in your system. You can use it to force properties of one entity to be stored in the table of another entity using the ToTable mapping. See https://learn.microsoft.com/en-us/ef/core/modeling/table-splitting for examples of this scenario. Table splitting is also the feature that enables the properties of owned entities to be stored alongside the properties of the entity that owns them.

When temporal table support arrived in EF Core 6, it couldn't be combined with table splitting. It was explicitly blocked, which I gather is due to time constraints and other priorities. The team was able to “unblock” the limitation early on but decided that it was too big of a change to release in a patch of EF Core 6, so they delayed it until EF Core 7.

The key is that both entities must be explicitly mapped to the target database table and configured as temporal. That's more effort than standard table splitting mappings. And for owned entities, it can get pretty cumbersome. Here's an example of what that would look like given two entities, Patron and ContactInfo, where ContactInfo is an owned entity of Patron.

First, here's the ContactInfo class:

public class ContactInfo
{
  public string? MobileNumber { get; set; }
  public string? MainEmailAddress { get; set; }
}

Patron now has a nullable ContactInfo property.

public ContactInfo? ContactInfo { get; set; }

Without temporal tables, you only need to specify that Patron owns ContactInfo like this:

modelBuilder.Entity<Patron>()
 .OwnsOne(p => p.ContactInfo);

To ensure that the Patrons table with the combination of columns from both types is temporal, you'll have to configure a lot of information. In fact, with standard temporal tables, you can rely on the default start and end column names that EF Core supplies. For this mapping, you must also explicitly map those columns for both types to ensure that they match (Listing 2). The resulting table is shown in Figure 1.

Listing 2: Mapping an owned entity for temporal tables

modelBuilder.Entity<Patron>().ToTable("Patrons",
  tableBuilder =>
  {
    tableBuilder.IsTemporal();
    tableBuilder.Property<DateTime>("PeriodStart")
    .HasColumnName("PeriodStart");
    tableBuilder.Property<DateTime>("PeriodEnd")
     .HasColumnName("PeriodEnd");
  }).OwnsOne(p => p.ContactInfo,
     ownedBuilder => ownedBuilder.ToTable("Patrons",
       tableBuilder =>
{
  tableBuilder.IsTemporal();
  tableBuilder.Property<DateTime>("PeriodStart")
   .HasColumnName("PeriodStart");
  tableBuilder.Property<DateTime>("PeriodEnd")
   .HasColumnName("PeriodEnd");
 }
));

Figure 1: Temporal table created by EF Core with columns from an owned entity

You may want to keep a copy of that mapping handy to copy and paste but be aware that the team is working on a simpler way to achieve that mapping. Follow https://github.com/dotnet/efcore/issues/29303 if you want to see the progress.

Migration File Naming Protection

Sometimes when adding migrations, you may just be experimenting and it's very tempting to name the migration file “migration”. In some cases, this causes a circular dependency with the Migrations class (https://github.com/dotnet/efcore/issues/13424).

Thanks to community member Kev Ritchie (https://github.com/KevRitchie) for his pull request to avoid this problem by having the migrations command warn you away from this naming with an internal validation and a message telling you “You cannot add a migration with the name ‘Migration’.”

New IsDesignTime Flag to Help with Migrations in Minimal APIs

EF Core migration commands need a runtime execution to do their job. When Minimal APIs were introduced in .NET 6, there were some scenarios where production startup logic, such as forced migrations with the Migrate command, were being inadvertently executed. EF Core 7 introduced a new flag, EF.IsDesignTime, to help avoid running this production code. For example:

if (!EF.IsDesignTime)
{
   //do something irrelevant to or in
  // conflict with migrations
}

Not only can this flag help you avoid conflicts that could arise between production code and migrations, you can also use it for other purposes. For example, I'm having my sample web app use Serilog, a .NET logging library (http://serilog.net), instead of the simple logging in EF Core. Therefore, I'm configuring Serilog and then adding it to the web app's services.

When I configure it, I ask my app to log to different files depending on whether I'm running the application or if it's migrations that have triggered the application.

if (!EF.IsDesignTime)
{
    _logfile = "logs/runtimelog.txt";
}
else {
    _logfile = "logs/migrationlog.txt"; 
}
Log.Logger = new LoggerConfiguration()
 .MinimumLevel.Debug()
 .WriteTo.File(_logfile,
   rollingInterval: RollingInterval.Day)
 .CreateLogger();

Then, when I'm injecting services into the pipeline, I also inject Serilog into ASP.NET Core's logging pipeline.

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Logging.AddSerilog();

With this set up, runtime sends logs to the runtimelog.txt file and migrations sends logs to the migrationlog.txt file.

Wrapping Up

It's important and interesting to explore the EF Core docs sections on what's new and breaking changes. Although the documentation is very detailed, sometimes you just need to explore those changes yourself in order to really understand what's going on. This article is a result of that experimentation and I hope that it deepens your understanding of these features.

It's clear that the EF Core team listens and responds to feedback from the community. They may not always be able to execute on those ideas quickly due to prioritization. But in addition to the high-level changes that get the most attention (and which I covered in the earlier article), there are many more interesting tweaks in EF Core 7 that can make a big difference to many of our projects.