Use result of entity's method for filtering wi

2019-05-14 20:45发布

问题:

I would like to filter my entites depending on result of a function working with their properties.

ie. I got entity like this:

public class Lorem
{
    public int A {get;set;}
    public int B {get;set;}
    public int C {get;set;}

    public double DoMath(int externalValue)
    {
        // real implementation is longer and more complex

        if(A==B) {
          // some calculations
          return 0.2;
        }
        if(B==C) {
          // some calculations
          return 0.9;
        }
        else return 0;
    }
}

Now I am querying entities and I would like to get only those with DoMath > 0.

// simplified scenario for example purpose
int someValue = 95;
// working with Entity Framework 4.1 Code First
var filtered = db.Lorems.Where(x=>x.DoMath(someValue) > 0);

I am getting this error:
LINQ to Entities does not recognize the method -----, and this method cannot be translated into a store expression.

Is it possible to make it work like this?
My skills in customizing lambda expressions and working with delegates is quite poor, so I would like to get some help.

Edit: Here is that 'DoMath' function real code (without comments, because they aren't in english):

public double VypocitejPrislusnost(int value)
{
    if (A == B)
    {

        if (value <= C)
        {
            return 1;
        }
        if (value > D)
        {
            return 0;
        }
        else
        {
            double b = D - C;
            double tangens = Math.Tan(1 / b);

            double a = tangens * (D - value);

            return Math.Round(a, 2);
        }
    }

    if (C == D)
    {

        if (value >= B)
        {
            return 1;
        }
        if (value <= A)
        {
            return 0;
        }
        else
        {
            double b = B - A;
            double tangens = Math.Tan(1 / b);

            double a = tangens * (value - A);

            return Math.Round(a, 2);
        }
    }

    else
    {
        if (value >= B && value <= C)
        {
            return 1;
        }
        if (value <= A || value >= D)
        {
            return 0;
        }
        if (value > A && value < B)
        {
            double b = B - A;
            double tangens = Math.Tan(1 / b);

            double a = tangens * (value - A);

            return Math.Round(a, 2);
        }
        if (value > C && value < D)
        {
            double b = D - C;
            double tangens = Math.Tan(1 / b);

            double a = tangens * (D - value);

            return Math.Round(a, 2);
        }
        else
        {
            return 0;
        }
    }
}

It basically calculates y coordinate in triangle in different scenarios. I am using this to count how much the given value fits into a fuzzy set.

回答1:

Entity Framework's Where is expecting an Expression Tree, which will get converted to a t-sql statement. Unfortunately there is no translation from your DoMath method to t-sql, so you'd have to pull the results down into memory, and then call Where as you have it. The reason is that once your results are in memory, LINQ methods work on standard delegates, not expression trees, so there are no restrictions on what can be put in there

To do this, just tack an AsEnumerable() in front of the Where—of course that will pull down your whole table into memory, so do this only if it's reasonably small

var filtered = db.Lorems.AsEnumerable().Where(x => x.DoMath(someValue) > 0);

Of course if you can identify some basic circumstances under which your DoMath will not be greater than 0, you can filter those results out up front, using an expression tree. This will trim down the results coming from the database. You can then filter the rest in memory. I have no idea what your DoMath method does (you imply it's more complicated than what's listed in your question), but hypothetically something like this should work:

var filtered = db.Lorems
                 .Where(x => x.A < 10 && x.B != x.C)
                 .AsEnumerable() 
                 .Where(x => x.DoMath(someValue) > 0);