.NET Core: Windows Compatibility Pack

Windows Compatibility Pack

A little over a year ago Microsoft released the Windows Compatibility Pack for .NET Core that filled in some gaps in .NET Core APIs if your application doesn’t need to run cross-platform. While some of the APIs included in the compatibility pack was added back when support for .NET Standard 2 was released, such as System.Data.SQL client. Some of the APIs will never make it into the .NET Standard because they are Windows only constructs such as registry access.

Since it has been such a long time since the compatibility pack was released I thought this post would be a good reminder. In this post, we will create a new .NET Core application and using the Windows Compatibility Pack to access the registry.

Application Creation

Use the following command from a command prompt to create a new .NET Core console application.

dotnet new console

Use the following command to add a reference to the compatibility pack NuGet package.

dotnet add package Microsoft.Windows.Compatibility

Using the Registry

The following is the full code for the application. This code may not be the best practice, but it does demonstrate the usage of the registry.

class Program
{
    static void Main(string[] args)
    {
        var thing = "World";

        if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
        {
            using (var regKey = Registry
                                .CurrentUser
                                .CreateSubKey(@"Software\Testing\Test"))
            {
                thing = regKey.GetValue("ThingText")?.ToString() ?? thing;
                regKey.SetValue("ThingText", "Registry");
            }
        }

        Console.WriteLine($"Hello {thing}!");
    }
}

Take special note of the if statement surrounding the registry access as it allows you to check what platform you are running on and vary your implementation, which is very helpful if your application is actually run cross-platform, but you need some slight differences when running on a particular OS.

The application should output “Hellow World!” the first run and “Hello Registry!” the second run.

Wrapping Up

If you were creating a new application it would be best to stay away from platform specific API as much as possible and stick with the APIs defined by the .NET Standard. If you are trying to convert an existing application I could see the compatibility pack speeding your conversion without having to rewrite part of the application that happens to us Windows-based APIs.

Entity Framework Core: Show Parameter Values in Logging

Back in October, I did a post on Entity Framework Core: Logging which covers enabling logging for Entity Framework Core. This post is going to expand on that previous post and show you how to get the parameter values used in the queries in addition to the SQL statements being used.

Review

This post will not be covering how to set up a logger please see this previous post for those details. The following is the kind of information you get in the log based on the previous post.

Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (41ms) [Parameters=[@__id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Address], [m].[City], [m].[Email], [m].[Name], [m].[Phone], [m].[PostalCode], [m].[State]
FROM [Contact] AS [m]
WHERE [m].[Id] = @__id_0

This is helpful when you need to verify the queries Entity Framework is producing, but if you are trying to track down a data related problem not knowing what parameters values are being used can be a pain.

Enable Parameter Values in Logging

Entity Framework Core provides an option to enable sensitive data logging. To enable this option open the Startup class and in the ConfigureServices function make the following change to the AddDbContext call for the DbContext you want the option on for.

Before:
services
  .AddDbContext<ContactsContext>(options => 
     options.UseSqlServer(Configuration["Data:ContactsContext:ConnectionString"]));

After:
services
  .AddDbContext<ContactsContext>(options =>
  {                         
     options.UseSqlServer(Configuration["Data:ContactsContext:ConnectionString"]);
     options.EnableSensitiveDataLogging();
  });

Now running the same query as above you will see the following output.

Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[@__id_0='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [m].[Id], [m].[Address], [m].[City], [m].[Email], [m].[Name], [m].[Phone], [m].[PostalCode], [m].[State]
FROM [Contact] AS [m]
WHERE [m].[Id] = @__id_0

As you can see with enable sensitive data logging we get the value of the ID parameter instead of a question mark.

Before: [Parameters=[@__id_0='?' (DbType = Int32)]
After:  [Parameters=[@__id_0='1' (DbType = Int32)]

Wrapping Up

This is a handy option when you need it. Just use it with care as it could expose data that should be kept private.

Creating Desktop Applications in .NET Core 3

In my job, I still do a fair amount of work desktop related work which means when I heard that .NET Core 3 is going to bring support for Winforms and WPF I got pretty excited. This post is going to show you how to installing the preview of .NET Core 3 and then using the .NET CLI to a new Winforms or WPF application.

This information was covered in the .NET Core 3 Preview 1 announcement from December, but I thought I might as well document it here as I was trying it out.

Download and Install

Head over to the .NET Core 3 download  page and select the SDK for your OS. Keep in mind that Winforms and WPF will only run on Windows machines unlike the rest of .NET which is cross-platform. After the download is complete run the installer and follow the prompts.

Project Creation

Using the .NET CLI you can use the following command to create a Winform application.

dotnet new winforms

Of the following to create a WPF application.

dotnet new wpf

After the project is created for either type you can use the following command to run the application.

dotnet run

The following is an example of what you would see for a new WPF application.

Limitations

If you are going to be playing with the desktop-based framework I recommend that you grab the preview of Visual Studio 2019. Even with the preview, there are no designers available Winforms or WPF as of the time of this writing.

Wrapping Up

I know Microsoft has shown some neat demos, but in this first preview doesn’t have much tooling around it so if you are going to do much more than the new applications as I did above you are going to be in for a lot of manual work. Please don’t take this as a dig, as I said above I am very excited about this functionality.

If you are wondering about what versions of Windows .NET Core 3 will be supported on that information can be found here.

ASP.NET Core Configuration Issue with In Process Hosting

Since ASP.NET Core 2.2 was released I have been working on getting all my different applications updated and using in-process hosting. I pretty quickly hit an issue with an application that uses SQLite. As soon as the application tried to access the database I ended up with the following error.

SqliteException: SQLite Error 14: ‘unable to open database file’. Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db) Microsoft.Data.Sqlite.SqliteConnection.Open() Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(bool errorsExpected)

Issue

After some Googling, I found an issue on GitHub that details the problem. It turns out that when the application gets its current directory it is returning the path to the IIS process that is hosting the application instead of the directory when the application is.

Work Around

On another GitHub issue, I found a link to a recommended workaround. Add the following class somewhere in your application. This code comes here.

internal class CurrentDirectoryHelpers
{
    internal const string AspNetCoreModuleDll = "aspnetcorev2_inprocess.dll";

    [System.Runtime.InteropServices.DllImport("kernel32.dll")]
    private static extern IntPtr GetModuleHandle(string lpModuleName);

    [System.Runtime.InteropServices.DllImport(AspNetCoreModuleDll)]
    private static extern int http_get_application_properties(ref IISConfigurationData iiConfigData);

    [System.Runtime.InteropServices.StructLayout(System.Runtime.InteropServices.LayoutKind.Sequential)]
    private struct IISConfigurationData
    {
        public IntPtr pNativeApplication;
        [System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.BStr)]
        public string pwzFullApplicationPath;
        [System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.BStr)]
        public string pwzVirtualApplicationPath;
        public bool fWindowsAuthEnabled;
        public bool fBasicAuthEnabled;
        public bool fAnonymousAuthEnable;
    }

    public static void SetCurrentDirectory()
    {
        try
        {
            // Check if physical path was provided by ANCM
            var sitePhysicalPath = Environment.GetEnvironmentVariable("ASPNETCORE_IIS_PHYSICAL_PATH");
            if (string.IsNullOrEmpty(sitePhysicalPath))
            {
                // Skip if not running ANCM InProcess
                if (GetModuleHandle(AspNetCoreModuleDll) == IntPtr.Zero)
                {
                    return;
                }

                IISConfigurationData configurationData = default(IISConfigurationData);
                if (http_get_application_properties(ref configurationData) != 0)
                {
                    return;
                }

                sitePhysicalPath = configurationData.pwzFullApplicationPath;
            }

            Environment.CurrentDirectory = sitePhysicalPath;
        }
        catch
        {
            // ignore
        }
    }
}

Finally, in the Main function of the Program class add the following line as the first thing in the function.

CurrentDirectoryHelpers.SetCurrentDirectory();

Wrapping Up

With the above changes, all will work as expected. It is my understanding that this issue will be addressed at some point in the future as a patch so this should just be a temporary fix.

Dapper Using Lists

Last week’s post on ASP.NET Core with Dapper covered the very basics of using Dapper in an ASP.NET Core project. This week we will be changing up the sample code from last week to show using Dapper to deal with lists instead of just a single object at a time.

This post is going to be pretty short, but I thought it was important to point out that Dapper can execute a single query multiple times by passing it an IEnumerable.

The Sample

The following sample connects to the database, deletes all the existing contacts, seeds the contact table with 3 contacts (this is our list), and then selects all the contacts back out (this comes out as an IEnumerable).

using (var connection = 
           new SqlConnection(_configuration
                             .GetConnectionString("DefaultConnection")))
{
    connection.Open();

    connection.Execute("DELETE Contacts");

    var seedContacts = new List<Contact>
                       {
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           },
                           new Contact
                           {
                               Name = "Teddy Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           },
                           new Contact
                           {
                               Name = "Kate Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           }
                       };

    connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                               Subregion, Email) 
                         VALUES (@Name, @Address, @City, 
                                 @Subregion, @Email)",
                       seedContacts);
    
    var allContacts = connection
                      .Query<Contact>(@"SELECT Id, Name, Address, City, 
                                               Subregion, Email 
                                        FROM Contacts");
}

As expected the end result contains three contact records.

Wrapping Up

Hopefully, this quick little post helps fill in one of the gaps of last week’s post. Make sure and check out the Dapper GitHub page in addition to features I have covered in these two posts it also supports a lot of other functionality such as stored procedures, multiple result sets, etc. The code in its final state can be found here.

ASP.NET Core with Dapper

Entity Framework Core has been a the heart of a lot of the post I have done recently. I thought it would be a good change of pace to try out one of the alternatives to Entity Framework Core and give Dapper a try.

I tried to find a good example of the difference between micro ORMs like Dapper and full ORMs like Entity Framework Core, but all the ones I found ended up pushing one option over the other.  I’m not looking to say one is better than the other, but to add a new tool to my toolbelt.

Basically, the differences seem to come down to micro ORMs provide significantly fewer features than a full ORM but come with a higher level of performance. Which is the right choice is going to vary by project.

Sample Project

We will be using a new Razor Pages application as the starting point for the sample application. It currently doesn’t have any database access setup. The code for the starting point of the sample can be found here.

I’m going to be using an existing database created for the SQL Server example in my Entity Framework sample project. Below is a Contacts table generated from that sample project in case you want to manually generate the table.

CREATE TABLE [dbo].[Contacts] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (MAX) NULL,
    [Address]    NVARCHAR (MAX) NULL,
    [City]       NVARCHAR (MAX) NULL,
    [Subregion]  NVARCHAR (MAX) NULL,
    [PostalCode] NVARCHAR (MAX) NULL,
    [Phone]      NVARCHAR (MAX) NULL,
    [Email]      NVARCHAR (MAX) NULL,
    [Timestamp]  ROWVERSION     NULL
);

Add Dapper Nuget Package

Using your favorite way to add a NuGet package add a reference to the Dapper package. I’m going to do it using Visual Studio by right-clicking on the project and selecting Manage NuGet Packages.

On the next screen, from the Browse tab use the Search Box to search for Dapper. Select the Dapper package and click Install.

Add Connection String Configuration

We are going to store the database connection string in the appsettings.json file. Open the file and add a ConnectionString section with a DefaultConnection that contains the database connection string. This exact setup isn’t required I’m just following the way configuration is set up for Entity Framework base projects. The following is my full configuration file with the new section at the top.

{
  "ConnectionStrings": {
    "DefaultConnection": "Your connection string"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Contact Model

Add a Contact class in a Models folder which will be used to represent our data. The following is the full model class I will be using in this example.

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; }
}

Test Setup

To keep things simple I will not be dealing with any UI other than as a way to run the Dapper related code. As such we will be using the Page Modle of the Index page to run the sample code. This is in no way an example of the best practice setup and is only done to demo Dapper in the simplest setup possible.

The following is the full page model with the configuration for the application being injected via the constructor. All the changes passed this point will be in the OnGet function.

public class IndexModel : PageModel
{
    private readonly IConfiguration _configuration;

    public IndexModel(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public void OnGet()
    {

    }
}

Using Dapper

Dapper provides a number of extension methods off of the IDbConnection interface. For this example, we will use these extensions to check to see if a specific contact exists, if it doesn’t then insert it, and finally select the contact back out of the database.

The first step is to open a connection. In this example, we are using SQL Server, but Dapper doesn’t really care and will work with any ADO.NET provider. The following code opens a SQL connection using the connection string from appsettings.json.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
    connection.Open();
}

Next, we are going to query the Contacts table for the ID of a specific contact.

if (connection.QueryFirstOrDefault<int?>(@"SELECT Id 
                                           FROM Contacts 
                                           WHERE Name = @Name",
                                         new {Name = "Charlie Plumber"}) == null)
{
}

As you can see from the above you there is nothing special about the SQL being written. For this query, we are using an anonymous type to pass the query a name filter which will be translated to a SqlParameter which ensures the query won’t all things like SQL injection. Note that the parameter name in the SQL string must match the name of the corresponding property on the object being passed to the query.

This next example is the contact insert if the contact wasn’t found in the previous query.

connection.Execute(@"INSERT INTO Contacts (Name, Address, City, Subregion, Email)
                     VALUES (@Name, @Address, @City, @Subregion, @Email)",
                   new Contact
                   {
                       Name = "Charlie Plumber",
                       Address = "123 Main St",
                       City = "Nashville",
                       Subregion = "TN",
                       Email = "[email protected]"
                   });

Again the SQL is exactly what you would expect, which is one of the great parts about Dapper if you are familiar with SQL.

This last example is a combination of all the previous examples with a select to get out the contact details of the contact in question.

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
    connection.Open();

    if (connection
           .QueryFirstOrDefault<int?>(@"SELECT Id 
                                        FROM Contacts 
                                        WHERE Name = @Name",
                                      new {Name = "Charlie Plumber"}) == null)
    {
        connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                                   Subregion, Email) 
                             VALUES (@Name, @Address, @City, 
                                     @Subregion, @Email)",
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           });
    }

    var charile = 
      connection.QueryFirstOrDefault<Contact>(@"SELECT Id, Name, Address, 
                                                       City, Subregion, Email 
                                                FROM Contacts 
                                                WHERE Name = @Name",
                                              new {Name = "Charlie Plumber"});
}

Wrapping Up

This first round of playing with Dapper was fun. I write a lot of SQL in my day job so it was kind of nice seeing the explicit SQL vs the magic of a full ORM. I can tell you from doing this sample I would miss Entity Framework’s database creation and migration capabilities.

The code in its final state can be found here.

Entity Framework Core: Client-side Evaluation

My post a few weeks ago on Entity Framework Core: String Filter Tips may have come across as too harsh on client-side evaluation based on what I saw on Reddit. My point wasn’t that you shouldn’t use client-side evaluation when you need too, but that you should be making a choice when to use client-side evaluation and it is very easy to miss when a query is running client-side.

TimeRemove did a great job explaining ways to detect and deal with client-side evaluation which this post will review. The sample application is the same one used in  Entity Framework Core: String Filter Tips as defined in the Sample Application section.

Sample Client-side Query

The following is the query we will be using to trigger client-side evaluation.

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

No Changes

Running this query with no change to the application will actually give you the following warning in the Debug Window.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression ‘where (Compare([c].FirstName, “D”, Ordinal) > 0)’ could not be translated and will be evaluated locally.

I’m not sure how long this warning has been there, but this is the first time I have noticed it. Good reminder to make sure and check the debug window more often.

Throw Exception for Client-side Evaluation

Making the following change to the DbContext will cause Entity Framework Core to throw an exception when it does client-side evaluation instead of just showing a warning. In this example, we are overriding the OnConfiguring function of our DbContext and changing to throw an exception if the application is built in debug mode.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
#if DEBUG
    optionsBuilder.ConfigureWarnings(w => 
          w.Throw(RelationalEventId.QueryClientEvaluationWarning));
#endif

    base.OnConfiguring(optionsBuilder);
}

Now running the sample query results in the following exception.

InvalidOperationException: Error generated for warning ‘Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression ‘where (Compare([c].FirstName, “D”, Ordinal) > 0)’ could not be translated and will be evaluated locally.’. This exception can be suppressed or logged by passing event ID ‘RelationalEventId.QueryClientEvaluationWarning’ to the ‘ConfigureWarnings’ method in ‘DbContext.OnConfiguring’ or ‘AddDbContext’.

There are times when a client-side evaluation is what you want. How do you deal with that need and leave the exception enabled so you get a notification when a client-side evaluation is happening? To accomplish this you will need to split your query into multiple parts running the server evaluated parts and then in a different statement running the client evaluated parts. The following is our sample query broken up so that no exception is thrown.

//Server evaluation
var contacts = await _context.Contacts.ToListAsync();

//Client evaluation
contacts = 
    contacts
        .Where(c => String.Compare(c.FirstName, "D", StringComparison.Ordinal) > 0)
        .ToList();

Wrapping Up

If I introduced any confusion I hope this post helps clear it up. Just to be 100% clear client-side evaluation isn’t bad, but I feel it is important for it to be a choice that the developer has made. I hope the above will give you the tools to be clear on when you are choosing client-side evaluation.

Thanks again to TimeRemove for the clarifications to my original post.

Entity Framework Core: SQL Server Concurrency Checks

Over the last couple of weeks, I have covered concurrency checks in SQLite and Postgres. This week I’m expanding my Entity Framework sample repo to include a SQL Server based example project in order to demo concurrency checks. for SQL Server. The repo with the SQL Server project added, but before the concurrency checks can be found here.

Note that all the changes in this post will be made under the SqlServer folder.

Context Changes and Data Migration

SQL Server has a built-in column type of rowversion which is automatically generated with a unique binary number when a row is inserted or updated. In order to utilize the rowversion column type via Entity Framework, we need to add a new column to our model with the Timestamp attribute like the following from the sample code in the Contact model.

[Timestamp] 
public byte[] Timestamp { get; set; }

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.

dotnet ef migrations add ContactTimestamp --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 test add 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>()
                                         .UseSqlServer("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>()
                                         .UseSqlServer("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

Of the three providers, Postgres seems to have the simplest implementation, not that concurrency is going to be how you choose your database provider. I don’t plan on adding any more providers at the moment, but if you are using with that hasn’t be covered leave a comment and I will look at adding another example.

The code in its final state can be found here.

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.