How can I build LINQ query when object type is not known at compile-time

Question

Hello. I am developing Web Custom control which able to apply filters to the LinqDataSource object. It is generic control since it should operate with object of certain type.

The control knows what field of object it should operate by following field

    /// <summary>
    /// Method to get compared column from object
    /// </summary>
    public Expression<Func<T, int>> GetColumnMethod;

(I transfer to it method which gets appropriate field from object type)

We perform the filtering with code like that

... if (selectedValue == "<=") predicate = predicate.And(c => method(c) <= val); if (selectedValue == "<") predicate = predicate.And(c => method(c) < val);

All proceeds OK until LINQ to SQL transformation occurs. Then error "Method '.....' has no supported translation to SQL.

sure, CLR doesn't know how to make SQL for delegates.

If only C# could compile the expression before translation to SQL, but I have no idea how to make it to do it.

Perversions like Expression.Compile (whatever tricky ways I tried the whole day - I already cannot remember them all...nothing helped)

But ... at the runtime CLR already knows the type of my object, so it could to manage to build SQL expression having compiked delegate values. But how to do it ? God knows.

Help highly appreciates.

Answer 1

The LINQ to SQL provider is responsible for translation your expression tree into a valid T-SQL statement. As there is not a 1-to-1 relationship between C# and T-SQL it is highly probable that the more sophisticated your expression tree, the less likely it is that LINQ to SQL will be able to translate.

When you use a lambda expression you have to decide if you want to compile it or use it as an expression tree. If you want to use the lambda as an expression then you are responsible for ensuring that the expression contains functions and syntax that your provider supports.

Answer 2

It won't work. Basically your LINQ query is valid C# code so it compiles fine but it fails during runtime on anything outside of scope of LINQ to SQL provider.

Read more here:

“Cannot call methods on DateTime”, and other limitations

Answer 3

If it's just a matter of selecting a particular field to use in your query, you should be able to do this using the Expression.Compile "perversion". But that's likely to be a lot of work. You can probably get a head start by compiling what you have now and using Reflector against the output to see what the code generated by the C# compiler looks like.

Can you split the query into two parts -- one that gets translated to SQL and runs on the server and one that uses your GetColumnMethod and runs in memory against the data output by the first part?

Answer 4

There is no SQL for arbitrary delegates. If method(c) can be expressed as a lambda, you can invoke the lambda as a sub-expression using Expression.Invoke, but you'd need to build the expression tree yourself, like so.

Answer 5

Expression.Invoke doesn't help. It requires some parameters and what should be those parameters like ? I don't have any parameters at runtime, the only thing I have is a type of object.

.Complie also doesn't help, filter just doesn't apply.

I tried to use .ToExpandable extension methods (found that way in google), but the DLL I have downloaded is corrupted and crashes at runtime.

MSDN descriptions of .Invoke and .Parameters methods are rather vague so I can hardly use them properly.

Can I manage the issue without using reflection (i.e. field name must not be given as string constant ) and use delegate ?

The only thing I suppose to be helpful at moment is to define the full set of predicates as my control fields and initialize them as control created. Very rough way, looks so scramble, but it will definitely work unlike attempts to use delegates results of which look desperately yet.

Answer 6

I'm not sure what problem you're solving.

My answer to this question may be of interest though.