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.

Welcome to 2019

Last year I did a Welcome to 2018 post due to a technical issue with my blog. This year I wanted to do the same type of post unprompted by technical issues. So to welcome 2019 let us do a little review of 2018 of the things that provided me value.

Newsletters

ASP.NET Weekly
Dev Tips Weekly
Shawn Wildermuth

Podcasts

I use these podcast to make sure I have a good pulse on the industry even and go deeper when a topic really sounds like something I want to dig into. My commute would be wasted time without these podcasts.

Software Related

.NET Rocks!
Complete Developer Podcast
Developer on Fire
Hanselminutes
Software Engineering Daily

Fun

Freakonomics
Radiolab

Other

Gary Vee
RunAsRadio

Books Read in 2018

Like last year I pulled heavily from  John Sonmez’s book reviews playlist as well as some read suggestions from last year. You will notice there are more fiction books this year. I’m playing with my learn something vs entertainment ratio to see if I can help with my stress level. Without further ado here is the list.

Blogging

From Zero to Thousands of Target Blog Subscribers in 60 Days

Personal Development

The 5 Second Rule
Algorithms to Live By
Antifragile
As a Man Thinketh
Be Obsessed Or Be Average
Breaking the Habit of Being Yourself
The Compound Effect
The New Kingmakers
The Power of Now
Presence
Principles
Psycho-Cybernetics
Secrets of the Millionaire Mind
Slipstream Time Hacking
So Good They Can’t Ignore You
Stop Being Lazy
The Subtle Are of Not Giving a F*ck
Unshakeable
Wait, What?
Willpower Doesn’t Work
Your One Word

Business

Crushing It!
The E-Myth Revisited
Zero to One

Fun

The Fellowship of the Ring
Fight Club
The Hobbit
The Sword of Shannara Trilogy
What If?

Health

The Plant Paradox

Software/Career

Clean Architecture
Curious Moon
Modern API Design with ASP.NET Core 2
Writing High-Performance .NET Code

Top Picks

The Compound Effect is my number one pick from this year’s books. It really brings home the fact that the little thing you do can have huge effects on your life. It was also through this that I found Darren Daily which is basically free daily mentoring by Darren Hardy the book’s author. Check it out it is how I start every weekday.

Willpower Doesn’t Work did an awesome job of explaining willpower vs our environments. Our environments are where we need to make changes to truly set ourselves up for success.

Principles by Ray Dailo round out my top 3 for the year. This book is a great look into how Ray runs his life and how he ran his business. I have never seen someone as successful as Ray lay everything our as he did it this book.

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.