Category Archives: Entity Framework Core

Entity Framework 3.1 Gotchas

I’ve recently been upgrading an EF Core 2.x project to EF Core 3.1. In doing so, I came across the issues in this post. Hopefully, next time, I’ll look here first!

Include has changed

If you’re using .include in a lambda that’s based on a DbSet, you may have something akin to the following:

var result = animals
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Duck")
                .Include(a => a.Type)
                .ToList();

Whilst this does work in EF 2.x, it will not work in 3.x; you’ll may get the following error:

System.InvalidOperationException: ‘Include has been used on non entity queryable.’

The issue here is that you’re restricted as to how you can use Includes. You’ll need to use something more like this:

var ducks = animals
                .Include(a => a.Type)
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Duck")
                .ToList();

Should you wish to include multiple related entities, you can use the following syntax:

var ducks = animals
                .Include(a => a.Type)
                .ThenInclude(a => a.EnvironmentInformation)
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Duck")
                .ToList();

Beware of Unions

Unions don’t actually work in Entity Framework Core. They never have. What this means, is that in 2.1, when you issues this type of command:

var ducks = animals
                .Include(a => a.Type)
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Duck");

var geese = animals
                .Include(a => a.Type)
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Goose");


var combined = geese
               .Union(ducks);

What actually happened was that the two queries would be run, the results brought down to the client and joined there. In 3.x, you get something like the following error:

System.InvalidOperationException: ‘Set operations over different store types are currently unsupported’

Essentially, what this forces you to do is ether explicitly bring the results down to the client yourself:

var ducks = animals
                .Include(a => a.Type)
                .Select(a => a.Name)
                .Where(a => a.Type.Name == "Duck")
                .ToList();

Or run the query on the server by employing something like a Stored Procedure.

References

https://github.com/dotnet/efcore/issues/18091

Change the Primary Key Type in Entity Framework

Imagine that you’ve created a Primary Key in Entity Framework (AFAIK, this applies to EF Core and EF 6), and it’s the wrong type: perhaps you created it as a Guid and decided it should be a string; obviously, if you created it as a string and you want it as an integer, then you have the same issue you have with any such change: EF doesn’t know how to change the text “Donald” into an integer any more than you do !

The Problem

Imagine you have a primary key PK_Duck on the table: Duck. The table has the following data:



Id Name Description
80c983c9-14d0-49b7-889d-a08b874d4629 Donald White with a blue coat
5f2178fa-2605-4f70-bdfd-a7f6e924aab0 Jemima White with a bonnet and shawl

If you just change the model / entity type from a Guid to a string, EF will generate something like the following migration:

migrationBuilder.AlterColumn<string>(
                name: "Id",
                table: "Duck",
                nullable: false,
                oldClrType: typeof(Guid));

If you try to update the DB, you’ll get the following error:

The object ‘PK_Duck’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.

The Solution

The solution here is actually quite straightforward, and exactly what you would do in raw SQL; you just drop the primary key. Manually change the migration to drop the key first, and then re-create it afterwards:

migrationBuilder.DropPrimaryKey("PK_Duck", "Duck");

migrationBuilder.AlterColumn<string>(
                name: "Id",
                table: "Duck",
                nullable: false,
                oldClrType: typeof(Guid));

migrationBuilder.AddPrimaryKey("PK_Duck", "Duck", "Id");

An assembly specified in the application dependencies manifest was not found… strange errors when trying to create an EF migration

This has been on my list to write up for some time, but this must be the third or fourth time I’ve encountered it – each time I get a vague sense that I’ve been here before.

Setting the Scene

It’s Friday afternoon, and you’re trying to create a migration, so you can check your code in and go home… or to the pub. Disaster strikes, you get the following error:

An assembly specified in the application dependencies manifest (MyProject.deps.json) was not found:
package: ‘Some.Package’, version: ‘1.0.0’
path: ‘Some.Package.dll’

You panic – there’s a pint of Doom Bar with your name on it, and it’s getting cold. You do a quick search, and you find this post… actually, it doesn’t matter what I write here – you’ll just see the title and scroll down to the answer. The Pigeon King is called Donald; there are 336 holes in your average golf ball; there’s nobody on the 37th floor, and nobody knows why.

The Answer

The answer is either that you have the incorrect project, or startup project. You can change both of these manually, or simply specify them in the migration; for example:

Add-Migration MigrationName -Context AppDbContext -StartupProject MyProject -Project MyProject.DataAccess

In my specific case, I had an Azure function set as the start-up project.

Unit Testing EF Core – How to Invoke the Contents of OnModelCreating

In this post, I wrote about how you can test an EF Database, using an InMemory database.

I’m guessing a few people reading this will be thinking this is stating the bloody obvious, but it certainly wasn’t to me. Imagine you have a DBContext with some seed data; for example:

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<EntityType1>().HasData(
                new EntityType1
                {
                    Id = "1",
                    Name = "Test1",
                    Description = "Testing",
                    IsTest = true,
                    SomeNumber = 1
                },

In the above linked article, this seed data will never fire; however, simply calling:

context.Database.EnsureCreated();

Once the context is created, will force the migrations to be run inside the in-memory instance, and you should end up with a system mirroring what you would see, should you run this migration against a physical database.

Caveat Emptor

When using this against EF Core 2.2 I found, what appeared to be, this issue. The error being, while I was trying to insert a record, an error returned saying that an item with the same key exists on the table. However, no such item should exist. The linked article seems to imply that this relates to a bug with the insert for the in-memory database, and that it is resolved for EF Core 3.0. I haven’t validated this, so it may, or may not work to upgrade to EF Core 3.0. Please add a comment if you can validate or negate this.

References

https://github.com/dotnet/efcore/issues/11666

https://github.com/dotnet/efcore/issues/6872

Add Entity Framework Core to an Existing Asp.Net Core Project

This is one of those posts born of the fact that I’m constantly googling this, and coming up with videos and Microsoft docs – I just want a single, quick step by step guide; so here it is.

Step One – NuGet packages

If you’re using SQL Server, then you’ll need these packages (technically, you only need the first, unless you want to actually create or run a migration yourself):

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

Step Two – Create DBContext (and entity classes if needed)

The minimum DBContext looks like this:

    public class MyDbContext : DbContext
    {

        public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
        {

        }

        public DbSet<MyEntity> MyEntities { get; set; }
        public DbSet<MyOtherEntity> MyOtherEntities { get; set; }
    }

You can feed your model directly into EF. That’s much easier to do than it used to be, for example, any field called Id will automatically be inferred to be a Primary Key; however, at some point, you’re going to want to do something data specific to your model, and at that point, you lose the separation between data and business layers. A nice way that I’ve found around this is to subclass your models:

    public class MyEntity : MyModel
    {
    }

Step Three – Startup.cs

You’ll need to register the DbContext in your DI pipeline:

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddDbContext<MyDbContext>(a =>
                a.UseSqlServer(Configuration.GetConnectionString("SqlConnectionString")));
        }

Your connection string goes in appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SqlConnectionString": "Server=(localdb)\\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Step Four – Migration

Since you’ve installed EF Tools, you can add a migration:

Add-Migration InitialMigration -StartupProject MySolution.MyProject

The other option is to switch on automatic migrations; although, I would personally advise against that, as you can quickly lose track of what it’s doing.

That’s it – once you run:

Update-Database

You should have a working EF instance.

Step Five (optional) – Auto-Migration

You may also wish to have the system upgrade itself to the latest version of the DB. Whether you choose to do this depends on exactly how much control you need over the changes to your DB. I’ve heard that some people create the migrations as SQL Scripts and hand them to a DBA.

Should you wish to have EF manage that for you, then you can do the following:

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            UpdateDatabase(app);

. . .

private void UpdateDatabase(IApplicationBuilder app)
        {
            using var serviceScope = app.ApplicationServices
                .GetRequiredService<IServiceScopeFactory>()
                .CreateScope();

            using var context = serviceScope.ServiceProvider.GetService<CourseSelectDbContext>();

            context.Database.Migrate();            
        }

That’s it – now, next time I want to find this, I can search my own website!

References

https://docs.microsoft.com/en-us/ef/core/get-started/install/

https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-3.1&tabs=visual-studio

https://blog.rsuter.com/automatically-migrate-your-entity-framework-core-managed-database-on-asp-net-core-application-start/

Manually Adding DbContext for an Integration Test

In EF Core, there is an extension method that allows you to add a DBContext, called AddDBContext. This is a really useful method, however, in some cases, you may find that it doesn’t work for you. Specifically, if you’re trying to inject a DBContext to use for unit testing, it doesn’t allow you to access the DBContext that you register.

Take the following code:

services.AddDbContext<MyDbContext>(options =>
                options.UseSqlServer());
         

I’ve previously written about using UseInMemoryDatabase. However, this article covered unit tests only – that is, you are able to instantiate a version of the DBContext in the unit test, and use that.

As a reminder of the linked article, if you were to try to write a test that included that DBContext, you might want to use an in memory database; you might, therefore, build up a DBContextOptions like this:

var options = new DbContextOptionsBuilder<MyDbContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .EnableSensitiveDataLogging()
                .Options;
var context = new MyDbContext(options);

But in a scenario where you’re writing an integration test, you may need to register this with the IoC. Unfortunately, in this case, AddDbContext can stand in your way. The alternative is that you can simply register the DbContext yourself:

var options = new DbContextOptionsBuilder<MyDbContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .EnableSensitiveDataLogging()
                .Options;
var context = new MyDbContext(options);
AddMyData(context);
services.AddScoped<MyDbContext>(_ => context);

AddMyData just adds some data into your database; for example:

private void AddTestUsers(MyDbContext context)
{            
    MyData data = new MyData()
    {
        value1 = "test",
        value2 = "1"                
    };
    context.MyData.Add(subject);
    context.SaveChanges();
}

This allows you to register your own, in memory, DbContext in your IoC.

Unit Testing With Entity Framework and Entity Framework Core 2.1

Entity Framework Core 2.1 comes with a nifty little feature: an In Memory Database setting. What this means, is that with a single option setting, your tests can interact directly with the database (or at least EF’s impression of the database) but not actually touch any physical database. In other words, you can write unit tests for data access; an example:

// Arrange
DbContextOptions<ApplicationDbContext> options = new DbContextOptionsBuilder<ApplicationDbContext>()
    .UseInMemoryDatabase(Guid.NewGuid().ToString())
    .EnableSensitiveDataLogging()                
    .Options;

using (var context = new ApplicationDbContext(options))
{
    context.Database.EnsureDeleted();
	ResourceCategory resourceCategory = new ResourceCategory()
    {
        Name = "TestCategory"
    }
 
    // Act
    _applicationDbContext.ResourceCategories.Add(resourceCategory);
    _applicationDbContext.SaveChanges();
};	
 
// Assert                
Assert.Equal("TestCategory", context.ResourceCategories.First().Name);               

To just quickly explain what this is doing: we have a DbContext called ApplicationDbContext and we’re building a set of options on top of that context. We’re then instantiating the context and cleaning the in memory database. Finally, we’re adding a new piece of data to the context and then asserting that it has been added.

Told you it was nifty.

But what about if you’re still using Entity Framework 6?

Glad you asked.

Out of the box, EF does not come with this kind of functionality; however, I recently came across (and contributed) to a NuGet library that provides just such a facility. It provides a wrapper for both Moq and Nsubstitute. The GitHub Repo is here.

Short Walks – System.InvalidOperationException: ‘The seed entity for entity type ‘MyEntity’ cannot be added because another seed entity with the same key value for {‘Id’} has already been added. Consider using ‘DbContextOptionsBuilder.EnableSensitiveDataLogging’ to see the conflicting key values.’

I got this error recently while playing with EF Core 2. There’s very little on Google about it; although it’s not a hugely difficult problem to solve, if I ever get it again, I can just Google it !

The error:

System.InvalidOperationException: ‘The seed entity for entity type ‘MyEntity’ cannot be added because another seed entity with the same key value for {‘Id’} has already been added. Consider using ‘DbContextOptionsBuilder.EnableSensitiveDataLogging’ to see the conflicting key values.’

If effectively cause by a conflict in the primary key; and it gives you the first step towards solving it in the error (in OnModelCreating):

var options =
    new DbContextOptionsBuilder<ApplicationDbContext>()
         .UseSqlServer(configuration.GetConnectionString("DefaultConnection"))
         .EnableSensitiveDataLogging()
         .Options;

Now it says:

System.InvalidOperationException: ‘The seed entity for entity type ‘MyEntity’ cannot be added because another seed entity with the key value ‘Id:1′ has already been added.’

In my particular case, I’d been playing around with adding some seed data, and had left this in (OnModelCreating):

modelBuilder.Entity<MyEntity>().HasData(new Data.MyEntity()
{
    Id = 1,
    Name = "Test",
    CreatedDate = new DateTime(2018, 07, 03),
    UpdatedDate = new DateTime(2018, 07, 03)
});

Short Walks – Data Seeding in Entity Framework Core 2

Entity Framework Core 2.1 has a nice little feature for seed data. Previously, seeding data quite often involved a series of checks and potential for a script to exist that, if run on the wrong data, would repeatedly re-generate the same seed data.

In 2.1, you can simply override the OnModelCreating function of the data context, like so:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
 
    public DbSet<ResourceType> ResourceType { get; set; }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
 
        modelBuilder.Entity<ResourceType>().HasData(new Data.ResourceType()
        {
            Id = 1,
            Name = "Web Site"                
        });
    }

And the framework will calculate whether or not this needs to run to put the data into the state that you’ve requested.

References

https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding

Short Walks – Setting up a Foreign Key Relationship in Entity Framework

Having had to search for this for the fiftieth time, I thought I’d document it here, so I knew where to look!

To set-up a foreign key relationship in EF, the first step is to define your classes; for example:

In this case, each Resource has a ResourceType in a simple one-to-many relationship. In the lookup table, in this case: ResourceType, define the key:

public class ResourceType
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    
}

(You’ll need to reference: System.ComponentModel.DataAnnotations)
Then, in the main table, in this case Resource, map a field to the Lookup, and then tell it how to store that in the DB:

public class Resource
{        
    public int Id { get; set; }
 
    public int ResourceTypeId { get; set; }
 
    [ForeignKey("ResourceTypeId")]
    public ResourceType ResourceType { get; set; } 
 
    public string Name { get; set; }
}

(You’ll need to reference: System.ComponentModel.DataAnnotations.Schema)

That’s it. Once you run Add-Migration, you should have a foreign key relationship set-up.