String.CompareTo emits unicode literal against varchar column

[kdblocher]

Seems related to #4978, #8370, and #4686, but logging it here because it's a different use case.

EF is correctly emitting a unicode or non-unicode literal when used directly like this:
query.Where(x => x.ID == "test")

SELECT [x].[ID]
FROM [Table] AS [x]
WHERE [x].[ID] = 'test'

But not with String.CompareTo, like this:
query.Where(x => x.ID.CompareTo("test") == 0)

SELECT [x].[ID]
FROM [Table] AS [x]
WHERE [x].[ID] = N'test'

This means any inequality comparisons (i.e. paging) off of a string field when it is varchar will not use the index on the column, because it is not possible to write a string comparison with the > operator.

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9

[smitpatel]

Generated SQL in 3.1

      SELECT [b].[Id], [b].[Value]
      FROM [Blogs] AS [b]
      WHERE CASE
          WHEN [b].[Value] = 'test' THEN 0
          WHEN [b].[Value] > 'test' THEN 1
          WHEN [b].[Value] < 'test' THEN -1
      END = 0

[kdblocher]

I will send this over to the project team where this bug was discovered and see if they can verify this is fixed.

[maumar]

simplification of CASE blocks is tracked by #16092