The dangerous EF Core feature: automatic client evaluation

Recently when going through our shiny new ASP.NET Core application’s Seq logs, I accidentally spotted a few entries like this:

The LINQ expression ‘foo’ could not be translated and will be evaluated locally.

Odd.

I dug around in the code and found the responsible queries. Some of them were quite complex with many joins and groupings, while some of the other ones were very simple stuff, like someStringField.Contains("bar", StringComparison.OrdinalIgnoreCase).

You may have spotted the problem right away. StringComparison.OrdinalIgnoreCase is a .NET concept. It doesn’t translate to SQL and you can’t blame EF Core for that. As a matter of fact if you run the same query in full-blown Entity Framework, you’ll get a NotSupportedException telling you it can’t convert your perdicate to a SQL expression. And that’s a good thing! because it prompts you to review your query, and if you really want to have a predicate in your query that only makes sense in the CLR world, you can decide if doing a ToList() at some point in your IQueryable to pull down the results of your query from the database into memory makes sense. And when you have your results in memory you can continue with your query shenanigans without having to worry if the rest of your query is translatable to SQL. Or you may decide that you don’t need that StringComparison.OrdinalIgnoreCase, because your database collation is case-insensitive anyway.

The point is, by default, you are in control and can make explicit decisions based on your circumstance.

Not anymore in Entity Framework Core. Apparently there’s this concept of mixed client/server evaluation in EF Core. What it effectively does is if you put stuff in an IQueryable LINQ query that can’t be translated to SQL or a query in your underlying database, it tries to magically make it work for you, by taking the untranslatable bits out and running them locally! and it’s enabled by default!

That’s a huge and extremely dangerous behavior change compared to the full Entity Framework. Consider this familiar entity:

public class Person
{
	public string FirstName { get; private set; }
	public string LastName { get; private set; }
    public List<Address> Addresses { get; private set; }
    public List<Order> Orders { get; private set; }
	
	/* 
	
	more properties and child collections
	
	*/
}

And imagine someone writing an EF query like this:

	var results = dbContext.Persons
		.Include(p => p.Addresses)
		.Include(p => p.Orders)
		.Where(p => p.LastName.Equals("Amini", StringComparison.OrdinalIgnoreCase))
		.ToList();

EF Core can’t translate p.LastName.Equals("Amini", StringComparison.OrdinalIgnoreCase) into a query that can be run on the database, so what it does is, it pulls down the whole Persons table, as well as the whole Orders and Addresses tables from the database into the memory and then runs the .Where(p => p.LastName.Equals("Amini", StringComparison.OrdinalIgnoreCase)) filter on the results. 🤦

It’s not hard to imagine the performance repercussions of that on any real-sized application with a significant number of users. It can easily bring down applications to their knees. Compare that to the full Entity Framework where you get an exception. The fact that this hugely different behavior is the default is mind blowing!

You could argue that it’s the developer’s fault for including something like StringComparison.OrdinalIgnoreCase in the IQueryable prediate. While that’s true, you can’t blame people newer to EF for expecting something like that to magically work. Especially since it does! for more senior devs it can easily sneak past unnoticed when context switching between LINQ-to-entities and LINQ-to-objects.

Also, having untranslatable things like StringComparison.OrdinalIgnoreCase in your query isn’t the only culprit that results in client evaluation. If you have too many joins or groupings, the query can become too complex for EF Core and make it fall back to local evaluation.

So you probably want to keep an eye on your logs if your EF Core queries are magically working without a hitch as you may be getting client evaluation. Or better yet, if you don’t want that additional cognitive overhead, disable it altogether and make it throw like the good old Entity Framework:

/* Startup.cs */

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	optionsBuilder
		.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
		.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

I believe languages and frameworks should always make it harder to make mistakes, especially ones like this with potentially devastating consequences, so I strongly disagree with this new default behavior and will be keeping it disabled.

Visual Studio Console.ReadLine & Console.ReadKey Snippets

Visual Studio Code Snippets are awesome productivity enhancers; I can only imagine how many millions of keystrokes I’ve saved over the years by making a habit out of using them.

However, not all common pieces of code are available as snippets out of the box. Good examples are Console.ReadLine & Console.ReadKey. Fortunately, creating them yourself is very easy, just save the following as .snippet files and then import them via Tools > Code Snippet Manager... > Import...:

Console.ReadLine:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>cr</Title>
      <Shortcut>cr</Shortcut>
      <Description>Code snippet for Console.ReadLine</Description>
      <Author>Community</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal Editable="false">
          <ID>SystemConsole</ID>
          <Function>SimpleTypeName(global::System.Console)</Function>
        </Literal>
      </Declarations>
      <Code Language="csharp">
        <![CDATA[$SystemConsole$.ReadLine();$end$]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Console.ReadKey:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>ck</Title>
      <Shortcut>ck</Shortcut>
      <Description>Code snippet for Console.ReadKey</Description>
      <Author>Community</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal Editable="false">
          <ID>SystemConsole</ID>
          <Function>SimpleTypeName(global::System.Console)</Function>
        </Literal>
      </Declarations>
      <Code Language="csharp">
        <![CDATA[$SystemConsole$.ReadKey();$end$]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Now you can use them by typing cr or ck for Console.ReadLine or Console.ReadKey respectively and hitting TAB twice.

Allowing only one instance of a C# application to run

Making a singleton application, i.e. preventing users from opening multiple instances of your app, is a common requirement which can be easily implemented using a Mutex.

A Mutex is similar to a C# lock, except it can work across multiple processes, i.e. it is a computer-wide lock. Its name comes from the fact that it is useful in coordinating mutually exclusive access to a shared resource.

Let’s take a simple Console application as an example:

    class Program
    {
        static void Main()
        {
            // main application entry point
            Console.WriteLine("Hello World!");
            Console.ReadKey();
        }
    }

Using a Mutex, we can change the above code to allow only a single instance to print Hello World! and the subsequent instances to exit immediately:

static void Main()
{
    // Named Mutexes are available computer-wide. Use a unique name.
    using (var mutex = new Mutex(false, "saebamini.com SingletonApp"))
    {
        // TimeSpan.Zero to test the mutex's signal state and
        // return immediately without blocking
        bool isAnotherInstanceOpen = !mutex.WaitOne(TimeSpan.Zero);
        if (isAnotherInstanceOpen)
        {
            Console.WriteLine("Only one instance of this app is allowed.");
            return;
        }

        // main application entry point
        Console.WriteLine("Hello World!");
        Console.ReadKey();
        mutex.ReleaseMutex();
    }
}

Note that we’ve passed false for the initiallyOwned parameter, because we want to create the mutex in a signaled/ownerless state. The WaitOne call later will try to put the mutex in a non-signaled/owned state.

Once an instance of the application is running, the saebamini.com SingletonApp Mutex will be owned by that instance, causing further WaitOne calls to evaluate to false until the running instance relinquishes ownership of the mutex by calling ReleaseMutex.

Keep in mind that only one thread can own a Mutex object at a time, and just as with the lock statement, it can be released only from the same thread that has obtained it.

Modeling PowerToys for Visual Studio 2013

I rarely use tools that generate code, however, one that has become a fixed asset of my programming toolbox is Visual Studio’s class designer. It’s a great productivity tool that helps you quickly visualize and understand the class structure of projects, classes and class members. It’s also great for presentation of code-base that does not come with a UI, e.g. a Class Library.

It also lets you quickly wire-frame your classes when doing top-down design, but it is limited in that aspect, for example it does not support Auto-Implemented Properties, which I tend to almost always use in my Types, instead it blurts out a verbose Property declaration along with a backing field. Fortunately, almost all of these issues are fixed with the great Modeling PowerToys Visual Studio add-in by Lie which turns Class Designer into an amazing tool.

When I finally upgraded from my beloved Visual Studio 2010 to 2013, in the midst of all the horrors of VS 2013, I also found out that this add-in has not been updated to support later versions and the original author seems to be inactive, so I upgraded it myself and decided to put it here for other fellow developers who also happen to like the tool:

Download Link

To install the add-in, extract the ZIP file contents to %USERPROFILE%\Documents\Visual Studio 2013\Addins and restart Visual Studio.

Please note that I’m not the author of this add-in, I merely upgraded it for VS 2013.

Git: commit with a UTC timestamp and ignore local timezone

When you git commit, Git automatically uses your system’s local timezone by default, so for example if you’re collaborating on a project from Sydney (UTC +10) and do a commit, your commit will look like this in git log for everyone:

commit c00c61e48a5s69db5ee4976h825b521ha5bx9f5d
Author: Your Name <your@email.com>
Date:   Sun Sep 28 11:00:00 2014 +1000 # <-- your local time and timezone offset

Commit message here

If you find it rather unnecessary to include your local timezone in your commits, and would like to commit in UTC time for example, you have two options:

  1. Changing your computer’s timezone before doing a commit.
  2. Using the --date commit option to override the author date used in the commit, like this:

     git commit --date=2014-09-28T01:00:00+0000
    

The first option is very inconvenient, changing system’s timezone back and forth between UTC and local for commits is just silly, so let’s forget about that. The second option however, seems to have potential, but manually inputting the current UTC time for each commit is cumbersome. We’re programmers, there’s gotta be a better way…

Bash commands and aliases to the rescue! we can use the date command to output the UTC time to an ISO 8601 format which is accepted by git commit’s date option:

git commit --date="$(date --utc +%Y-%m-%dT%H:%M:%S%z)"

We then alias it to a convenient git command like utccommit:

git config --global alias.utccommit '!git commit --date="$(date --utc +%Y-%m-%dT%H:%M:%S%z)"'

Now whenever we want to commit with a UTC timestamp, we can just:

git utccommit -m "Hey! I'm committing with a UTC timestamp!"