Skip to main content

4 posts tagged with "entity framework"

View All Tags

Managed Identity, Azure SQL and Entity Framework

Managed Identity offers a very secure way for applications running in Azure to connect to Azure SQL databases. It's an approach that does not require code changes; merely configuration of connection string and associated resources. Hence it has a good developer experience. Importantly, it allows us to avoid exposing our database to username / password authentication, and hence making it a tougher target for bad actors.

This post talks us through using managed identity for connecting to Azure SQL.

Integrated Security=true#

Everyone is deploying to the cloud. Few are the organisations that view deployment to data centers they manage as the future. This is generally a good thing, however in the excitement of the new, it's possible to forget some of the good properties that "on premise" deployment afforded when it came to connectivity and authentication.

I speak of course, of our old friend Integrated Security=true. When you seek to connect a web application to a database, you'll typically use some kind of database connection string. And back in the day, it may have looked something like this:

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;

The above provides a database server, a database and also Integrated Security=true. When you see Integrated Security=true, what you're essentially looking at is an instruction to use the identity that an application is running under (typically called a "service account") as the authentication credential to secure access to the database. Under the covers, this amounts to Windows Authentication.

The significant thing about this approach is that it is more secure than using usernames and passwords in the connection string. If you have to use username and password to authenticate, then you need to persist them somewhere - so you need to make sure that's secure. Also, if someone manages to acquire that username and password, they're free to get access to the database and do malicious things.

Bottom line: the less you are sharing authentication credentials, the better your security. Integrated Security is a harder nut to crack than username and password. The thing to note about the above phrase is "Windows Authentication". Web Apps in Azure / AWS etc do not typically use Windows Authentication when it comes to connecting to the database. Connecting with username / password is far more common.

What if there was a way to have the developer experience of Integrated Security=true without needing to use Windows Authentication? There is.

Managed Identity#

The docs express the purpose of managed identity well:

A common challenge for developers is the management of secrets and credentials to secure communication between different services. On Azure, managed identities eliminate the need for developers having to manage credentials by providing an identity for the Azure resource in Azure AD and using it to obtain Azure Active Directory (Azure AD) tokens

Historically a certain amount of ceremony was required to use managed identity to connect to a database, and could involve augmenting a DbContext like so:

public MyDbContext(DbContextOptions options) : base(options) {
var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
var credential = new DefaultAzureCredential();
var token = credential
new Azure.Core.TokenRequestContext(new[] { "" })
conn.AccessToken = token.Token;

This mechanism works, and has the tremendous upside of no longer requiring credentials be passed in a connection string. However, as you can see this isn't the simplest of setups. And also, what if you don't want to use managed identity when you're developing locally? This approach has baggage and forces us to make code changes.

Connection String alone#

The wonderful aspect of the original Integrated Security=true approach, was that there were no code changes required; one need only supply the connection string. Just configuration.

This is now possible with Azure SQL thanks to this PR to the Microsoft.Data.SqlClient nuget package. (Incidentally, Microsoft.Data.SqlClient is the successor to System.Data.SqlClient.)

Support for connection string managed identities shipped with v2.1. Connection strings can look slightly different depending on the type of managed identity you're using:

// For System Assigned Managed Identity
"Server:{serverURL}; Authentication=Active Directory MSI; Initial Catalog={db};"
// For System Assigned Managed Identity
"Server:{serverURL}; Authentication=Active Directory Managed Identity; Initial Catalog={db};"
// For User Assigned Managed Identity
"Server:{serverURL}; Authentication=Active Directory MSI; User Id={ObjectIdOfManagedIdentity}; Initial Catalog={db};"
// For User Assigned Managed Identity
"Server:{serverURL}; Authentication=Active Directory Managed Identity; User Id={ObjectIdOfManagedIdentity}; Initial Catalog={db};"

Regardless of the approach, you can see that none of the connection strings have credentials in them. And that's special.

Usage with Entity Framework Core 5#

If you're using Entity Framework Core, you might be struggling to get this working and encountering strange error messages. In my ASP.NET project I had a dependendency on [email protected].

Microsoft.EntityFrameworkCore.SqlServer@5 in NuGet

If you look close above, you'll see that the package has a dependency on Microsoft.Data.SqlClient, but crucially on 2.0.1 or greater. So if dotnet has installed a version of Microsoft.Data.SqlClient which is less than 2.1 then the functionality required will not be installed. The resolution is simple, ensure that the required version is installed:

dotnet add package Microsoft.Data.SqlClient --version 2.1.2

The version which we want to use is 2.1 (or greater) and fortunately that is compatible with Entity Framework Core 5. Incidentally, when Entity Framework Core 6 ships it will no longer be necessary to manually specify this dependency as it already requires [email protected] as a minimum.

User Assigned Managed Identity#

If you're using user assigned managed identity, you'll need to supply the object id of your managed identity, which you can find in the Azure Portal:

Managed Identity object id

You can configure this in ARM as well, but cryptically, the object id goes by the nom de plume of principalId (thanks to my partner in crime John McCormick for puzzling that out):

"CONNECTIONSTRINGS__OURDBCONNECTION": "[concat('Server=tcp:', parameters('sqlServerName') , ',1433;Initial Catalog=', parameters('sqlDatabaseName'),';Authentication=Active Directory MSI',';User Id=', reference(resourceId('Microsoft.ManagedIdentity/userAssignedIdentities/', parameters('managedIdentityName')), '2018-11-30').principalId)]"

That's it! With managed identity handling your authentication you can sleep easy, knowing you should be in a better place security wise.

VSTS and EF Core Migrations

Let me start by telling you a dirty secret. I have an ASP.Net Core project that I build with VSTS. It is deployed to Azure through a CI / CD setup in VSTS. That part I'm happy with. Proud of even. Now to the sordid hiddenness: try as I might, I've never found a nice way to deploy Entity Framework database migrations as part of the deployment flow. So I have [blushes with embarrassment] been using the Startup of my ASP.Net core app to run the migrations on my database. There. I said it. You all know. Absolutely filthy. Don't judge me.

If you care to google, you'll find various discussions around this, and various ways to tackle it. Most of which felt like too much hard work and so I never attempted.

It's also worth saying that being on VSTS made me less likely to give these approaches a go. Why? Well, the feedback loop for debugging a CI / CD setup is truly sucky. Make a change. Wait for it to trickle through the CI / CD flow (10 mins at least). Spot a problem, try and fix. Start waiting again. Repeat until you succeed. Or, if you're using the free tier of VSTS, repeat until you run out of build minutes. You have a limited number of build minutes per month with VSTS. Last time I fiddled with the build, I bled my way through a full month's minutes in 2 days. I have now adopted the approach of only playing with the setup in the last week of the month. That way if I end up running out of minutes, at least I'll roll over to the new allowance in a matter of days.

Digression over. I could take the guilt of my EF migrations secret no longer, I decided to try and tackle it another way. I used the approach suggested by Andre Broershere:

I worked around by adding a dotnetcore consoleapp project where I run the migration via the Context. In the Build I build this consoleapp in the release I execute it.

Console Yourself#

First things first, we need a console app added to our solution. Fire up PowerShell in the root of your project and:

md MyAwesomeProject.MigrateDatabase
cd .\MyAwesomeProject.MigrateDatabase\
dotnet new console

Next we need that project to know about Entity Framework and also our DbContext (which I store in a dedicated project):

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add reference ..\MyAwesomeProject.Database\MyAwesomeProject.Database.csproj

Add our new project to our solution: (I always forget to do this)

cd ../
dotnet sln add .\MyAwesomeProject.MigrateDatabase\MyAwesomeProject.MigrateDatabase.csproj

You should now be the proud possessor of a .csproj file that looks like this:

<Project Sdk="Microsoft.NET.Sdk">
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.1.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.1" />
<ProjectReference Include="..\MyAwesomeProject.Database\MyAwesomeProject.Database.csproj" />

Replace the contents of the Program.cs file with this:

using System;
using System.IO;
using MyAwesomeProject.Database;
using Microsoft.EntityFrameworkCore;
namespace MyAwesomeProject.MigrateDatabase {
class Program {
// Example usage:
// dotnet MyAwesomeProject.MigrateDatabase.dll "Server=(localdb)\\mssqllocaldb;Database=MyAwesomeProject;Trusted_Connection=True;"
static void Main(string[] args) {
if (args.Length == 0)
throw new Exception("No connection string supplied!");
var myAwesomeProjectConnectionString = args[0];
// Totally optional debug information
Console.WriteLine("About to migrate this database:");
var connectionBits = myAwesomeProjectConnectionString.Split(";");
foreach (var connectionBit in connectionBits) {
if (!connectionBit.StartsWith("Password", StringComparison.CurrentCultureIgnoreCase))
try {
var optionsBuilder = new DbContextOptionsBuilder<MyAwesomeProjectContext>();
using(var context = new MyAwesomeProjectContext(optionsBuilder.Options)) {
Console.WriteLine("This database is migrated like it's the Serengeti!");
} catch (Exception exc) {
var failedToMigrateException = new Exception("Failed to apply migrations!", exc);
Console.WriteLine($"Didn't succeed in applying migrations: {exc.Message}");
throw failedToMigrateException;

This code takes the database connection string passed as an argument, spins up a db context with that, and migrates like it's the Serengeti.

Build It!#

The next thing we need is to ensure that this is included as part of the build process in VSTS. The following commands need to be run during the build to include the MigrateDatabase project in the build output in a MigrateDatabase folder:

cd MyAwesomeProject.MigrateDatabase
dotnet build
dotnet publish --configuration Release --output $(build.artifactstagingdirectory)/MigrateDatabase

There's various ways to accomplish this which I wont reiterate now. I recommend YAML.

Deploy It!#

Now to execute our console app as part of the deployment process we need to add a CommandLine task to our VSTS build definition. It should execute the following command:

dotnet MyAwesomeProject.MigrateDatabase.dll "$(ConnectionStrings.MyAwesomeProjectDatabaseConnection)"

In the following folder:


Do note that the command uses the ConnectionStrings.MyAwesomeProjectDatabaseConnection variable which you need to create and set to the value of your connection string.

Give It A Whirl#

Let's find out what happens when the rubber hits the road. I'll add a new entity to my database project:

using System;
namespace MyAwesomeProject.Database.Entities {
public class NewHotness {
public Guid NewHotnessId { get; set; }

And reference it in my DbContext:

using MyAwesomeProject.Database.Entities;
using Microsoft.EntityFrameworkCore;
namespace MyAwesomeProject.Database {
public class MyAwesomeProjectContext : DbContext {
public MyAwesomeProjectContext(DbContextOptions<MyAwesomeProjectContext> options) : base(options) { }
// ...
public DbSet<NewHotness> NewHotnesses { get; set; }
// ...

Let's let EF know by adding a migration to my project:

dotnet ef migrations add TestOurMigrationsApproach

Commit my change, push it to VSTS, wait for the build to run and a deployment to take place.... Okay. It's done. Looks good.

Let's take a look in the database:

select * from NewHotnesses

It's there! We are migrating our database upon deployment; and not in our ASP.Net Core app itself. I feel a burden lifted.

Wrapping Up#

The EF Core team are aware of the lack of guidance around deploying migrations and have recently announced plans to fix that in the docs. You can track the progress of this issue here. There's good odds that once they come out with this I'll find there's a better way than the approach I've outlined in this post. Until that glorious day!

Back to the Future with Code First Migrations

Code First Migrations. They look a little like this in Visual Studio:

The thing I want you to notice about the image above is not the pithily named migrations. It isn't the natty opacity on everything but the migration files (which I can assure you took me to the very limits of my GIMP expertise). No, whilst exciting in themselves what I want you to think about is the order in which migrations are applied. Essentially how the __MigrationHistory table in SQL Server ends up being populated in this manner:

Because, myself, I didn't really think about this until it came time for me to try and change the ordering of some migrations manually. Do you know how migrations end up the order they do? I bet you don't. But either way, let's watch and see what happens to the pre-enlightenment me as I attempt to take a migration which appears before a migration I have created locally and move it to after that same migration.

Great Scott! It's clearly filename driven#

That's right - it's blindingly obvious to me. All I need do is take the migration I want to move forwards in time and rename it in Visual Studio. So take our old migration ("2014 is so passé darling"):

And rename it to make it new and shiny ("2015! Gorgeous - I love it sweetie!"):

Perfection right? Wrong! What you've done makes not the slightest jot of difference.

Whoa, this is heavy! Gimme the project file#

How could I be so dim? I mean it makes perfect sense - before the days of TypeScript's tsconfig.json the default ordering of *.ts files being passed to the TypeScript compiler was determined by the ordering of the *.ts files in the .csproj file. It must be the same for Code First Migrations.

So, simply spin up Notepad++ and let's play hack the XML until each file is referenced in the required order.

Well, I'm glad we sorted that out. A quick test to reassure myself of my astuteness. Drum roll.... Fail!! Things are just as they were. Shame on you John Reilly, shame on you.

Designer.cs... Your kids are gonna love it#

I want you to look very carefully at this and tell me what you see. We're looking at the mysterious 201508121401253_AddSagacityToSage.Designer.cs file that sits underneath the main 201508121401253_AddSagacityToSage.cs file. What could it be.... Give in?

The IMigrationMetadata.Id property is returning <u>201408121401253</u>_AddSagacityToSage. That is the old date! Remember? The passé one. If you change that property to line up with the file name change you're done. It works.

Let's say it together: "Automatic Migrations? Where we're going, we don't need Automatic Migrations."

Unit Testing and Entity Framework: The Filth and the Fury

Just recently I've noticed that there appears to be something of a controversy around Unit Testing and Entity Framework. I first came across it as I was Googling around for useful posts on using MOQ in conjunction with EF. I've started to notice the topic more and more and as I have mixed feelings on the subject (that is to say I don't have a settled opinion) I thought I'd write about this and see if I came to any kind of conclusion...

The Setup#

It started as I was working on a new project. We were using ASP.NET MVC 3 and Entity Framework with DbContext as our persistence layer. Rather than crowbarring the tests in afterwards the intention was to write tests to support the ongoing development. Not quite test driven development but certainly test supported development. (Let's not get into the internecine conflict as to whether this is black belt testable code or not - it isn't but he who pays the piper etc.) Oh and we were planning to use MOQ as our mocking library.

It was the first time I'd used DbContext rather than ObjectContext and so I thought I'd do a little research on how people were using DbContext with regards to testability. I had expected to find that there was some kind of consensus and an advised way forwards. I didn't get that at all. Instead I found a number of conflicting opinions.

Using the Repository / Unit of Work Patterns#

One thread of advice that came out was that people advised using the Repository / Unit of Work patterns as wrappers when it came to making testable code. This is kind of interesting in itself as to the best of my understanding ObjectSet / ObjectContext and DbSet / DbContext are both in themselves implementations of the Repository / Unit of Work patterns. So the advice was to build a Repository / Unit of Work pattern to wrap an existing Repository / Unit of Work pattern.

Not as mad as it sounds. The reason for the extra abstraction is that ObjectContext / DbContext in the raw are not MOQ-able.

Or maybe I'm wrong, maybe you can MOQ DbContext?#

No you can't. Well that's not true. You can and it's documented here but there's a "but". You need to be using Entity Frameworks Code First approach; actually coding up your DbContext yourself. Before I'd got on board the project had already begun and we were already some way down the road of using the Database First approach. So this didn't seem to be a go-er really.

The best article I found on testability and Entity Framework was this one by K. Scott Allen which essentially detailed how you could implement the Repository / Unit of Work patterns on top of ObjectSet / ObjectContext. In the end I adapted this to do the same thing sat on top of DbSet / DbContext instead.

With this in place I had me my testable code. I was quite happy with this as it seemed quite intelligible. My new approach looked similar to the existing DbSet / DbContext code and so there wasn't a great deal of re-writing to do. Sorted, right?

Here come the nagging doubts...#

I did wonder, given that I found a number of articles about applying the Repository / Unit of Work patterns on top of ObjectSet / ObjectContext that there didn't seem to be many examples to do the same for DbSet / DbContext. (I did find a few examples of this but none that felt satisfactory to me for a variety of reasons.) This puzzled me.

I also started to notice that a 1 man war was being waged against the approach I was using by Ladislav Mrnka. Here are a couple of examples of his crusade:

Ladislav is quite strongly of the opinion that wrapping DbSet / DbContext (and I presume ObjectSet / ObjectContext too) in a further Repository / Unit of Work is an antipattern. To quote him: "The reason why I don’t like it is leaky abstraction in Linq-to-entities queries ... In your test you have Linq-to-Objects which is superset of Linq-to-entities and only subset of queries written in L2O is translatable to L2E". It's worth looking at Jon Skeets explanation of "leaky abstractions" which he did for TekPub.

As much as I didn't want to admit it - I have come to the conclusion Ladislav probably has a point for a number of reasons:

1. Just because it compiles and passes unit tests don't imagine that means it works...#

Unfortunately, a LINQ query that looks right, compiles and has passing unit tests written for it doesn't necessarily work. You can take a query that fails when executed against Entity Framework and come up with test data that will pass that unit test. As Ladislav rightly points out: LINQ-to-Objects != LINQ-to-Entities.

So in this case unit tests of this sort don't provide you with any security. What you need are **integration

** tests. Tests that run against an instance of the database and demonstrate that LINQ will actually translate queries / operations into valid SQL.

2. Complex queries#

You can write some pretty complex LINQ queries if you want. This is made particularly easy if you're using comprehension syntax. Whilst these queries may be simple to write it can be uphill work to generate test data to satisfy this. So much so that at times it can feel you've made a rod for your own back using this approach.

3. Lazy Loading#

By default Entity Framework employs lazy loading. This a useful approach which reduces the amount of data that is transported. Sometimes this approach forces you to specify up front if you require a particular entity through use of Include statements. This again doesn't lend itself to testing particularly well.

Where does this leave us?#

Having considered all of the above for a while and tried out various different approaches I think I'm coming to the conclusion that Ladislav is probably right. Implementing the Repository / Unit of Work patterns on top of ObjectSet / ObjectContext or DbSet / DbContext doesn't seem a worthwhile effort in the end.

So what's a better idea? I think that in the name of simplicity you might as well have a simple class which wraps all of your Entity Framework code. This class could implement an interface and hence be straightforwardly MOQ-able (or alternatively all methods could be virtual and you could forego the interface). Along with this you should have integration tests in place which test the execution of the actual Entity Framework code against a test database.

Now I should say this approach is not necessarily my final opinion. It seems sensible and practical. I think it is likely to simplify the tests that are written around a project. It will certainly be more reliable than just having unit tests in place.

In terms of the project I'm working on at the moment we're kind of doing this in a halfway house sense. That is to say, we're still using our Repository / Unit of Work wrappers for DbSet / DbContext but where things move away from simple operations we're adding extra methods to our Unit of Work class or Repository classes which wrap this functionality and then testing it using our integration tests.

I'm open to the possibility that my opinion may be modified further. And I'd be very interested to know what other people think on the subject.


It turns out that I'm not alone in thinking about this issue and indeed others have expressed this rather better than me - take a look at Jimmy Bogard's post for an example:

Update 2#

I've also recently watched the following Pluralsight course by Julie Lerman: In this course Julie talks about different implementations of the Repository and Unit of Work patterns in conjunction with Entity Framework. Julie is in favour of using this approach but in this module she elaborates on different "flavours" of these patterns that you might want to use for different reasons (bounded contexts / reference contexts etc). She makes a compelling case and helpfully she is open enough to say that this a point of contention in the community. At the end of watching this I think I felt happy that our "halfway house" approach seems to fit and seems to work. More than anything else Julie made clear that there isn't one definitively "true" approach. Rather many different but similar approaches for achieving the same goal. Good stuff Julie!