C# LINQ to Entities does not recognize the method 'Boolean'

Question

I have the following linq expression in lambda syntax:

var myValue = 6;
var from = 2;
var to = 8;

var res = MyList.Where(m => m.person.Id == person.Id
                         && IsBetween(myValue, from, to))
                .Select(x => new Person { blah blah blah })
                .ToList());

IsBetween is simple generic helper method to see whether I have something in between:

public bool IsBetween<T>(T element, T start, T end)
{
    return Comparer<T>.Default.Compare(element, start) >= 0
        && Comparer<T>.Default.Compare(element, end) <= 0;
}

Now I get this error, and I don't know hot to get around it:

LINQ to Entities does not recognize the method 'Boolean IsBetween[Decimal](System.Decimal, System.Decimal, System.Decimal)' method, and this method cannot be translated into a store expression.

Answer 1

You cannot call arbitrary methods from within a LINQ to Entities query, as the query is executed within the SQL database engine. You can only call methods which the framework can translate into equivalent SQL.

If you need to call an arbitrary method, the query operator calling the method call will need to be preceded by an AsEnumerable() operator such that the call happens client-side. Be aware that by doing this, all results to the left-hand side of AsEnumerable() will potentially be loaded into memory and processed.

In cases where the method you are calling is short enough, I would simply inline the logic. In your case, you would also need to drop the Comparer calls, and IsBetween(myValue, from, to) would simply become myValue >= from && myValue <= to.

Answer 2

In Addition to this, if you want to pass the values to IsBetween method from MyList.

Take a wrapper class (here Person) contains the same properties to be passed to the method. and do something like this:

var res = MyList.Where(m => m.person.Id == person.Id)
            .Select(x => new Person { p1 = x.p1, p2 = x.p2 })
            .AsEnumerable()
            .where(x => (IsBetween(x.p1, x.p2)))
            .ToList());