Example of query that works in SQLite but not SQL Server: An expression of non-boolean type specified...
Opened this issue · 2 comments
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.
Could you please write a unit test for similar case, to ensure it's fail?
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.