Entity Framework Core: Postgres Concurrency Checks

Last week’s post on SQLite Concurrency Checks used this repo which contains examples of using Entity Framework Core with SQLite and Postgres. This post is going to tackle concurrency checks using Postgres to keep the projects in the repo with the same level of functionality. You can grab the sample code before any change here. This whole post will only be touch files found in the Postgres folder/project.

Context Changes and Data Migration

Unlike SQLite, Postgres has better built-in support for concurrency checks. If you read the official docs on Optimistic Concurrency and Concurrency Tokens you will find that all tables have an implicit/hidden system column call xmin which holds the ID of the latest updating transaction which means it gets changed automatically every time a row is changed.

The Postgres Entity Framework Core provide contains an extension that makes it very simple to use the xmin column as a concurrency token. In the ContactDbContext add the following to the OnModelCreating function to enable concurrency checking on the specified entity, in this case, a Contact.

modelBuilder.Entity<Contact>().ForNpgsqlUseXminAsConcurrencyToken();

Next, from a command prompt in the same directory as your project file using the following .NET CLI command to generate a migration for the above change. This migration is a bit strange since the column technically already exists, but the migration seemed to be needed.

dotnet ef migrations add Concurrency --context ContactsDbContext

Then, use the following command to apply the migration to your database.

dotnet ef database update --context ContactsDbContext

Testing it out

For a quick and dirty test, I added a ConcurrencyTest razor page under the Contacts directory. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

public void OnGet()
{
    var context1 = 
        new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                              .UseNpgsql("yourConnectionString")
                              .Options);
    var contactFromContext1 = context1.Contacts
                                      .FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
                              {
                                  Name = "Test"
                              };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var context2 = 
        new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                              .UseNpgsql("yourConnectionString")
                              .Options);
    var contactFromContext2 = context2.Contacts
                                      .FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is https://localhost:44324/Contacts/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

This process was much simpler using Postgres than SQLite. Not that the SQLite version was hard just not as simple of a path.

The code in its final state can be found here.

Entity Framework Core: SQLite Concurrency Checks

Most of the work I have done with SQLite has been on single-user systems, but recently I had to work on a SQLite project that was going to have a handful of concurrent users and a subset of the user activities would need to deal with concurrency issues. In the past, in a situation like this, I have been using SQL Server and use the rowversion or timestamp column type which places a unique value on the row on any updates or inserts.

There is a page in the official docs on Concurrency Tokens, but for me, it wasn’t super helpful. Thankfully after some searching, I came across the GitHub issue In ASP.Net Core 2.x with Entity Framework Core, Concurrency Control not working with SQLite which had a solid sample as one of the replies. This post is going to walk through an example implementation of that sample. The starting point of the code can be found in this GitHub repo.

Sample Background

The sample project being used is a simple web application to manage a contact list. The repo contains an implementation using Postgres and one using Sqlite. This whole post will only be touch files found in the Sqlite folder/project.

Model Changes and Data Migration

SQLite doesn’t have the concept of a timestamp column, but this solution is going to emulate one. To do this we are going to change the Contact model found in the Models folder. We are going to add a Timestamp property with a Timestamp data annotation. The following is the full model class with the new property at the bottom.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    [Timestamp] public byte[] Timestamp { get; set; }
}

Next, let’s create a new migration with the change to the model. I’m using the .NET CLI so from a command prompt in the project directory run the following command.

dotnet ef migrations add ContactTimestamp --context ContactsDbContext

In the Migrations directory, open newly created migration. It should be named something like *_ContactTimestamp.cs.  In the Up function, we are going to add a couple of triggers to the new Timestamp column. These triggers are going to assign a random blob to the timestamp column when a row is inserted or updated which is how we are simulating the function of SQL Server’s Timestamp data type. The following is the full Up function with the added triggers.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<byte[]>(
        name: "Timestamp",
        table: "Contacts",
        rowVersion: true,
        nullable: true);

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnUpdate
        AFTER UPDATE ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnInsert
        AFTER INSERT ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");
}

To apply the migration to the database you can use the following command.

dotnet ef database update --context ContactsDbContext

Testing it out

Now for a quick and dirty test, we are going to add a ConcurrencyTest function to the existing ContactsController. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

[Route("ConcurrencyTest")]
public void ConcurrencyTest()
{
    var context1 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);
    var context2 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);

    var contactFromContext1 = context1.Contacts.FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
        {
            Name = "Test"
        };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var contactFromContext2 = context2.Contacts.FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is http://localhost:1842/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

While the information wasn’t the easiest in the world to locate, as you can see Entity Framework Core using SQLite has good support for concurrency control. The above is just one option for its implementation. I hope this saves you all so time.

The code in its final state can be found here.

Entity Framework Core: String Filter Tips

I have been working on an application that is backed by Entity Framework Core using SQLite and I have hit a couple of things that were not super clear to me at first when dealing with string filters. This post is going to cover setting up a sample application and demoing a couple of things to keep in mind when working with string in filters.

Sample Application

I’m using the Razor Pages template with individual auth as the base for this sample since it comes with Entity Framework Core already set up and ready to go. Using a command prompt in the directory you want the sample project created run the following command.

dotnet new razor --auth Individual

Open the resulting project in your editor of choice and add a Models folder. As usual, I’m going to be using a contact as my example so inside the Models folder create a Contact class matching the following. The override on ToString is just to make it easy to see the results when debugging.

public class Contact
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }

    public override string ToString()
    {
        return $"{Id} - {LastName}, {FirstName}";
    }
}

Next, open the ApplicationDbContext found in the Data folder and add the following DbSet property to expose our contacts.

public DbSet<Contact> Contacts { get; set; }

Add the following OnModelCreating function to the ApplicationDbContext which will create some test data for us.

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Contact>()
           .HasData(new Contact
                    {
                        Id = 1,
                        FirstName = "Bob",
                        LastName = "Smith",
                        Address = "123 Main Street",
                        City = "Nashville",
                        State = "TN",
                        Zip = "35970"
                    },
                    new Contact
                    {
                        Id = 2,
                        FirstName = "Sam",
                        LastName = "Smith",
                        Address = "1 Sun Lane",
                        City = "Knoxville",
                        State = "TN",
                        Zip = "48909"
                    },
                    new Contact
                    {
                        Id = 3,
                        FirstName = "Clark",
                        LastName = "Swift",
                        Address = "750 10th Street",
                        City = "Chattanooga",
                        State = "TN",
                        Zip = "91590"
                    }
                   );

    base.OnModelCreating(builder);
}

Back in the command prompt run the following command in the same directory as the csproj file to create a migration for our new contact model.

dotnet ef migrations add Contacts

Finally, run the following command to apply the migration to your database.

dotnet ef database update

Data Execution

For this example, I don’t really care to display the results in a UI so I am using the OnGetAsync of the Index page to run my queries. The following is my full index page model with a query that returns all the contacts. The rest of the post will just be showing the LINQ statements to query the database and not the full page model.

public class IndexModel : PageModel
{
    private readonly ApplicationDbContext _context;

    public IndexModel(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IActionResult> OnGetAsync()
    {
        var contacts = await _context.Contacts.ToListAsync();

        return Page();
    }
}

The above results in all the of the contacts seeded being return.

1 - Smith, Bob
2 - Smith, Sam
3 - Swit, Clark

Like Queries

As part of the Entity Framework Core 2.0 release EF.Functions.Like was added which allows usages of wildcards that were not possible using string function translation that was previously the only option.  The following query is an example of using like.

_context.Contacts
        .Where(c => EF.Functions.Like(c.LastName, "S_i%"))
        .ToListAsync();

While this would have been possible before I would imagine the query would have been nasty and involved some level of client-side evaluation. The result of the query is the same as above.

Greater Than/Less Than

Using String.Compare or value.CompareTo will allow you to do greater than or less than comparison on strings. For example String.Compare(value) > 0 give you a greater than and less than zero would be for less than. For example, here is a string comparison query along with the SQL that is generated.

_context.Contacts
        .Where(c => String.Compare(c.FirstName, "D") > 0 )
        .ToListAsync();

SELECT [c].[Id], [c].[Address], [c].[City], [c].[FirstName], [c].[LastName], [c].[State], [c].[Zip]
FROM [Contacts] AS [c]
WHERE [c].[FirstName] > N'D'

It is important to not try and use any of the overloads of String.Compare or you will end up with client-side evaluation of your query. The following is a query that uses one of the overloads and the SQL that is generated.

_context.Contacts
        .Where(c => String.Compare(c.FirstName, "D", StringComparison.Ordinal) > 0)
        .ToListAsync();

SELECT [c].[Id], [c].[Address], [c].[City], [c].[FirstName], [c].[LastName], [c].[State], [c].[Zip]
FROM [Contacts] AS [c]

Notice that the first query has a Where clause and the second one doesn’t. This means the second query will pull all the records to the client and then apply the filter.  While this is fine to a small amount of data please be careful with queries that are evaluated client-side as they can cause performance issues.

Both of the above queries return the following result.

2 - Smith, Sam

Wrapping Up

This is one of those posts that will be a reminder for me as much as anything. I do hope that is save you some time of clears up a bit how some of the way that Entity Framework Core handles strings.

Migration from ASP.NET Core 2.1 to 2.2

On December 4th .NET Core 2.2 including ASP.NET Core 2.2 and Entity Framework 2.2. In this post, I will be taking one of the projects used in the ASP.NET Basics series and converting it from ASP.NET 2.1.x to the new 2.2 version of ASP.NET Core. This will all be based on the official 2.2 migration guide.

The code before any changes can be found here. In the sample solution, this guide will be working with the Contacts project only.

Installation

Head over to the .NET download page and download the new version of the .NET Core SDK for version 2.2 which is available for Window, Linux and Mac.

After installation is done run the following command if you want to verify the SDK is installed.

dotnet --list-sdks

You should see 2.2.100 listed. If you are like me you might also see a few preview versions that would be good to uninstall.

If you are using Visual Studio make sure you are on at least version 15.9. If not updates can be downloaded from here.

Project File Changes

Right-click on the project and select Edit projectName.csproj.

Change the TargetFramework to netcoreapp2.2.

Before:
<TargetFramework>netcoreapp2.1</TargetFramework>
After:
<TargetFramework>netcoreapp2.2</TargetFramework>

Update any Microsoft packages with a version to 2.2.x the following is an example.

Before:
<PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.1.1" PrivateAssets="All" />

After:
<PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.0" PrivateAssets="All" />

If you want to use the new IIS in-process hosting model you also need to add the following line to a property group.

<AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>

The following is my full csproj for reference.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    <AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>
    <UserSecretsId>aspnet-Contacts-cd2c7b27-e79c-43c7-b3ef-1ecb04374b70</UserSecretsId>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.0" PrivateAssets="All" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="4.0.1" />
  </ItemGroup>

</Project>

Startup Changes

In Startup.cs update the compatibility version to enable the new 2.2 features.

Before:
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

After:
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

Wrapping Up

As with the migration to 2.1 the move to 2.2 is really easy to do. Make sure you check out the official migration guide for more details that may have not been covered by this project.

The code in its final state can be found here.

Entity Framework Core: Logging

The other day I was having to dig into some performance issues around a process that is using Entity Framework Core. As part of the process, I need to see the queries generated by Entity Framework Core to make sure they were not the source of the issue (they were not). I’m going to be making these changes using the Contacts project from my ASP.NET Core Basics repo if you want to see where I started from.

First, we will cover adding a logging provider. Next, I’m going to show you what I came up with and then I will show you the method suggested by the Microsoft docs (which I didn’t find until later).

Logging Providers

First, we need to do pick how we want the information logged. A good starting place is using one of the Microsoft provides which can be found on NuGet. Right-click on the project you want to add the logging to and click Manage NuGet Packages.

In the search box enter Microsoft.Extensions.Logging for a list of good list of logging options. For this post, we will be using the console logger provided by Microsoft. Select Microsoft.Extensions.Logging.Console and then click the Install button on the upper right side of the screen.

First Go

For my first try at this, all the change are in the ConfigureServices function of the Startup class. The following is the code I added at the end of the function that will log all the queries to the console window (if you are using IIS Express then use the Debug logger instead).

var scopeFactory = services.BuildServiceProvider()
                           .GetRequiredService<IServiceScopeFactory>();

using (var scope = scopeFactory.CreateScope())
{
    using (var context = scope.ServiceProvider
                              .GetRequiredService<ContactsContext>())
    {
        var loggerFactory = context.GetInfrastructure()
                                   .GetService<ILoggerFactory>();
        loggerFactory.AddProvider(new ConsoleLoggerProvider((_, __) => true, true));
    }
}

This code is creating a scope to get an instance of the ContactsContext and then using the context to get it’s associated logger factory and adding a console logger to it. This isn’t the cleanest in the world but gets the job done especially if this is just for a quick debug session and not something that will stay.

Microsoft Way

While the above works I ended up finding a logging page in the Entity Framework Core docs. After undoing the changes made above open the ContactsContext (or whatever your DBContext is) and add a class level static variable for a logger factory. This class level variable will be used to prevent memory and performance issues that would be caused by creating a new instance of the logging classes every time a context is created.

public static readonly LoggerFactory LoggerFactory = 
       new LoggerFactory(new[] {new ConsoleLoggerProvider((_, __) => true, true)});

Next, add/update an override to the OnConfiguring to use the logger factory defined above. The following is the full function in my case.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);

    optionsBuilder.UseLoggerFactory(LoggerFactory);
}

The Output

Either way, the following is an example of the output you will get with logging on.

The query is highlighted in the red box above. As you can see there is a lot of output, but there are options for filtering which are detailed in the docs.

Wrapping Up

Entity Framework Core does a great job, but the above gives you an option to check in on what it is doing. If you are using SQL Server you could also get the queries using SQL Server Profiler.

Entity Framework Core 2.1: Data Seeding

I am taking some time to explore some of the new features that came out with the .NET Core 2.1 release and this post is going to be a continuation of that process. The following are links to the other posts in this same vein.

Host ASP.NET Core Application as a Windows Service
ASP.NET Core 2.1: ActionResult<T>

Today we are going to be looking at a new feature added to Entity Framework to allow for data seeding. I am using the official docs for this feature as a reference.

Sample Application

We are going to use the .NET CLI to create a new application using the MVC template with individual authorization since it is one of the templates that come with Entity Framework already set up. If you have an existing project and need to add Entity Framework you can check out this post. The following is the command I used to create my project.

dotnet new mvc --auth Individual

Model

Before we get to data seeding we need to create an entity to seed. In this example, we will be creating a contact entity (surprise!). In the Models directory, I created a Contacts.cs file with the following contents.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

Next, in the Data directory, we are going to open the ApplicationDbContext class and add a DbSet for our new Contact entity. Added the following property to the class.

public DbSet<Contact> Contacts { get; set; }

Now that we have our DbContext setup lets add a migration for this new Contact entity using the following .NET CLI command.

dotnet ef migrations add Contacts -o Data/Migrations

Finally, run the following command to create/update the database for this application.

dotnet ef database update

Data Seeding

Now that our project is setup we can move on to actual data seeding. In Entity Framework Core data seeding is done in the OnModelCreating function of your DbContext class. In this example that is the ApplicationDbContext class. The following example shows using the new HasData method to add seed data for the Contact entity.

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<Contact>().HasData(
        new Contact 
        {
            Id = 1,
            Name = "Eric",
            Address = "100 Main St",
            City = "Hometown",
            State = "TN",
            Zip = "153789"
        }
    );
}

Data seeding is handled via migrations in Entity Framework Core, which is a big difference from previous versions. In order to get our seed data to show up, we will need to create a migration which can be done using the following command.

dotnet ef migrations add ContactSeedData -o Data/Migrations

Then apply the migration to your database using the following command.

dotnet ef database update

Looking at the code that the migration created you can see that it is just inserting the data.

public partial class ContactSeedData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Contacts",
            columns: new[] { "Id", "Address", "City", "Name", "State", "Zip" },
            values: new object[] { 1, "100 Main St", "Hometown", "Eric", "TN",
                                   "153789" });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Contacts",
            keyColumn: "Id",
            keyValue: 1);
    }
}

The above works great on new databases or new tables but can cause issues if you are trying to add seed data to an existing database. Check out Rehan’s post on Migrating to Entity Framework Core Seed Data for an option on how to deal with this.

Wrapping up

I am very happy to see that we have a way to prepopulate data in Entity Framework Core it will make some scenarios, such as mostly static data, much easier to deal with. One downside I see to the migration approach is the inability to have some built-in test data since the migrations will always be applied to your databases.

Add Entity Framework Core to an Existing ASP.NET Core Project

I had a Web API application that I was using to test something that had no database interaction at all that I needed to add database interaction too. Going through my posts I didn’t find a guide to add Entity Framework Core to an existing project, so that is what this post is going to cover. We will be using SQLite for our database.

Project Creation

The project we are starting with is just a new ASP.NET Core Web API created using the .NET CLI using the following command from a command prompt.

dotnet new webapi

This gives us a new Web API with no database interaction and makes sure we are all on the same page for the rest of the post.

Project File Changes

Open the csproj file associated with your project. First, we need to add a package reference to the Entity Framework Core tools.

Before:
<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />
</ItemGroup>

After:
<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" PrivateAssets="All" />
</ItemGroup>

Next, we need to add a reference to the Entity Framework Core Tools for the .NET CLI.

Before:
<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.2" />
</ItemGroup>

After:
<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.2" />
  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
</ItemGroup>

The final change to the project file is only needed if you are using SQLite and it is to keep the database file from showing up in the file list. Add the following and adjust app.db to match your database name.

<ItemGroup>
  <None Update="app.db" CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>

App Settings

Now we need to store the database connection string. Again we are using SQLite in this example so if you are trying to use SQL Server (include LocalDb) your connection string will look much different. This is also a place where you need to replace app.db with the database name you want to use.

Open the appsettings.json file and add a ConnectionStrings section similar to the following.

"ConnectionStrings": {
  "DefaultConnection": "DataSource=app.db"
}

The following is my full settings file after the change just to make sure the context is clear.

{
  "ConnectionStrings": {
    "DefaultConnection": "DataSource=app.db"
  },
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  }
}

Model and Db Context

If you are a long time reader it will be no surprise that the model we will be using is that of a contact. The following is my full contact model.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Now that we have a model we need to add a DBContext.

public class ContactsDbContext : DbContext
{
    public DbSet<Contact> Contacts { get; set; }

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

    }
}

Startup

Now that we have the application settings, model, and context from above open up the Startup class and in the ConfigureServices function add the following code to get the ContactsDbContext into the container.

services.AddDbContext<ContactsDbContext>(options => 
    options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));

Initial Migration

Now that the project is all set to go let’s add an initial migration that will get the table for our contacts set up. From a command prompt in the project directory run the following command.

dotnet ef migrations add Contacts

This will add a Migrations directory to the project with the code needed to add the contacts table. If you want to go ahead and apply the migration to the database run the following command.

dotnet ef database update

Since we didn’t have a database yet the above command creates one which will show up in the root of our project with a name that matches our application settings since we are using SQLite.

Wrapping Up

Adding Entity Framework Core to a project is pretty easy, but not something I do a lot, so this will serve as a good reminder of the steps. Hope it helps you out as well.

Entity Framework Core with Postgres

With last week’s post, I now have Postgres running in a Docker container. This post is going to cover using Entity Framework Core with Postgres which will happen to be running in the Docker container from last week, but that bit isn’t a requirement for this post. This post is going to be very similar to this one which covered Entity Framework Core with SQLite.

Starting Point

Using Visual Studio 2017 I started with a new ASP.NET Core project using the Web Application template with Individual User Accounts for Authentication. Using Individual User Accounts is an easy way to get all the Entity Framework Core stuff setup.

Add Postgres Nuget Package

Right-click on the project file and click Manage NuGet Packages.

On the Browse tab search for Npgsql.EntityFrameworkCore.PostgreSQL and then click install.

Configuration Changes

Open the appsettings.json file and change the DefaultConnection in the ConnectionString section to a valid Postgres connection string as in the following example.

Before:
"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-Postgres-53bc9b9d-9d6a-45d4-8429-2a2761773502;Trusted_Connection=True;MultipleActiveResultSets=true"
}

After:
"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=aspnet-Postgres-53bc9b9d-9d6a-45d4-8429-2a2761773502;UserId=yourUserName;Password=yourPassword"
}

Not that a user ID and password are needed. If you are going to be checking your project into source control I recommend in addition to the above you only store the connection string with the real user ID and password in user secrets which don’t get checked into source control. You can find more information on user secrets here.

Startup Changes

The final change needed before running the application is in the ConfigureServices function of the Startup class to switch out SQL Server for Postgres. The following shows an example of the change for the ApplicationDbContext.

Before:
services.AddDbContext<ApplicationDbContext>(options =>
    options
      .UseSqlServer(Configuration
                    .GetConnectionString("DefaultConnection")));

After:
services.AddDbContext<ApplicationDbContext>(options =>
    options
      .UseNpgsql(Configuration
                 .GetConnectionString("DefaultConnection")));

Run the App

Running the application at this point will work fine until you hit a function that wants to talk to the database. For example, attempting to register a user would result in the following error.

If you see this error don’t panic just follow the instructions and they will get you going. The simplest solution is to just click the blue Apply Migrations button and continue your testing.

Wrapping Up

The application is now ready to go using Postgres. The Entity Framework Core team, as well as the Postgres Entity Framework Core Provider team, have done a great job making everything work with no pain.

The official docs for the provider can be found here. I would love to offer some tooling recommendations for working with Postgres, but I haven’t been working with it enough yet to provide any. If you have any Postgres tools you recommend please leave a comment.

The code in its final state, which has been expanded to include my standard contacts example, can be found here.

Identity Server: Migration to version 2.1 and Angular HTTP Changes

Version 2.1 of Identity Server 4 was released a few weeks and this post is going to cover updating my sample project to the latest version. The starting point of the code can be found here. We are going to tackle this in sections as there are updates needed for an ASP.NET Core Update, Identity Server Update, and some broken bits in Angular.

ASP.NET Core Update

The sample projects were all on ASP.NET Core version 2.0.0. For each project right-click and select Edit ProjectName.csproj. Make the following change.

Before:
<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.0" />

After:
<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.3" />

Identity Server Update

Right-click the Identity App project and select Edit IdentityApp.csproj. Next, make the following changes.
Before:
<PackageReference Include="IdentityServer4.EntityFramework" Version="2.0.0" />

After:
<PackageReference Include="IdentityServer4.EntityFramework" Version="2.1.0" />

Next, need to add a couple of Entity Framework migrations to see if there were any data changes with the following commands from a command prompt in the Identity App project directory.

dotnet ef migrations add Configration21 -c ConfigurationDbContext -o Data/Migrations/IdentityServer/Configuration
dotnet ef migrations add PersistedGrant21 -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrant

Turns out that there were no data changes for this version so if you are on version 2.0.0 you can skip this step.

Angular Issues

I’m not sure how I didn’t hit this issue on the last update post, but the Client App needs to be changed to use the new Angular HttpClient. I got the following error when trying to run the client application.

An unhandled exception occurred while processing the request.

NodeInvocationException: No provider for PlatformRef!
Error: No provider for PlatformRef!
at injectionError
After some digging, I tracked the issue down to using HttpModule instead of HttpClientModule. To make this transition we need to make a few changes. In the app.module.shared.ts make the following changes to the imports section.
Before:
import { HttpModule } from '@angular/http';

After:
import { HttpClientModule } from '@angular/common/http';

Next, in the imports array make the following change.

Before:
HttpModule

After:
HttpClientModule

Next, in the webpack.config.vendor.js fille add the following to the vendor array.

'@angular/common/http'

The last changes are to the auth.service.ts and they are extensive so instead of going through them I’m just going to post the full class after all the changes.

import { Injectable, Component, OnInit, OnDestroy, Inject } from '@angular/core';
import { HttpClient, HttpHeaders } from '@angular/common/http';
import { Observable } from 'rxjs/Rx';
import { Subscription } from 'rxjs/Subscription';

import { OidcSecurityService, OpenIDImplicitFlowConfiguration } from 'angular-auth-oidc-client';

@Injectable()
export class AuthService implements OnInit, OnDestroy {
    isAuthorizedSubscription: Subscription;
    isAuthorized: boolean;

    constructor(public oidcSecurityService: OidcSecurityService,
        private http: HttpClient,
        @Inject('ORIGIN_URL') originUrl: string,
        @Inject('IDENTITY_URL') identityUrl: string
    ) {
        const openIdImplicitFlowConfiguration = new OpenIDImplicitFlowConfiguration();
        openIdImplicitFlowConfiguration.stsServer = identityUrl;
        openIdImplicitFlowConfiguration.redirect_url = originUrl + 'callback';
        openIdImplicitFlowConfiguration.client_id = 'ng';
        openIdImplicitFlowConfiguration.response_type = 'id_token token';
        openIdImplicitFlowConfiguration.scope = 'openid profile apiApp';
        openIdImplicitFlowConfiguration.post_logout_redirect_uri = originUrl + 'home';
        openIdImplicitFlowConfiguration.forbidden_route = '/forbidden';
        openIdImplicitFlowConfiguration.unauthorized_route = '/unauthorized';
        openIdImplicitFlowConfiguration.auto_userinfo = true;
        openIdImplicitFlowConfiguration.log_console_warning_active = true;
        openIdImplicitFlowConfiguration.log_console_debug_active = false;
        openIdImplicitFlowConfiguration.max_id_token_iat_offset_allowed_in_seconds = 10;

        this.oidcSecurityService.setupModule(openIdImplicitFlowConfiguration);

        if (this.oidcSecurityService.moduleSetup) {
            this.doCallbackLogicIfRequired();
        } else {
            this.oidcSecurityService.onModuleSetup.subscribe(() => {
                this.doCallbackLogicIfRequired();
            });
        }
    }

    ngOnInit() {
        this.isAuthorizedSubscription = this.oidcSecurityService.getIsAuthorized().subscribe(
            (isAuthorized: boolean) => {
                this.isAuthorized = isAuthorized;
            });
    }

    ngOnDestroy(): void {
        this.isAuthorizedSubscription.unsubscribe();
        this.oidcSecurityService.onModuleSetup.unsubscribe();
    }

    getIsAuthorized(): Observable<boolean> {
        return this.oidcSecurityService.getIsAuthorized();
    }

    login() {
        console.log('start login');
        this.oidcSecurityService.authorize();
    }

    refreshSession() {
        console.log('start refreshSession');
        this.oidcSecurityService.authorize();
    }

    logout() {
        console.log('start logoff');
        this.oidcSecurityService.logoff();
    }

    private doCallbackLogicIfRequired() {
        if (typeof location !== "undefined" && window.location.hash) {
            this.oidcSecurityService.authorizedCallback();
        }
    }

    get(url: string): Observable<any> {
        return this.http.get<any>(url, { headers: this.getHeaders() });
    }

    put(url: string, data: any): Observable<any> {
        const body = JSON.stringify(data);
        return this.http.put<any>(url, body, { headers: this.getHeaders() });
    }

    delete(url: string): Observable<any> {
        return this.http.delete<any>(url, { headers: this.getHeaders() });
    }

    post(url: string, data: any): Observable<any> {
        const body = JSON.stringify(data);
        return this.http.post<any>(url, body, { headers: this.getHeaders() });
    }

    private getHeaders() {
        let headers = new HttpHeaders();
        headers = headers.set('Content-Type', 'application/json');
        return this.appendAuthHeader(headers);
    }

    private appendAuthHeader(headers: HttpHeaders) {
        const token = this.oidcSecurityService.getToken();

        if (token === '') return headers;

        const tokenValue = 'Bearer ' + token;
        return headers.set('Authorization', tokenValue);
    }
}

With all those changes made run the following two commands in a command prompt in the Client App project directory.

node node_modules/webpack/bin/webpack.js --config webpack.config.vendor.js
node node_modules/webpack/bin/webpack.js

Wrapping up

This post ended up being more about Angular than Identity Server, but it is nice to have everything upgraded to the latest and working.

The files in the completed can be found here.

Identity Server: Migration to ASP.NET Core 2

The Identity App that is part of my IdentityServer sample project is the last application I have on GitHub (of the ones that will get upgraded) that needs an upgrade to ASP.NET Core. The starting point of the project before any changes can be found here. This post assumes that you have already followed my generic ASP.NET Core 2 migration post, which can be found here, on the project you are migrating. One final note of caution this post has been written using the RC1 version of the Identity Server NuGet packages and then moved to the final version so there will be two different related pull requests that will have to be looked at to get the full picture of all the changes.

Package Changes

The first change is to get a version of the Identity Server packages what will work from ASP.NET Core 2.

Before:
<PackageReference Include="IdentityServer4.AspNetIdentity" Version="1.0.1" />
<PackageReference Include="IdentityServer4.EntityFramework" Version="1.0.1" />

After:
<PackageReference Include="IdentityServer4.AspNetIdentity" Version="2.0.0" />
<PackageReference Include="IdentityServer4.EntityFramework" Version="2.0.0" />

Database Initialization

I wasted a lot of time on finding out this was an issue when I was trying to create Entity Framework migrations and kept getting Unable to create an object of type ‘ApplicationDbContext’. Add an implementation of ‘IDesignTimeDbContextFactory‘ errors. The gist is database initialization needs to be moved out of Startup and context constructors.

Let’s start with the ApplicationDbContext and remove the following code from the constructor as well as the associated property.

if (_migrated) return;
Database.Migrate();
_migrated = true;

Next, in the Configure function of the Startup class remove the following line.

IdentityServerDatabaseInitialization.InitializeDatabase(app);

We still need the database initialization code to run, but where should that be done? In the Main function of the Program class seems to be the new recommended location. The following is the new Main function.

public static void Main(string[] args)
{
    var host = BuildWebHost(args);

    using (var scope = host.Services.CreateScope())
    {
        var services = scope.ServiceProvider;

        try
        {
            IdentityServerDatabaseInitialization.InitializeDatabase(services);
        }
        catch (Exception ex)
        {
            var logger = services.GetRequiredService<ILogger<Program>>();
            logger.LogError(ex, "An error occurred Initializing the DB.");
        }
    }

    host.Run();
}

InitializeDatabase now needs to take an IServiceProvider instead of an IApplicationBuilder. This forced a lot of lines to change so the following is the full class.

public static class IdentityServerDatabaseInitialization
{
    public static void InitializeDatabase(IServiceProvider services)
    {
        PerformMigrations(services);
        SeedData(services);

    }

    private static void PerformMigrations(IServiceProvider services)
    {
        services
          .GetRequiredService<ApplicationDbContext>()
          .Database.Migrate();
        services
          .GetRequiredService<ConfigurationDbContext>()
          .Database.Migrate();
        services
          .GetRequiredService<PersistedGrantDbContext>()
          .Database.Migrate();
    }

    private static void SeedData(IServiceProvider services)
    {
        var context = services.GetRequiredService<ConfigurationDbContext>();

        if (!context.Clients.Any())
        {
            foreach (var client in Config.GetClients())
            {
                context.Clients.Add(client.ToEntity());
            }
            context.SaveChanges();
        }

        if (!context.IdentityResources.Any())
        {
            foreach (var resource in Config.GetIdentityResources())
            {
                context.IdentityResources.Add(resource.ToEntity());
            }
            context.SaveChanges();
        }

        if (!context.ApiResources.Any())
        {
            foreach (var resource in Config.GetApiResources())
            {
                context.ApiResources.Add(resource.ToEntity());
            }
            context.SaveChanges();
        }
    }
}

Startup Changes

Most of the changes to the Startup class are in the ConfigureServices function, but some cross with the Configure function as well. The existing AddIdentityServer extension has multiple changes especially if you are using Entity Framework for your configuration data. AddTemporarySigningCredential is now AddDeveloperSigningCredential. The following is the new version including configuration data.

services.AddIdentityServer()
    .AddDeveloperSigningCredential()
    .AddAspNetIdentity<ApplicationUser>()
    .AddConfigurationStore(options =>
    {
      options.ConfigureDbContext = builder =>                 
        builder.UseSqlite(Configuration
                          .GetConnectionString("DefaultConnection"),
                          db => db.MigrationsAssembly(migrationsAssembly));
    })
    .AddOperationalStore(options =>
    {
      options.ConfigureDbContext = builder =>
        builder.UseSqlite(Configuration
                          .GetConnectionString("DefaultConnection"),
                          db => db.MigrationsAssembly(migrationsAssembly));
    });

The way to handle registration of external authentication has changed as well. For example, this application uses Twitter. The UseTwitterAuthentication call in the Configure function needs to be removed. The following added to the bottom of the ConfigureServices is now the proper way to add external authentication providers.

services.AddAuthentication().AddTwitter(twitterOptions =>
{
    twitterOptions.ConsumerKey = 
         Configuration["Authentication:Twitter:ConsumerKey"];
    twitterOptions.ConsumerSecret = 
         Configuration["Authentication:Twitter:ConsumerSecret"];
});

Entity Framework

The new changes in Identity from the ASP.NET Core team included a new foreign key which is one of the things that Sqlite migrations can’t actually do. Since I don’t really have any data I care about I just deleted the database and the existing migrations and snapshots and regenerated everything. If you are using Sqlite and this isn’t an option for you check out this post for some options. If you aren’t using Sqlite then the migrations should work fine. The following are the commands to generate migrations for the 3 contexts that the Identity Application uses.

dotnet ef migrations add InitConfigration -c ConfigurationDbContext -o Data/Migrations/IdentityServer/Configuration

dotnet ef migrations add InitPersistedGrant -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrant

dotnet ef migrations add InitApplication -c ApplicationDbContext -o Data/Migrations

Quick Start UI Changes

As part of going from the RC1 version to the Final version, the Identity Server team updated the UI and related bits to be in line with the new features added in the ASP.NET Core 2.0 release. Turns out that resulted in a lot of changes. Since I haven’t done any custom work in this area of my Identity Application I deleted the related files in my local project and pulled from the ASP.NET and Entity Framework Combined sample. I am going to give a good idea of all the files I replace, but in case I miss something GitHub will have the full story.

In the Controllers folder replace AccountController.cs and ManageController.cs. Add or replace the following folders:  ExtensionsModelsQuickstartServices, and Views.

Application Insights Error

I ran into the following error.

System.InvalidOperationException: No service for type ‘Microsoft.ApplicationInsights.AspNetCore.JavaScriptSnippet’ has been registered.

You may or may not see it, but if you do open the _Layout.cshtml and remove the following two lines.

@inject Microsoft.ApplicationInsights.AspNetCore.JavaScriptSnippet JavaScriptSnippet


@Html.Raw(JavaScriptSnippet.FullScript)

Wrapping up

If you hit any issues not covered above make sure and check out the breaking changes issue. The completed code can be found here for part 1 and here for part 2.