Improve Query Generation for .CompareTo()

[dbrownems]

.NET does not allow comparison operators >,<,<=,>= on strings. SQL Server does. The only way to express range predicates is with string.CompareTo(string). But EF translates them like this

var col = db.Customers.Where(c => c.Name.CompareTo("J") < 0).ToList();

becomes

      SELECT [c].[Id], [c].[Name]
      FROM [Customers] AS [c]
      WHERE CASE
          WHEN [c].[Name] = N'J' THEN 0
          WHEN [c].[Name] > N'J' THEN 1
          WHEN [c].[Name] < N'J' THEN -1
      END < 0

This query must perform a scan of all the customers' Names to apply the predicate. A better translation would be

      SELECT [c].[Id], [c].[Name]
      FROM [Customers] AS [c]
      WHERE [c].[Name] < N'J'

Which can perform a seek to the starting location if an index is present.

Alternatively methods could be added to EF.Functions for Between, LessThan, LessThanOrEqualTo, GreaterThan, and GreaterThanOrEqualTo.

[smitpatel]

@maumar

[maumar]

dupe of #16092 which has been fixed in 5.0 preview 6. @dbrownems in order to take advantage of the improvement you need to do equality comparison to 1, 0, -1, rather than > 0, < 0 etc