Tweaking the SQL Telerik OpenAccess generates from LINQ expression

[Alex]

I use the specification pattern to dynamically generate LINQ expressions, which will be used

1. On entities in memory
2. For SQL code generation by OpenAccess

See this blog post for an example of what I'm trying to achieve.

It all works fine so far, except that I would like to somehow override the SQL that OpenAccess generates for one particular specification (which could turn up anywhere in the generated expression tree). All the rest is fine, which is why I would like to avoid writing my own translator.

Something as simple as running a RegEx replacement over the complete SQL string just before it's sent to the server would be sufficient, but I just can't find a way...

[Thomas]

There is currently no way to 'tweak' the generated SQL manually on a per query base.
The only means to influence the generated SQL is by altering the field->column mapping and the delimitation setting. We do not have a callback that would allow for a change of the generated SQL.

What kind of change do you need?


Kind regards,
Thomas
the Telerik team

[Alex]

Thanks for your reply, Thomas!

We have a requirement for a filter, which checks that a property is in a list of values. The default approach works fine in general:
myList.Contains(theEntity.TheProperty).
This gets properly translated to an IN() condition. Our problem is, that myList can get longer than 1000 elements and Oracle can't handle IN() conditions longer than that. So our idea was to persist the lists and translate the IN() to something like

EXISTS(SELECT 1 FROM TB_LIST_ELEMENTS a WHERE a.ID_LIST = :idList AND a.ID_ELEMENT = ID)

Note that the condition would be used when filtering the entities. :idList would be passed in as a parameter, ID is the entity's ID.

I'm still trying to convince our user rep, that such long lists are a bad idea in the first place, but I'm not too confident, that we'll get around it. So if you have any suggestion, how we can manage this, I'd be more than thankful!

Cheers,
Alex

[Thomas]

Hello Alex,

after looking deeper into this issue we must say it is not easy to be fixed. The absolutely right fix would require quite some changes in order to produce (x.field IN (list1) OR x.field IN (list2) OR x.field IN (list3)), and needs to do the right thing in cases of NOT IN clauses too.

However, considering the fact that passing a large number of parameters is also something that needs to be avoided for some databases (and is probably not efficient neither) we thought of using a ";1;2;3;" representation of the values and split them on the server in order to avoid many parameters, but this has it's limitations as well as the strings get to large for simple NVARCHAR2 parameters.

Now, the way that we can make that work is to use large number of values that are stored on the database already; in the way the TB_LIST_ELEMENTS proposal works. The only question is how can the database be filled up with a large number of values quickly enough, so that the following query is fast? Persisting an object with a list of > 1000 entries requires >1000 rows to be generated. It depends on how often the list changes that this approach is viable.
The following code could then be used; it assumes a persitent type 'ManySimpleValues' in the database having a Guid Id and an ICollection<int> Values property.

Guid listId = ...
var q = context.Entities.Where(x => context.ManySimpleValues.Where(z =>  z.Id == listId).Any(z => z.Values.Contains(x.TheProperty)) );

Another completely different approach would be to define and call a stored procedure that returns persistent objects. In this sp you could perform any action.... manually.

Table valued parameters/functions would be a principally possible approach too, but they are not available on many databases and OpenAccess does not have support for them yet either.

In summary: The root problem is the transport of many values to the server, this is why servers have limitations in the amount of SQL or in the number of parameters or in the number of operands in the IN statement. If you avoid that, joins/exists/sp can be used to efficiently query the stored data.

The client side approach of splitting overlong lists into multiple sublists and issuing multiple queries and then concatenating the results would also be doable,but demands that certain restrictions on the kind of filters / orderings etc be met.

I would have liked to give a 'just do this or that'-answer, but as you can see, this is an issue where database limits are reached, and maybe a different approach is required to allow an efficient processing.


Kind regards,
Thomas
the Telerik team