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

Question

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