Entity Framework execute query with not mapped properties. Expression tree

c# entity-framework expression-trees linq

Question

I want to use an expression tree from a function to run a linq method on an iqueryable while giving the name of the method and the name of the property. However, my test method only works with mapped properties. When I attempt to, for instance, determine the maximum value of a computed property, it throws an exception.

my courses

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

Example approach:

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

You have at least two choices for queries on computed properties:

1) You save the computed values in the database with the rows (or in another table) and utilize them in your queries. This is the most efficient method, although it does need a datamodel modification and redundant data. nonetheless, is not that intriguing, so let's move on.

2) The property must be substituted with a linq expression in the final query in order to indicate how you "compute" the properties in a form that SQL can comprehend. I came across a fantastic post by Eric Lippert about registering inline such attributes in 2009, but I can't seem to locate it any more. As a result, this link to another has the identical concept. In essence, you declare your computation as an expression tree and utilize the code that results from compilation.

You might give your item a more handy description by attributing it to 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, the compilation may be cached)

The static property value is then obtained in your method and used in your queries if the property contains your attribute. Along these lines

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