Using custom methods in linq to entities

Question

I have a Person table in my database that has NationalId field. Is there any way to load all Persons with even NationalId, using Ef code first and Linq to entities, without loading all Persons to memory?

Somethings like:

public  bool IsEven(int number)
{
   return number % 2 == 0;
}

var context = new MyContext();
var personsWithEvenNationalId = context.Persons
                                       .Where(x=> IsEven(x.NationalId))
                                       .ToList();

Answer 1

You would have to do your check inline

var personsWithEvenNationalId = context.Persons
                                       .Where(x=> x.NationalId%2 == 0)
                                       .ToList();

Linq to Entities doesn't know how to translate your custom method into SQL basically. If you do need to use a custom method you would have to get Persons as enumerable and then use your custom method, i.e.

var personsWithEvenNationalId = context.Persons
                                       .AsEnumerable()
                                       .Where(x=> IsEven(x.NationalId))
                                       .ToList();

But that's hardly ideal as it would load all Persons and then filter on IsEven

Edit: Thinking about it you could also create an extension method for IQueryable<Person> if you don't want to have to write it inline every time. Something like this where you build an Expression

    public static IQueryable<Person> WhereEven(this IQueryable<Person> source, Expression<Func<Person, int>> property)
    {
        var expression = Expression.Equal(
            Expression.Modulo(
                property.Body,
                Expression.Constant(2)),
            Expression.Constant(0));

        var methodCallExpression = Expression.Call(typeof (Queryable),
            "where",
            new Type[] {source.ElementType},
            source.Expression,
            Expression.Lambda<Func<Person, bool>>(expression, property.Parameters));

        return source.Provider.CreateQuery<Person>(methodCallExpression);
    }

And to use it:

context.Persons.WhereEven(x => x.NationalId).ToList();

Answer 2

Rather than a function that does what you want, you'll need to have an function (or property, or field) that provides an Expression that does the projection that you want:

public static Expression<Func<int, bool>> IsEven()
{
    return number => number % 2 == 0;
}

You can now write:

using(var context = new MyContext())
{
    var personsWithEvenNationalId = context.Persons
        .Select(x=> x.NationalId)
        .Where(IsEven())
        .ToList();
}