ASP.NET Core 3: React Template with Auth

Preview 3 of ASP.NET Core was released on March 6th. This release added the option to include auth when creating an Angular or React application using the templates provided by Microsoft. I can’t convey how happy this feature makes me. As someone that hasn’t done a super deep dive on auth having a good starting point for a new application is very helpful.

Installation

To get the updated version of the templates install the latest version of the .NET Core 3 previews. You can find the installers here.

Project Creation

Using the .NET CLI from a command prompt in the directory you want the project created in run the following command.

dotnet new react --auth Individual

After the project is built you should be able to use the following command to run the application.

dotnet run

Issues with Preview 3

I did the above and it results in the following error.

Microsoft.AspNetCore.SpaServices: Information:

Failed to compile.

info: Microsoft.AspNetCore.SpaServices[0]
./src/components/api-authorization/ApiAuthorizationConstants.js
It seems that there are a few issues with the React template that shipped with Preview 3.  To fix the above error open the ApiAuthorizationConstants.js file found in the ClientApp/src/components/api-authorization directory and make the following change.
Before:
ApiAuthorizationPrefix = prefix,

After:
ApiAuthorizationPrefix: prefix,

After that fix, you will see the following error.

./src/components/api-authorization/ApiAuthorizationRoutes.js
Module not found: Can’t resolve ‘./components/api-authorization/Login’ in ‘\ClientApp\src\components\api-authorization’

This fix is a bit more involved. I found the workaround in the known issues page provided by Microsoft.

First, delete the ApiAuthorizationRoutes.js file which is in the same directory as the previous fix. Then replace the contents of App.js found in the ClientApp/src directory with the following.

import React, { Component } from 'react';
import { Route } from 'react-router';
import { Layout } from './components/Layout';
import { Home } from './components/Home';
import { FetchData } from './components/FetchData';
import { Counter } from './components/Counter';
import { Login } from './components/api-authorization/Login'
import { Logout } from './components/api-authorization/Logout'
import AuthorizeRoute from './components/api-authorization/AuthorizeRoute';
import { ApplicationPaths, LoginActions, LogoutActions } from './components/api-authorization/ApiAuthorizationConstants';

export default class App extends Component {
  static displayName = App.name;

  render () {
    return (
      <Layout>
        <Route exact path='/' component={Home} />
        <Route path='/counter' component={Counter} />
        <AuthorizeRoute path='/fetch-data' component={FetchData} />
        <Route path={ApplicationPaths.Login} render={() => loginAction(LoginActions.Login)} />
        <Route path={ApplicationPaths.LoginFailed} render={() => loginAction(LoginActions.LoginFailed)} />
        <Route path={ApplicationPaths.LoginCallback} render={() => loginAction(LoginActions.LoginCallback)} />
        <Route path={ApplicationPaths.Profile} render={() => loginAction(LoginActions.Profile)} />
        <Route path={ApplicationPaths.Register} render={() => loginAction(LoginActions.Register)} />
        <Route path={ApplicationPaths.LogOut} render={() => logoutAction(LogoutActions.Logout)} />
        <Route path={ApplicationPaths.LogOutCallback} render={() => logoutAction(LogoutActions.LogoutCallback)} />
        <Route path={ApplicationPaths.LoggedOut} render={() => logoutAction(LogoutActions.LoggedOut)} />
      </Layout>
    );
  }
}

function loginAction(name){
    return (<Login action={name}></Login>);
}

function logoutAction(name) {
    return (<Logout action={name}></Logout>);
}

With the above fixes, the site will load and you should see something like the following.

When you try to register you will get the following error.

MissingMethodException: Method not found: ‘Microsoft.EntityFrameworkCore.Metadata.Builders.IndexBuilder Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder`1.HasIndex(System.Linq.Expressions.Expression`1<System.Func`2<!0,System.Object>>)’.

IdentityServer4.EntityFramework.Extensions.ModelBuilderExtensions+<>c__DisplayClass2_0.<ConfigurePersistedGrantContext>b__0(EntityTypeBuilder<PersistedGrant> grant)

Again the known issue page to the rescue. Open your csproj file and replace the following package references.

Before:
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.0.0-preview3-19153-02" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.0.0-preview3.19153.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0-preview3.19153.1" />

After:
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.0.0-preview-18579-0056" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.0.0-preview.19080.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0-preview.19080.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.0.0-preview.19080.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.0.0-preview.19080.1" />

And add the following bit of XML.

<PropertyGroup>
  <NoWarn>$(NoWarn);NU1605</NoWarn>
</PropertyGroup>

Wrapping Up

After the above tweaks, everything just worked. I’m sure by preview 4 the experience will be even better. Even with the issues I hit getting a basic new project going I am very excited. Thank you, ASP.NET team, at Microsoft adding auth to these templates is going to be super helpful. Also, note that Angular template also got an auth option (and it seems to be a smoother experience at the moment).

.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.

Four Years of Blogging

Monday will be the fourth anniversary of my first post on this blog. If you have been around for a while this post is going to be very similar to the one from last year.

Positives

Most of my positive points are the same as last year, but here they are again in case you are new this year.

  • Driver for learning new things
  • Opportunity to use new/different technology outside of my normal work
  • Helping others learn new concepts
  • Helping others overcome problems
  • Comments from readers letting me know posts were helpful
  • No big hosting issues this year (thank you NodeHost!)

Challenges

Suprise some of the challenges are the same too. The big new one this year is negative responses/comments to some of my posts which has actually been much worse for me to come to terms with than the rest of the challenges I have faced.

  • Negative responses/comments (See this post an example)
  • Learning new things on a deadline
  • Self-applied pressure to meet my goal of a post a week (increasing my stress level)
  • Not focusing on stats, shares, comment, etc.
  • Picking the right things to learn
  • Time requirements taking away from other projects I would like to do

Top posts of the year

I had two repeats this year and the rest are new to the top 5. It is a bit surprising that a post with Angular 2 in the title is still making the top 5.

Resources

ASP.NET Weekly is a weekly digest of all the best ASP.NET related news and blog post run by Jerrie Pelser.

ASP.NET Community Standup is straight from the team at Microsoft who is responsible for ASP.NET Core. These videos are a great way to get the scoop on what is in the works.

NodeHost is my hosting provider. They provide super simple and cheap hosting. If you are looking for a place to host your own blog check them out. Combine them with Cloudflare and Let’s Encrypt and you are all set if you are going to WordPress route.

See my Books, Podcasts and Other Resources post for a more complete list.

The next year

This coming year is going to be a good one with the continued previews and eventual release of .NET Core 3 and related ASP.NET Core 3, Entity Framework Core 3, and support for Winforms/WPF.

I am also open to topic suggestions. Use the comments on this post or drop me an email and I will see what I can do.

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.