LINQ to entities does not recognise the method

问题:

I understand why I'm getting the following error, however I'm not sure how I could go about providing a solution that can get around it.

{System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsStatus(CDAX.DataModel.ProcessStatusEnum)' method, and this method cannot be translated into a store expression.

I don't want to just return IEnumerable as I want to translate the filters to an underlying SQL statement so that I don't query on so many rows etc

    public override IQueryable<Session> GetQuery()
    {
        Func<Session, bool> activeSessions = (session) => !session.IsStatus(ProcessStatusEnum.Deleted);

        // these functions are causing issues.  I'm not sure how to change them to
        // work with IQueryable??   
        return base.GetQuery().Where(p => activeSessions(p) && _queryFilter.FilterOn(p.Customer));
    }

The _queryFilter class is an interface such as:

public interface IDataQueryFilter
{
    bool FilterOn(Customer obj);
}

Customer is just an Entity object in my database with properties such as Id, CustomerNumber etc

Session is another Entity object in my database and the IsStatus method is such:

    public bool IsStatus(ProcessStatusEnum status)
    {
        return SessionStatus == (byte)status;
    }

The conditions I use are typically very simple so I believe they should be able to translate to SQL. I guess it's just because they are within functions that they cannot. Could I perhaps use something else as the return type to get these to work?

回答1:

LINQ to Entities is trying to convert your LINQ query into SQL statements. It doesn't now how to perform your custom Func on the database directly so it throws this error.

You could simply change your IsStatus method into session.Status == ProcessStatusEnum.Deleted but you will likely then get an error about the second function.

The simplest way to 'fix' this is to put a .ToList() between the part to perform on the database, and the part you want to do in memory. Obviously the more that goes on the database the better however.

For your filter, you might look at changing your FilterOn into a method that returns an Expression tree:

public interface IDataQueryFilter
{
    Expression<Func<Customer, bool>> FilterOn();
}

And then for example if you want all customers in London pass in an instance of LondonCustomFilter:

public class LondonCustomFilter : IDataQueryFilter {
    Expression<Func<Customer, Bool>> FilterOn() {
        return (customer) => customer.City == "London";
    }
}

回答2:

Your IsStatus method call cannot be converted into SQL. I suggest you to do status verification manually without method call.

(session) => session.Status != ProcessStatusEnum.Deleted

Another option is bringing all entities into memory (by executing ToList()), but that will be slow.