Example of query that works in SQLite but not SQL Server: An expression of non-boolean type specified...

[rjpowers10]

I wrote the following query in my Orchard Core site (omitting some details for brevity)

// 'published' is a boolean method parameter
query.With<ContentItemIndex>(c => (published && c.Published) || (!published && c.Latest))

This generates the following clause. For simplicity I've replaced the parameters with literal values, and in this example the value of published is true so it is replaced by a 1.

(1 and ([ContentItemIndex_a1].[Published] = 1)) or (0 and ([ContentItemIndex_a1].[Latest] = 1)

This clause is valid in SQLite but not in SQL Server. SQL Server will complain that "An expression of non-boolean type specified in a context where a condition is expected, near 'and'."

To make SQL Server happy I think something like the following clause should be generated.

(1 = 1 and ([ContentItemIndex_a1].[Published] = 1)) or (0 = 1 and ([ContentItemIndex_a1].[Latest] = 1)

It was easy enough for me to rewrite my query to avoid the problem as a workaround.

[hishamco]

Could you please write a unit test for similar case, to ensure it's fail?

[rjpowers10]

Could you please write a unit test for similar case, to ensure it's fail?

I can try but I'm not sure how I would write it. I can take a look at some other tests for inspiration.