Query: Select with Ternary Operator/CASE-WHEN Server Side Evaluation

[nooxnet]

I wanted to select a few columns of a table/class into a smaller object. I used the ternary operator to get the value for one field. With 1.0.1 this produces a wrong SQL query.

var result = dbContext.Foos
    .OrderBy(f => f.Name)
    .ThenBy(f => f.Id)
    .Select(f => new CodeNameClass()
    {
        Id = f.Id,
        Name = f.Name,
        Code = !string.IsNullOrEmpty(f.Code) ? f.Code : f.Id.ToString()
    })
    .AsNoTracking()
    .ToList();

The generated SQL is:

SELECT [f].[Id], [f].[Name], CASE
    WHEN CASE
        WHEN [f].[Code] IS NULL OR (([f].[Code] = N'') AND [f].[Code] IS NOT NULL)
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END, [f].[Code]
FROM [Foos] AS [f]
ORDER BY [f].[Name], [f].[Id]

The code worked with RC2 and 1.0. As I saw from my logs the ternary operator had been evaluated on the client side till 1.0.

Steps to reproduce

public class Foo
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    // some further fields
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"server=(localdb)\MsSqlLocalDB;database=SampleDB");
    }
}

public class CodeNameClass
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

public class TestEfCoreServerEvaluation
{
    public TestEfCoreServerEvaluation()
    {
        using (var dbContext = new ApplicationDbContext())
        {
            Console.WriteLine("EnsureDeleted ...");
            dbContext.Database.EnsureDeleted();
            Console.WriteLine("Done!");
            Console.WriteLine("EnsureCreated ...");
            dbContext.Database.EnsureCreated();
            Console.WriteLine("Done!");

            var result = dbContext.Foos
                .OrderBy(f => f.Name)
                .ThenBy(f => f.Id)
                .Select(f => new CodeNameClass()
                {
                    Id = f.Id,
                    Name = f.Name,
                    Code = !string.IsNullOrEmpty(f.Code) ? f.Code : f.Id.ToString()
                })
                .AsNoTracking()
                .ToList();

            //var result = dbContext.Foos
            //  .OrderBy(f => f.Name)
            //  .ThenBy(f => f.Id)
            //  .Select(f => new CodeNameClass()
            //  {
            //      Id = f.Id,
            //      Name = f.Name,
            //      Code = f.Code == null ? f.Code : f.Id.ToString()
            //  })
            //  .AsNoTracking()
            //  .ToList();

            Console.WriteLine("");
        }
    }
}

The second query does not use IsNullOrEmpty. The generated SQL is:

SELECT [f].[Id], [f].[Name], [f].[Code] IS NULL, [f].[Code]
FROM [Foos] AS [f]
ORDER BY [f].[Name], [f].[Id]

Exception message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.dll

Additional information: Incorrect syntax near the keyword 'IS'.

Further technical details

EF Core version: 1.0.1
Operating system: Windows 10
Visual Studio version: VS 2015

[divega]

@smitpatel this seems to be a regression in 1.0.1. Please investigate.

[smitpatel]

We have ProjectionComparisonTransformingVisitor class in Sql-Server specific code.
https://github.com/aspnet/EntityFramework/blob/dev/src/Microsoft.EntityFrameworkCore.SqlServer/Query/Sql/Internal/SqlServerQuerySqlGenerator.cs#L153
Which somewhat does the same thing as SearchConditionTranslatingVisitor. In combination, both tried to make a case block hence we got into state of incorrect nested case blocks.
Sql-Server specific code should be removed and relational one should be used. The most of the changes needed in SearchConditionTranslatingVisitor are already done in 1.1.0 codebase.

[divega]

Reopening to go through proper approval process.

[Eilon]

This patch is approved, please ensure it is merged into the correct branch and building as part of the patch train.