Hello,

I have a case where I want full control on the sql query text produced for a predicate. In the past, using the LL API, I could create a new custom inherited predicate and override the output text to render the statement I wanted.

Now that I am using linq, I see that I can utilize something similar, a functionmapping. The function mapping examples shown assume a static list of arguments. Ideally, I would like to pass an array of parameters into the functionmapping, and have control over the rendering of the query text, rather than the default string.format mapping of parameter {0}, {1}, ... in the string to the supplied fixed number of parameters.

Is there a way to create a functionmapping where I can control the implementation of the substitution in creating the query output text? or is there a way in linq where I can create my custom LL predicate and use that in linq to render my custom predicate?

Thanks.

Can1

Mmm, I think you can't with the normal usage of function mappings. Please let us know what predicate or custom sql you are trying to reproduce, maybe we together found a solution.

I have some id's that I need to include in an IN (id1,id2, .... id3000) clause. These id's come from a separate database system, so I can't include them in a select sub-query. In SQL 2005, if you have over 2100 parameters in an parametrized query, you get a TDS stream error.

So, in this one odd-ball case where I need to include 3000 id's in an IN clause, I could get around this issue in this specific case by generating the IN clause via a custom predicate so that it does not have 3000 separate parameters. That works fine for LL API queries, but how do I do this for a linq query?

Can1

So, in this one odd-ball case where I need to include 3000 id's in an IN clause, I could get around this issue in this specific case by generating the IN clause via a custom predicate so that it does not have 3000 separate parameters. That works fine for LL API queries, but how do I do this for a linq query

In general, I wouldn't recommend using an IN() query with 3000 parameters, this is not efficient regrding performance. I would have broken these into batches of 500 parameter each, and execute more than one query and merge the results at client side.

But anyway, I don't know of a way to do this with Linq, so I'd suggest to stick with LLBLGen's predicats in this edge case.

You can do this in a linq query like:

List<int> someIDs = GetTheIDs();

var q = from x in metaData.SomeEntity where someIDs.Contains(x.ID) select x;

you have to make sure 'someIDs' of course isn't larger than 2100 elements.