EF Core

Complex Types:

• complex types (also known as owned types) are used to represent value objects that do not have their own identity but are defined as part of another entity.

Unmapped Types:

• Raw SQL Queries: When you need to execute raw SQL queries that return types not directly mapped to an entity.
• Stored Procedures: When using stored procedures that return results not easily mapped to existing entities.
• Ad Hoc Projections: When projecting results into types that are not part of the EF Core model.

Bulk Update and Delete:

• Let's say you wanted to run a bulk update on a table. In EF Core 7, we could do that but were limited to a single entity. However, now in EF Core 8, we can do updates across multiple structures (however, they still need to live in the same table).

Primitive collections:

• Now as our heroes are getting more active, we want to store the date every time they save the city (instead of just the last date). One option is to create another table to store this data, but that's a lot of work. Instead, we can use a primitive collection.

Solution Setup

1. Create directory

   mkdir EfCore
   cd EfCore

2. Create EF Core project

   mkdir EfCore.Persistence
   cd EfCore.Persistence
   dotnet new classlib --framework net9.0
   dotnet add package Microsoft.EntityFrameworkCore.SqlServer
   dotnet add package Microsoft.EntityFrameworkCore.Tools

3. Create EF Core tests project

   cd ..

   mkdir EfCore.Tests
   cd EfCore.Tests
   dotnet new xunit --framework net9.0

4. Create solution and add projects

   cd ..

   dotnet new sln --format slnx
   dotnet sln add ./EfCore.Persistence
   dotnet sln add ./EfCore.Tests

SQL Setup

1. Create the following docker-compose.yml file:

   touch docker-compose.yml

services:
  db:
    container_name: ssw-net-superpowers
    hostname: ssw-net-superpowers
    image: mcr.microsoft.com/mssql/server:2022-latest
    platform: ${DOCKER_PLATFORM:-linux/amd64}
    environment:
      - SA_PASSWORD=yourStrong(!)Password
      - ACCEPT_EULA=Y
    ports:
      - "2300:1433"
    healthcheck:
      # from: https://github.com/Microsoft/mssql-docker/issues/133
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -Q "SELECT 1" -b
      interval: 30s
      timeout: 5s
      retries: 10
      start_period: 10s 

2. Start the server via the CLI or VS Code

   docker compose up -d

EF Setup

1. Open the solution in Rider

   rider .

2. In the EfCore.Persistence project, create a new file called Models.cs.

3. Add the models

   public class Hero
   {
       public int HeroId { get; set; }
       public string Name { get; set; }
       public string Alias { get; set; }
   
       public int? TeamId { get; set; }
       public Team Team { get; set; }
   
       public List<Power> HeroPowers { get; set; }
   }
   
   public class Power
   {
       public int PowerId { get; set; }
       public string Name { get; set; }
   }
   
   public class Team
   {
       public int TeamId { get; set; }
       public string Name { get; set; }
   }

4. Create the HeroDbContext.cs:

   public class HeroDbContext : DbContext
   {
       public DbSet<Hero> Heroes { get; set; } = null!;
   
       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
           optionsBuilder
               .UseSqlServer(
                   "Server=localhost,2300;Database=Heroes;User ID=sa;Password=yourStrong(!)Password;Encrypt=False;");
   
               // optionsBuilder.LogTo(System.Console.WriteLine, new[] { RelationalEventId.CommandExecuted });
       }
   
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
       }
   }

   info

   The connection string is for the SQL Server container we created earlier. Make sure this matches the port and password you set in the docker-compose.yml file.

5. Create a file called Factories.cs:

   namespace EfCore.Persistence;
   
   public static class HeroFactory
   {
       public static List<Hero> CreateHeroes()
       {
           var heroes = new List<Hero>
           {
               new()
               {
                   Name = "Superman",
                   Alias = "Clark Kent",
                   Team = TeamFactory.JusticeLeague,
                   HeroPowers =
                   [
                       PowersFactory.Flight,
                       PowersFactory.SuperStrength,
                       PowersFactory.HeatVision,
                       PowersFactory.XRayVision,
                       PowersFactory.SuperSpeed,
                       PowersFactory.SuperHearing,
                       PowersFactory.Invulnerability
                   ]
               },
               new()
               {
                   Name = "Batman",
                   Alias = "Bruce Wayne",
                   Team = TeamFactory.JusticeLeague,
                   HeroPowers =
                   [
                       PowersFactory.Wealth,
                       PowersFactory.MartialArts,
                       PowersFactory.Intelligence,
                       PowersFactory.Gadgets
                   ]
               },
               new()
               {
                   Name = "Wonder Woman",
                   Alias = "Diana Prince",
                   Team = TeamFactory.JusticeLeague,
                   HeroPowers =
                   [
                       PowersFactory.Flight,
                       PowersFactory.SuperStrength,
                       PowersFactory.SuperSpeed,
                       PowersFactory.Invulnerability
                   ]
               },
               new()
               {
                   Name = "Wolverine",
                   Alias = "Logan",
                   Team = TeamFactory.XMen,
                   HeroPowers =
                   [
                       PowersFactory.Regeneration,
                       PowersFactory.AdamantiumClaws,
                       PowersFactory.SuperStrength,
                       PowersFactory.SuperSpeed,
                       PowersFactory.SuperSenses
                   ]
               },
               new()
               {
                   Name = "Cyclops",
                   Alias = "Scott Summers",
                   Team = TeamFactory.XMen,
                   HeroPowers =
                   [
                       PowersFactory.OpticBlast,
                       PowersFactory.SuperStrength,
                       PowersFactory.SuperSpeed,
                       PowersFactory.SuperSenses
                   ]
               },
               new()
               {
                   Name = "Spiderman",
                   Alias = "Peter Parker",
                   Team = TeamFactory.XMen,
                   HeroPowers =
                   [
                       PowersFactory.SuperStrength,
                       PowersFactory.SuperSpeed,
                       PowersFactory.SuperSenses,
                       PowersFactory.Regeneration
                   ]
               }
   
           };
   
           return heroes;
       }
   
       private static class TeamFactory
       {
           public static Team JusticeLeague { get; } = new() { Name = "Justice League" };
           public static Team XMen { get; } = new() { Name = "X-Men" };
       }
   
       private static class PowersFactory
       {
           public static Power Flight { get; } = new() { Name = "Flight" };
           public static Power SuperStrength { get; } = new() { Name = "Super Strength" };
           public static Power HeatVision { get; } = new() { Name = "Heat Vision" };
           public static Power XRayVision { get; } = new() { Name = "X-Ray Vision" };
           public static Power SuperSpeed { get; } = new() { Name = "Super Speed" };
           public static Power SuperHearing { get; } = new() { Name = "Super Hearing" };
           public static Power Invulnerability { get; } = new() { Name = "Invulnerability" };
           public static Power Wealth { get; } = new() { Name = "Wealth" };
           public static Power MartialArts { get; } = new() { Name = "Martial Arts" };
           public static Power Intelligence { get; } = new() { Name = "Intelligence" };
           public static Power Gadgets { get; } = new() { Name = "Gadgets" };
           public static Power Regeneration { get; } = new() { Name = "Regeneration" };
           public static Power AdamantiumClaws { get; } = new() { Name = "Adamantium Claws" };
           public static Power SuperSenses { get; } = new() { Name = "Super Senses" };
           public static Power OpticBlast { get; } = new() { Name = "Optic Blast" };
       }
   }
   

Test Setup

1. Rename UnitTest1 to EfCoreTests

2. Add a reference from EfCore.Tests to EfCore.Persistence:

3. Update EfCoreTests to create and seed the database:

   using EfCore.Persistence;
   using Microsoft.EntityFrameworkCore;
   
   namespace EfCore.Tests;
   
   public class EfCoreTests
   {
       private readonly HeroDbContext _db;
   
       public EfCoreTests()
       {
           _db = new HeroDbContext();
           _db.Database.EnsureDeleted();
           _db.Database.EnsureCreated();
           //_db.Database.Migrate();
   
           var data = HeroFactory.CreateHeroes();
           _db.Heroes.AddRange(data);
           _db.SaveChanges();
       }
   
       [Fact]
       public void Query_Heroes()
       {
           var heroes = _db.Heroes
               .Include(i => i.Team)
               .Include(i => i.HeroPowers)
               .ToList();
       }
   }

4. Run the Query_Heroes test.

Look at the database and confirm that the data has been created. Notice how the HeroPower many-to-many joining table was automatically created.

Seeding Data

Instead of seeding data manually we can use a new feature in .NET 9 to get the DbContext to seed data automatically.

1. Update the OnConfiguring() with the following:

   HeroDbContext.cs

   // Seed functions get called after migrations are applied
   optionsBuilder.UseSeeding(void (context, _) => Seed(context));
   optionsBuilder.UseAsyncSeeding((context, _, _) =>
   {
       Seed(context);
       return Task.CompletedTask;
   });

   HeroDbContext.cs

   private void Seed(DbContext context)
   {
       var dataExists = context.Set<Hero>().Any();
   
       if (dataExists)
           return;
   
       var data = HeroFactory.CreateHeroes();
       Heroes.AddRange(data);
       context.SaveChanges();
   }

2. Remove the seeding code from the constructor of EfCoreTests:

3. Confirm the tests still run.

info

I'm not quite sure how I feel about this new feature. It feels a bit janky that I need to hook up two Seed methods. I also prefer to keep my test data out of my production code. But you should be aware of the feature as perhaps it will be useful in your projects.

While we can use this approach for test data, static data (i.e. look up tables), should be controlled via using HasData(). That will cause EF to bake the inserts and updates into the migrations.

Migrations

Now we've got a database that can be created and populated with data. But what if we want to change the schema after it's been deployed to production. For that, we'll need to use migrations.

1. install ef tools n the solution root:

   dotnet new tool-manifest
   dotnet tool install dotnet-ef

2. Update EfCoreTests to use migrations:

   //_db.Database.EnsureCreated();
   _db.Database.Migrate();

3. Add a migration called Initial via Rider in the persistence project.

4. Add a new property to the Hero class:

   public DateOnly? LastSavedTheCity { get; set; }

   info

   Notice how we are using 'DateOnly' this was added in .NET 8

5. Add a new migration called AddedLastSavedTheCity via Rider

6. Run the test and ensure the new column is added.

Bundles

Now, running migrations during start-up of a program is not ideal. In a web farm scenario this can cause issues with scaling out. Ideally, we want to run our migrations once during deployment and not during start-up.

In older versions of EF we could script out the migrations and run them manually.

But since EF 8 we can bundle the migrations into a single file and run them all at once.

Let's now add a migration bundle.

1. Add the following test and run it:

   [Fact]
   public void DropDatabase()
   {
       _db.Database.EnsureDeleted();
   }

2. Generate the bundle

   cd EfCore.Persistence

   dotnet ef migrations bundle --self-contained --force

3. Execute the migration bundle

   ./efbundle --connection 'Server=localhost,2300;Database=Heroes;User ID=sa;Password=yourStrong(!)Password;Encrypt=False;'

   info

   if an appsettings.json can be found the connection string can be automatically picked up from there

4. Inspect the DB. Confirm tables and data are created.

   info

   You'll notice that the tables are populated. That is due to use seeding the data via the DbContext.

Complex Types

When modeling our entities in EF, we don't have to use flat structures. We can use nested data structures to group similar properties together and help us reason about our data.

In EF 7, we used Owned Entities for this. These mostly worked, but had some limitations as they were using entities under the hood.

In EF 8 onwards, we now have Complex Types, which serve a similar purpose, but are not entities under the hood. They more closely match a typical 'Value Object', that you may see in the DDD world.

Let's add a Complex Type to our model.

1. First, let's revert back to dropping and creating the DB so we don't have to keep adding migrations.

   db.Database.EnsureDeleted();
   db.Database.EnsureCreated();
   //db.Database.Migrate();

2. Add a new class called SecretHideout to Models.cs

   public class SecretHideout
   {
       public string? Street { get; set; }
       public string? City { get; set; }
       public string? Country { get; set; }
   }

3. Add this to our Hero model

   public SecretHideout SecretHideout { get; set; } = new ();

4. Configure our HeroDbContext

       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           // 👇
           var entity = modelBuilder.Entity<Hero>();
           entity.ComplexProperty(e => e.SecretHideout);
           // 👆
       }

5. Add a Hideout for Batman

   SecretHideout = new SecretHideout
   {
       Street = "Bat Cave",
       City = "Gotham",
       Country = "USA"
   }

6. Run the solution and check the DB.

   • Notice how the SecretHideout is now a nested structure in the Heroes table.

Unmapped queries

Sometimes you might want to run queries against a DB you don't control, and you don't want the extra hassle of setting up an ORM. In those cases you can use EF Core to run unmapped queries.

You may also want to run some SQL that is not supported by EF Core (such as common table expressions, and window functions).

1. Create a new class called HeroName in our test project

   public class HeroName
   {
       public int HeroId { get; set; }
       public string Name { get; set; }
       public string Alias { get; set; }
   }

2. Add the following query to EfCoreTests

   [Fact]
   public void Query_With_Unmapped_Types()
   {
       var heroName = "Superman";
       FormattableString sql =
           $"""
           SELECT HeroId, Name, Alias
           FROM Heroes
           WHERE Name = {heroName}
           """;
       var heroNames = _db.Database.SqlQuery<HeroName>(sql).ToList();
   }

3. Run and test data is returned

Bulk Updates & Deletes

Let's say you wanted to run a bulk update on a table. In EF 7 and earlier, we could do that but were limited to a single entity. However, from EF8 onwards, we can do updates across multiple structures (however, they still need to live in the same table).

1. Add the following code

   [Fact]
   public void Bulk_Update_With_Nested_Types()
   {
       _db.Heroes.ExecuteUpdate(s => s.SetProperty(h => h.SecretHideout.City, "Metropolis"));
   }

2. Run and test

Look at the database in SQL Server and confirm that the SecretHideout.City for all heroes has been updated to "Metropolis".

Primitive collections

Now as our heroes are getting more active, we want to store the date every time they save the city (instead of just the last date). One option is to create another table to store this data, but that's a lot of work. Instead, we can use a primitive collection.

1. Change the Hero class to the following

       public List<DateOnly>? SavedTheCityDates { get; set; }

2. Add some dates to one of the heroes

   SavedTheCityDates =  [
       new DateOnly(2000, 01, 01),
       new DateOnly(2000, 02, 02),
       new DateOnly(2000, 03, 03),
   ]

3. Add the following query

   [Fact]
   public void Query_Primitive_Collection()
   {
       var heroes = _db.Heroes.Where(h => h.SavedTheCityDates.Contains(new DateOnly(2000, 01, 01))).ToList();
   }

4. Run the solution and check the database, and inspect the query

   info

   In EF9 we, can also use read only collections for primatives. In a contrived example, maybe the saved city dates are historical and we don't want to change them. We can use a read only collection to ensure that the dates cannot be modified.

5. Change the SavedTheCityDates property to the following

   public IReadOnlyList<DateOnly>? SavedTheCityDates { get; set; }

6. Rerun the test and check that it still works.

JSON column enhancements

Support for JSON columns was introduced in EF7. You could query and update JSON columns. But there were some limitations.

Since EF8 we've had more advanced JSON capabilities. Let's take a look.

1. Add the following configuration to HeroDbContext

   entity.OwnsMany(e => e.HeroPowers, builder => builder.ToJson());

   info

   This will store our HeroPowers as a JSON document instead of a in a separate table

2. Add this test

   [Fact]
   public void Query_Json_Array()
   {
       var superpowers = _db.Heroes
           .AsNoTracking()
           .SelectMany(h => h.HeroPowers.Where(hp => hp.Name.Contains("Super")))
           .ToList();
   }

3. Run and inspect the DB schema and data. Run the query and inspect the JSON.

   info

   Notice how the HeroPowers are now stored as a JSON array in the Heroes table. This allows us to query and manipulate the data without needing a separate table.

   Who thinks that’s cool!? 😎

As Split Query

presenter

Consider just running through the article only.

Normally when doing includes, EF Core will use a single query to fetch the data. This can lead to performance issues if the data is large or complex.

From a SQL point of view left joins are used and the gets duplicated for every join.

info

See https://gordonbeeming.com/blog/2025-07-10/slaying-the-ef-core-cartesian-explosion-with-assplitquery for a great article on this topic.

1. Add the following test

   [Fact]
   public void Query_As_Split_Query()
   {
       var heroes = _db.Heroes
           .Include(i => i.Team)
           .Include(i => i.HeroPowers)
           //.AsSplitQuery()
           .ToList();
   }

2. Show what it's like to use and not use AsSplitQuery()