Entity Framework execute query with not mapped properties. Expression tree

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);
    }

Why do you think the expression tree can allow you to do something that is not supported by the EF query provider? — Ivan Stoev, Jun 01 '16 at 12:35
I need universal method to find max,min,sum etc. of mapped and not mapped properties. With given name of function and name of property as parameters of this method. It can be done by expression tree or other way. — Adam Bubula, Jun 01 '16 at 12:43
Then why don't you find the "other way" first, and then ask for expression tree help if needed. I guess the main issue is how to determine if the field is mapped or not. And even if you do that, since there is no way to know what the calculated property is using, the only way to execute will be to execute it against `IEnumerable`. — Ivan Stoev, Jun 01 '16 at 12:51
I tried other ways but without success. I have large database and getting all records to an IEnumerable is causing out of memory exception. — Adam Bubula, Jun 01 '16 at 13:06
Hmm, then IMO there is no other solution than passing `Expression` containing the calculation based on mapped fields only, instead of a field name. — Ivan Stoev, Jun 01 '16 at 13:22
Here's en interesting compilation of various approaches: http://daveaglick.com/posts/computed-properties-and-entity-framework — Gert Arnold, Jun 02 '16 at 21:10

score 3 · Accepted Answer · answered Jun 02 '16 at 20:54

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);
}

Thanks! It works for calculation of mapped properties, but I don't know what to do with calculation based on mapped and calculated fields. When I try to use nested Expression i'm getting error 'The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.'. For example I tried something like this: [CalculatedBy("calculatedExpression1")] public int calculated1 { get { return calculatedExpression1.Compile()(this); } } public static Expression> calculatedExpression1 = s => s.a * calculatedExpression.Compile()(s); — Adam Bubula, Jun 03 '16 at 09:50
You sure that was me? 2009 was a long time ago, but I do not recall writing such an article. My Microsoft colleague Matt Warren wrote some articles about that sort of thing back in the day; maybe you're thinking of one of them? — Eric Lippert, Jun 03 '16 at 14:38
@AdamBubula: if you need to construct more complex scenarios, check out this answer : http://stackoverflow.com/questions/29448432/pass-expression-parameter-as-argument-to-another-expression/29471092#29471092 — MBoros, Jun 04 '16 at 20:26
@EricLippert: sorry, my memory served me wrong, this one was one of the few great articles (I read) not written by you :P Actually after your comment I found it, and today the whole thing is called the Microsoft.Linq.Translations project (nuget package).AdamBubula: Feel free to play around with it, it has a nicer registration syntax :) — MBoros, Jun 04 '16 at 20:42
@MBoros I tried to use methods from question link but I'm still getting error "'Invoke' is not supported". In linked question there is a predicate passed to where function and in my situation I want to use nested expression in calculation. My sample Expressions. public static Expression> CalculatedExpressionWrapped() { return s => s.a + s.b; } public static Expression> NextCalculatedExpression = s => Math.Round(CalculatedExpressionWrapped().AsQuote()(s) / s.d,2); — Adam Bubula, Jun 07 '16 at 06:21
I doubt Sql understands Math.Round(). Try https://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions%28v=vs.110%29.aspx. — MBoros, Jun 08 '16 at 06:49

Entity Framework execute query with not mapped properties. Expression tree

1 Answers1