igor-tkachev/bltoolkit — pull request #226 by MaceWindu (2013-05-02 19:54:05+00:00)
Title: Invalid SQL generated for SQL Server 2008

Generated SQL:
-- Sql2008 MsSql2008
SELECT
CASE
WHEN [leftJoin].[ID] IS NOT NULL OR [leftJoin].[MoneyValue] IS NOT NULL OR [leftJoin].[DateTimeValue] IS NOT NULL OR [leftJoin].[BoolValue] IS NOT NULL OR [leftJoin].[GuidValue] IS NOT NULL OR [leftJoin].[BinaryValue] IS NOT NULL OR [leftJoin].[SmallIntValue] IS NOT NULL
THEN [leftJoin].[BoolValue] <> 1
ELSE [p].[BoolValue] <> 1
END as [c1]
FROM
[LinqDataTypes] [p]
LEFT JOIN [LinqDataTypes] [leftJoin] ON [p].[ID] = [leftJoin].[ID]
Error message:Incorrect syntax near '<'.
Not tested with other providers, except Access. Access generates correct sql using IIF function.
Checked documentation, looks like such syntax should work only on SQL2012 (not really tested): THEN and ELSE accept expression, and expression definition for SQL2008 isn't so rich as for SQL2012:
SQL2008R2:
http://msdn.microsoft.com/en-us/library/ms190718%28v=sql.105%29.aspx
SQL2012:
http://msdn.microsoft.com/en-us/library/ms190286.aspx
btw, SQL2012 introduced IIF, so it could be used here instead of CASE I suppose.