Entity Framework execute query with not mapped properties. Expression tree

c# entity-framework expression-trees linq

Question

I want to execute linq method on iqueryable with an expression tree from function where I'm passing name of linq method and name of property. But my sample method works only with mapped properties. It throws an exception when I try to for example to find max of calculated property.

My classes:

    public partial class Something
    {
        public int a { get; set; }
        public int b { get; set; }
    }

    public partial class Something
    {
        public int calculated { get { return a * b; } }
    }

Sample method:

public static object ExecuteLinqMethod(IQueryable<T> q, string Field, string Method)
    {
        var param = Expression.Parameter(typeof(T), "p");

        Expression prop = Expression.Property(param, Field);

        var exp = Expression.Lambda(prop, param);

            Type[] types = new Type[] { q.ElementType, exp.Body.Type };
            var mce = Expression.Call(typeof(Queryable),Method,types,q.Expression,exp);

            return q.Provider.Execute(mce);
    }
1
1
6/1/2016 12:25:15 PM

Accepted Answer

To be able to query on calculated properties, you have at least 2 options:

1) you store the calculated values in the db with the rows (or in a different table), and use them in your queries of course this requires datamodel change, and redundancy in data, but is the most performant way. But is not that exciting, so lets move on to

2) you need to be able to express the way you "calculate" the properties in a way that sql will understand, meaning the property needs to be replaced with a linq expression in the final query. I found in 2009 an amazing article from Eric Lippert on registering inline such properties, but I cannot find it anymore. As such here is a link to another, that has the same idea. Basically you define your calculation as an expression tree, and use the compiled version in your code.

To make it more convenient, you would attribute your property with a

[AttributeUsage(AttributeTargets.Property)]
class CalculatedByAttribute: Attribute
{
    public string StaticMethodName {get; private set;}
    public CalculatedByAttribute(string staticMethodName)
    {
        StaticMethodName = staticMethodName;
    }
}

Like:

public partial class Something
{
    [CalculatedBy("calculatedExpression")]
    public int calculated { get { return calculatedExpression.Compile()(this); } }
    public static Expression<Func<Something, int>> calculatedExpression = s => s.a * s.b;
}

(of course you can cache the compilation) :)

Then in your method, if the property has your attribute, you get the static property value, and use that in your queries. Something along:

public static object ExecuteLinqMethod<T>(IQueryable<T> q, string Field, string Method)
{
    var propInfo = typeof(T).GetProperty(Field);
    LambdaExpression exp;
    var myAttr = propInfo.GetCustomAttributes(typeof(CalculatedByAttribute), true).OfType<CalculatedByAttribute>().FirstOrDefault();
    if (myAttr != null)
        exp = (LambdaExpression)typeof(T).GetField(myAttr.StaticMethodName, BindingFlags.Static | BindingFlags.Public).GetValue(null);
    else
    {
        var param = Expression.Parameter(typeof(T), "p");
        Expression prop = Expression.Property(param, Field);
        exp = Expression.Lambda(prop, param);
    }

    Type[] types = new Type[] { q.ElementType, exp.Body.Type };
    var mce = Expression.Call(typeof(Queryable),Method,types,q.Expression,exp);

    return q.Provider.Execute(mce);
}
1
6/2/2016 8:54:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow