NET Core 3.1 Required String Properties - SqlException: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

[robinwilson16]

Upgrading projects from .NET Core 3.0 to 3.1 generates invalid SQL in some situations and I have tracked the issue down to being the case that .NET Core 3.1 generates an invalid SQL case statement for a required attribute of a data model which generates correctly on .NET Core 3.0.

Here is an example of some C# code where the code for the BodyTypes entity is ok after upgrade as does not have the required attribute but the code for the Makes entity generates invalid an invalid case statement.

Properties from the object model:

[StringLength(20)]
public string BodyType { get; set; }

[Required]
[StringLength(50)]
public string Make { get; set; }

C# Code in code behind:

BodyTypes = await _context.StocklistImport
   .Select(
      s => new SelectListItem
      {
         Value = s.BodyType,
         Text = s.BodyType,
         Selected = s.BodyType == body_type
      }
   )
   .Distinct()
   .ToListAsync();

Makes = await _context.StocklistImport
   .Select(
      s => new SelectListItem
      {
         Value = s.Make,
         Text = s.Make,
         Selected = s.Make == make
      }
   )
   .Distinct()
   .ToListAsync();

Using SQL Profiler, before this code generated the following:
BodyTypes:

exec sp_executesql N'SELECT DISTINCT [s].[BodyType] AS [Value], CASE
    WHEN (([s].[BodyType] = @__body_type_0) AND ([s].[BodyType] IS NOT NULL AND @__body_type_0 IS NOT NULL)) OR ([s].[BodyType] IS NULL AND @__body_type_0 IS NULL) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Selected]
FROM [StocklistImport] AS [s]',N'@__body_type_0 nvarchar(20)',@__body_type_0=NULL

Makes:

exec sp_executesql N'SELECT DISTINCT [s].[Make] AS [Value], CASE
    WHEN ([s].[Make] = @__make_0) AND @__make_0 IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Selected]
FROM [StocklistImport] AS [s]',N'@__make_0 nvarchar(50)',@__make_0=NULL

After migration to NET Core 3.1 sp_executesql is no longer used for either but now the SQL generated for Makes is invalid:

BodyTypes (is still valid):

SELECT DISTINCT [s].[BodyType] AS [Value], CASE
    WHEN [s].[BodyType] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Selected]
FROM [StocklistImport] AS [s]

Makes:

SELECT DISTINCT [s].[Make] AS [Value], CASE
    WHEN CAST(0 AS bit) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [Selected]
FROM [StocklistImport] AS [s]

If I switch the project back to 3.0 then it is fine again but as soon as I change it to 3.1 it stops working correctly as the SQL being generated is invalid.

The exception generated is:

SqlException: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

This is the same error you get if you run the SQL in SSMS.

Workarounds are either to:

• Comment out the selected property from the code behind (but then drop-down loses pre-selection of correct item)
• Comment out the [Required] attribute from the object model (but allows value to be left empty)
• Switch back to .NET 3.0 (but won't be supported in future if issue not fixed)

[ajcvickers]

Moved to dotnet/efcore#19444