Hi *,

I have a simple LINQ query, that has filter like:

int[] ids;
.Where(x=> ids.Contains(x.Id))

Nothing special. When the "ids" array is empty, the query is translated correctly to general SQL, but Firebird will not eat it. It creates

WHERE ( ( ( ( @p4 = @p2))))
p4 = 0
p2 = 1

And Firebird (server) has no idea what type of p2/p4 is. For Firebird to make it work, there needs to be explicit CAST, liek:

WHERE ( ( ( ( CAST(@p4 as int) = CAST(@p2 as int)))))

Or maybe because these are constants you can put "1" and "0" directly there, that will work fine.

runtime build nr, llblgen pro version?

Additionally, when ids isn't empty the query will work properly on FB?

SD.LLBLGen.Pro.ORMSupportClasses.dll: 4.0.13.417 SD.LLBLGen.Pro.DQE.Firebird.dll: 4.0.13.417

Yes. If the collection is not empty the generated query is correct.

What does the generated query look like if the collection is not empty?

Ah, yes, it's the pitfall we can't avoid, the situation where the predicate is always false and the provider knows that.

With a non-empty collection it will produce either an IN clause or a set of PK field value comparisons if the PK is a compound PK.

With an empty collection, there are no values to compare to, so it will result in an always false comparison. It tries to create such a thing by producing a 0=1 predicate. This apparently goes wrong in the case of Firebird. The thing is that the casts are removed because they're unnecessary (the type of the value is already int, unnecessary casts are removed, as casting an int to int is... redundant)

~~What I don't get is why Firebird doesn't know the type of the parameters, as it does know the type of the parameters, as that's set on the parameters. I know the @p1=@p2 construct is odd, but it's a valid construct.

... where field = @p1

works in firebird as well, why all of a sudden does it not know the parameter type?~~ Answered here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20953 which is a similar problem you brought up more than a year ago. Perhaps we can re-use that trick here again, we'll check.

Hmm. The problem is that the 1=0 predicate is created inside the linq provider, it has no idea what db type it is generating the query for, so it can't add a cast statement there.

The only way to fix this is to add a new predicate type for this, which simply emits 1=0 or 1=1, and which is used only at that spot. We'll look into adding this.

You can work around this by adding an if statement around the query, as the query will never return a row anyway in the case of an empty collection.

D'oh! FieldCompareRangePredicate already has this built-in. QuerySpec already uses this, the linq provider tries to be clever, but doesn't have to. Will re-route the code to use FieldCompareRangePredicate with the empty collection regardless whether the collection is empty or not.

Fixed. See attached dll.

Yep, it's working now.