Query: Errorneous IS NULL check in SQL causing repetitive null checks or invalid SQL with coalesce

[stehlikio]

It looks like the SQL generated from a null-coalesce operator changes depending on a previous statement in the Linq expression. So far I've only noticed this change in behavior when a Where statement contains a !StartsWith somewhere in the Linq expression (doesn't matter if it's before or after).

context.Employees
    .Where(employee => !employee.FirstName.StartsWith("Test"))
    .Where(employee => (employee.DirectorId ?? employee.Id) == 1)
    .ToList();

The problem is that after doing the null-coalesce, it tacks on [employee].[DirectorId] IS NOT NULL, which is unexpected.

Actual SQL

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE (NOT ([employee].[FirstName] LIKE N'Test' + N'%') OR (CHARINDEX(N'Test', [employee].[FirstName]) <> 1)) AND ((COALESCE([employee].[DirectorId], [employee].[Id]) = 1) AND [employee].[DirectorId] IS NOT NULL)

Expected SQL

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE (NOT ([employee].[FirstName] LIKE N'Test' + N'%') OR (CHARINDEX(N'Test', [employee].[FirstName]) <> 1)) AND (COALESCE([employee].[DirectorId], [employee].[Id]) = 1)

Steps to reproduce

I've provided the DDL and the following code that demonstrates a few different examples of StartsWith, !StartsWith, and different coalesce statements.

create table Employee (
    Id int identity(1, 1),
    DirectorId int,
    FirstName varchar(255)
);

internal class Program
{
    internal class Context : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        public Context() { }
        public Context(DbContextOptions options) : base(options) { }
    }

    [Table("Employee")]
    internal class Employee
    {
        [Column("Id")]
        public int Id { get; set; }

        [Column("DirectorId")]
        public int? DirectorId { get; set; }

        [Column("FirstName")]
        public string FirstName { get; set; }
    }

    public static void Main(string[] args)
    {
        var options = new DbContextOptionsBuilder()
            .UseSqlServer(/* ... */)
            .UseLoggerFactory(new LoggerFactory().AddConsole())
            .Options;

        using (var context = new Context(options))
        {
            System.Console.WriteLine();

            context.Employees
                .Where(employee => employee.FirstName.StartsWith("Test"))
                .Where(employee => (employee.DirectorId ?? employee.Id) == 1)
                .ToList();

            System.Console.WriteLine();

            context.Employees
                .Where(employee => employee.FirstName.StartsWith("Test"))
                .Where(employee => (employee.DirectorId.HasValue ? employee.DirectorId : employee.Id) == 1)
                .ToList();

            System.Console.WriteLine();

            context.Employees
                .Where(employee => !employee.FirstName.StartsWith("Test"))
                .Where(employee => (employee.DirectorId ?? employee.Id) == 1)
                .ToList();

            System.Console.WriteLine();

            context.Employees
                .Where(employee => !employee.FirstName.StartsWith("Test"))
                .Where(employee => (employee.DirectorId.HasValue ? employee.DirectorId : employee.Id) == 1)
                .ToList();

            System.Console.WriteLine();
        }
    }
}

FirstName does start with "Test" and null-coalesce operator to coalesce DirectorId/Id

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE ([employee].[FirstName] LIKE N'Test' + N'%' AND (CHARINDEX(N'Test', [employee].[FirstName]) = 1)) AND (COALESCE([employee].[DirectorId], [employee].[Id]) = 1)

FirstName does start with "Test" and null-coalesce operator to coalesce DirectorId/Id

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE ([employee].[FirstName] LIKE N'Test' + N'%' AND (CHARINDEX(N'Test', [employee].[FirstName]) = 1)) AND ((CASE
    WHEN [employee].[DirectorId] IS NOT NULL
    THEN [employee].[DirectorId] ELSE [employee].[Id]
END = 1) AND CASE
    WHEN [employee].[DirectorId] IS NOT NULL
    THEN [employee].[DirectorId] ELSE [employee].[Id]
END IS NOT NULL)

FirstName does not start with "Test" and null-coalesce operator to coalesce DirectorId/Id

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE (NOT ([employee].[FirstName] LIKE N'Test' + N'%') OR (CHARINDEX(N'Test', [employee].[FirstName]) <> 1)) AND ((COALESCE([employee].[DirectorId], [employee].[Id]) = 1) AND [employee].[DirectorId] IS NOT NULL)

FirstName does not start with "Test" and ternary operator to coalesce DirectorId/Id

SELECT [employee].[Id], [employee].[DirectorId], [employee].[FirstName]
FROM [Employee] AS [employee]
WHERE (NOT ([employee].[FirstName] LIKE N'Test' + N'%') OR (CHARINDEX(N'Test', [employee].[FirstName]) <> 1)) AND ((CASE
    WHEN [employee].[DirectorId] IS NOT NULL
    THEN [employee].[DirectorId] ELSE [employee].[Id]
END = 1) AND CASE
    WHEN [employee].[DirectorId] IS NOT NULL
    THEN [employee].[DirectorId] ELSE [employee].[Id]
END IS NOT NULL)

Further technical details

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Microsoft.EntityFrameworkCore": "1.1.0",
    "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
    "Microsoft.Extensions.Logging": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0"
  },
  "frameworks": {
    "netcoreapp1.1": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "version": "1.1.0"
        }
      },
      "imports": "dnxcore50"
    }
  },
  "runtimes": {
      "win7-x86" : { },
      "win7-x64" : { },
      "win81-x64" : { }
  }
}

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2017, Project Rider EAP 1.0

[maumar]

Fixed in 42e8bbe