We have code like this:

new RelationPredicateBucket(DemurrageClaimActiveVersionFields.ID == demurrageClaims.GetColumnValues(DemurrageClaim_Table.DemurrageClaimSequence, typeof(int))

where GetColumnValues returns an empty int array, in other words new int[] {}

Is it possible to change the generated SQL for this predicate from, for example,

DELETE FROM [dbo].[OSS_DEMURRAGE_CLAIM_ACTIVE_VERSION] WHERE ( ( ))\r\n

which is invalid SQL, to something like

DELETE FROM [dbo].[OSS_DEMURRAGE_CLAIM_ACTIVE_VERSION] WHERE (1=0)\r\n

otherwise I have to guard all my FieldCompareRangePredicate expressions to check for empty lists.

Thanks,

Scott

It's a bit problamatic, what if someone else want to compare to null if the array is empty.

otherwise I have to guard all my FieldCompareRangePredicate expressions to check for empty lists.

Either this or derive from the FieldCompareRangePredicate and override the ToQueryText() method.

I'm not convinced that argument is very strong. Granted you could change ToQueryText to do something like

col1 is null

if the list is empty, but then you would have the odd scenario of adding to the in list, and having results disappear from the result.

create table a(col1 int) insert into a values(null)

pseudo code:

List<int> inlist = new List<int>();

FetchEntityCollection(entityCollection, aFields.col1 == inlist); // returns 1 row

inlist.Add(1);

FetchEntityCollection(entityCollection, aFields.col1 == inlist); // returns no row

DB Nulls and in lists are funny things, the null value rows can never be returned in ANSI SQL, either with "col1 in (null)", or "col1 not in (1)". So I don't think there is much argument to have LLBLGEN return them either.

+1 for this

"... WHERE IN ()" should never be generated.

Cheers Simon

I agree that the SQL shouldn't contain errors, however the developer as well should take responsibility of what's passed to the API: if the API decides to throw an exception, the developer as well has to anticipate on that, so has to make sure the right values are passed to the API.

The main problem here is: what to do if the list is empty. The predicate is expected to produce some SQL snippet, so if the list is empty, it has to produce something, but it can't, as the list is empty, so it can't produce a proper range compare.

Though, a runtime exception won't help much as the exception is likely to pop up in production at 3 AM on a sunday morning. I indeed think '1=0' is a more useful snippet (and if negate is true: '1=1'), as that mimics semantically what's going on.

However, yowl, you've to understand that it's your code calling our code. We can't anticipate on what's 'correct' in the semantical context of your application, you have to check for that. This means that if we decide to throw an exception, you too have to take care of that exception, if you pass null for some parameter to a method and it causes problems, you have to take action, because apparently 'null' doesn't make sense for example. Sure we can throw exceptions all over the place, but that's not going to help you: they pop up at runtime, you have to write your code in such a way that it makes sense what you pass to the methods and ctors you call. Only then you can be sure your software is reliable and has a smaller chance of running into exceptions at runtime. Because the downside of exceptions is that if you don't have a testcase which triggers them, you won't know when or if they pop up.

I'll add behavior for empty lists in fieldcomparerange predicate for v2.6

And to make Simon happy: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=77340&ThreadID=13726