LinqConnect Professional Version 4.4.374 (14-Nov-2013) - MS SQL Server problem with SqlMethods.Between
Posted: Wed 04 Dec 2013 15:52
Using SqlMethods.Between() method in LINQ generates invalid query for SQL Server.
If a LINQ expression is constructed in the following way:
it is evaluated by the underlying engine as the following SQL query: SELECT [t1].[Id] ...
which, in turn, gives the error "Incorrect syntax near '<'". The following query is correct, however:
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.
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();Code: Select all
FROM [MyTable] [t1]
WHERE (([t1].[Id]) BETWEEN (10) AND (20)) <> 0;Code: Select all
SELECT [t1].[Id]
FROM [MyTable] [t1]
WHERE ([t1].[Id]) BETWEEN (10) AND (20);