Build Expression tree for LINQ to Entities Where clause

Question

I want to be able to write the following code for a LINQ to Entities query (EF6)

Repo.ContactRelations.WhereActive()
                .Select(r => new ContactModel
                {
                    Id = r.Contact.Id,
                    FirstName = r.Contact.FirstName,
                    LastName = r.Contact.Surname,
                    WorkEmail = r.Contact.WorkEmail
                })

Without the WhereActive() method, I would have to repeat the following expression in numerous places:

Repo.ContactRelations.Where(c => c.EndDate == null || c.EndDate > DateTime.Today)

I tried to write a simple extension method, but it gave an error "LINQ to Entities does not recognize the method WhereActive"

    public static IEnumerable<T> WhereActive<T>(this IEnumerable<T> source) where T : class, IMayExpire
    {
        return source.Where(c => c.EndDate == null || c.EndDate > DateTime.Today);
    }

After some reading on LINQ to Entities vs LINQ to Object, and Expression trees vs Func<>, I realized I would need to build a full Expression tree to express my intention.

I'm not sure how to do it, may I get some help please?

That code by itself shouldn't cause that error. Are you using it as a subquery of another query or something like that? — svick, Jun 19 '14 at 17:19

Aron · Accepted Answer · 2014-06-19T07:57:27.143

1

public static IQueryable<T> WhereActive<T>(this IQueryable<T> source) where T : class, IMayExpire
{
    return source.Where(c => c.EndDate == null || c.EndDate > DateTime.Today);
}

EDIT. That SHOULD have worked...however if it didn't you have two choices...

var activeContactRelations = Repo.ContactRelations.WhereActive();
var result = activeContactRelations
            .Select(r => new ContactModel
            {
                Id = r.Contact.Id,
                FirstName = r.Contact.FirstName,
                LastName = r.Contact.Surname,
                WorkEmail = r.Contact.WorkEmail
            })

OR

using System.Linq;

public static Expression<Func<T, bool>> IsActive<T>() where T : class, IMayExpire
{
    return c => c.EndDate == null || c.EndDate > DateTime.Today;
}

var isActive = IsActive<ContactRelation>();
var result = Repo.ContactRelations.Where(isActive)
            .Select(r => new ContactModel
            {
                Id = r.Contact.Id,
                FirstName = r.Contact.FirstName,
                LastName = r.Contact.Surname,
                WorkEmail = r.Contact.WorkEmail
            })

edited Jun 19 '14 at 07:57

answered Jun 19 '14 at 06:48

Aron

15,464
3
31
64

Thanks for the reply Aron, but what you suggested didn't work. I'm still getting the same error: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[ContactRelation] WhereActive[ContactRelation](System.Linq.IQueryable`1[ContactRelation])' method, and this method cannot be translated into a store expression. Doesn't it require rewriting the Where() expression and my custom expression into one single expression in order for LINQ to translate? – ozstudent Jun 19 '14 at 07:06
I might not be 100% correct, but among other issues, at least the parameter that's passed INTO WhereActive is different from the parameter declared WITHIN WhereActive. ie. When calling ContactRelations.WhereActive, there will be a parameter (e.g. can be called "i") passed into WhereActive, and it has nothing to do with the parameter "c" declared within... (Based on my limited understanding) – ozstudent Jun 19 '14 at 07:16
Thanks again, but still, your suggestion doesn't work. Now it throws "Internal .NET Framework Data Provider error 1025" PS: I have to change the return of IsActive to Func because Where() takes a Func<>, not an Expression> – ozstudent Jun 19 '14 at 07:34
No you don't. You need to use `System.Linq.Queryable.Where(Expression>)` and NOT `System.Linq.Enumerable.Where(Func)`. You might need to put the expression on another line... – Aron Jun 19 '14 at 07:55
Sorry for the delayed response. You are right Aron, I accidentally had this code inside a Select() block and that's why it was failing. Once I moved the code outside, it worked fine. Thanks for your help. – ozstudent Jun 23 '14 at 04:13

Build Expression tree for LINQ to Entities Where clause

1 Answers1

Linked