Using SqlMethods.Between() method in LINQ generates invalid query for SQL Server.
If a LINQ expression is constructed in the following way:
Code: Select all
list = ctx.MyTable.Where(row => SqlMethods.Between(row.Id, 10, 20)).ToList();
it is evaluated by the underlying engine as the following SQL query: SELECT [t1].[Id] ...
Code: Select all
FROM [MyTable] [t1]
WHERE (([t1].[Id]) BETWEEN (10) AND (20)) <> 0;
which, in turn, gives the error "Incorrect syntax near '<'". The following query is correct, however:
Code: Select all
SELECT [t1].[Id]
FROM [MyTable] [t1]
WHERE ([t1].[Id]) BETWEEN (10) AND (20);
It seems that the LinqConnect engine considers a BETWEEN expression value to be numeric rather than boolean, so it compares its value with zero, which is invalid for MS SQL Server.