Category Archives: Entity Framework Core

Executing Dynamically Generated SQL in EF Core

Entity Framework Core is primarily concerned with defining and executing pre-defined queries on a DB table, or executing a simple join on two of more tables. You can do more, but that’s its sweet spot – and for good reason. Have a think about the last project you worked on: I reckon 95% of you will be thinking about a forms-over-data application. Get a list of orders, update the product price, create a new user: really basic CRUD operations. So it makes sense that a framework like EF Core should make the 95% as easy as possible.

But what if you’re in the 5%? What if you’re working on a project where you have a query with 5 or 6 tables. Maybe you don’t even know which fields you’ll need to filter on. Well, for those users, EF Core provides two methods:

FromSqlRaw

And

FromSqlInterpolated

Both methods basically allow you to build your own SQL string, and execute it against the DB Context. It’s worth remembering that, unlike ADO.NET, you can’t just parse the output, you need to be ready for it; but that’s not the subject of this post. Here. We’re talking about a dynamically build query that returns a known type.

So, what are we trying to do?

The Problem

Let’s imagine that we have a table, called MyTable for the sake of argument, and MyTable has five columns:

MyTable
-- Field1
-- Field2
-- Field3
-- Field4
-- Field5

Now, let’s imagine that we have an app that allows the user to pick one or more fields to filter on (obviously, exposing the DB structure to the user is a bad idea unless you’re writing an SSMS clone, but bear with me here). When this comes through to EF, you’ve basically got three ways to implement this:

1. Dynamically build the query string and execute it directly.
2. Use (either in raw SQL, a stored procedure, or in Linq) the Field1 = filter OR filter = “” method.
3. Bring the data down to the client, and filter it there.

For the purpose of this post, we’re going to discuss option (1). All the options have merit, depending on the use case.

Let’s talk about building dynamic SQL, and some of the pitfalls.

Dynamic SQL

Building dynamic SQL is easy, right? You could just do this:

string sql = "select * from MyTable ";

If (!string.IsNullOrWhitespace(filter1)
{
    sql += $"where Field1 = {filter1}"
}

// Add additional fields, and deal with the WHERE / AND problem

var result = _myDbContext.MyTable.FromSqlInterpolated(sql);

So, this code is bad for several reasons. Let’s run through them.

1. It doesn’t compile

The first thing (although by far not the worst), is that this code won’t compile. The reason this won’t compile is that FromSqlInterpolated takes a FormattableString. Of course, this is easily correctable:

var result = _myDbContext.MyTable.FromSqlInterpolated($"{sql}");

Now the code compiles, but it probably doesn’t do what you want (to be clear, it probably will work at this point).

The next issue is one of security.

2. SQL Injection

If the field above (filter1) is set to: ‘1’; DROP TABLE MyTable; (or something equivalent), your app will execute it. This is because we’re not using placeholders. What does this mean:

1. FromSqlInterpolated accepts an interpolated string, but what we’re passing here is a pre-built string. The code being passed into FromSqlInterpolated needs to be interpolated at the time; e.g.:
– _myDbContext.MyTable.FromSqlInterpolated($”select * from MyTable where field1 = {filter1}”);
2. Since this won’t work in our case, we’ll need to build up the query using FromSqlRaw, and pass in parameters.

3. Caching

The way that most (at least relational) databases work, is that they try to cache the most frequently used queries. The problem is that, if you do something like the query above: “select * from MyTable where Field1 = ‘myvalue'”, that gets cached. If you run that again, but pass ‘myvalue2’ then that gets cached. Run it 1000 times with different values, and other queries start to get pushed out of the cache.

So, how can we build up a dynamic SQL string, without leaving ourselves open to SQL injection, or flooding the cache?

A Solution

This is a solution, it is not the solution. In it, we’re reverting a little to an ADO.NET style of doing things, by providing SqlParameters. Let’s see what that might look like:

            string sql =
                "select * " +
                "from MyTable ";

            var parameters = new List<SqlParameter>();

            int i = 1;
            foreach (var filter in filters)
            {
                if (i == 1)
                {
                    sql += $"where Field{i} = @filter{i} ";                    
                }
                else
                {
                    sql += $"and Field{i} = @filter{i} ";
                }
                parameters.Add(new SqlParameter($"@filter{i++}", filter));
            }

            var result = _paymentsDbContext.MyTable
                .FromSqlRaw(sql, parameters.ToArray())
                .ToList();

We’re assuming that we have an array / list of filters here, and we just create a query that looks something like this:

select *
from MyTable
where Field1 = @filter1
and Field3 = @filter3

Because these are placeholders, you’re protected against SQL injection, and the DB engine will cache this query (so changing the values themselves doesn’t affect the cache). It’s worth bearing in mind that if we run this again, and end up with the following:

select *
from MyTable
where Field1 = @filter1
and Field4 = @filter4
and Field5 = @filter5

This will be separately cached, so you’d need to make a decision as to whether you are likely to have few enough queries that it doesn’t matter.

Summary

Quite often, people use EF as though the data was all local. It’s always worth remembering that each time you make a call, you are accessing the DB – despite the fact that Microsoft have gone to great lengths to make you think you are not. Each time you touch the DBMS, you change something – or, rather, something is changed as a result of you touching the DB. This might be obvious, like you insert a record, or it might be hidden, like the cache is updated. Nevertheless, the DB is a service, and it is probably the most important service in your system.

This post is based on my knowledge of relational databases, so the same may not be completely true of No-Sql databases.

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