LINQ对实体不识别方法 [英] 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.

我不想仅仅返回IEnumerable，因为我想将过滤器翻译成底层的SQL语句所以我不会查询这么多行等

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));
    }

_queryFilter类是一个接口，如：

The _queryFilter class is an interface such as:

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

客户只是我的数据库中的一个Entity对象，其属性如Id，CustomerNumber etc

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

会话是我的数据库中的另一个Entity对象，IsStatus方法是这样的：

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

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

我使用的条件通常很简单，所以我相信他们应该能够翻译成SQL。我猜这只是因为它们在他们不能做的功能之内。我可以使用别的东西作为返回类型来使这些工作吗？

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?

推荐答案

LINQ to Entities正在尝试转换你的LINQ查询到SQL语句。现在还没有直接在数据库上执行自定义的Func，所以它会抛出这个错误。

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.

你可以简单的更改你的 IsStatus 方法转换为 session.Status == ProcessStatusEnum.Deleted ，但您可能会收到有关第二个功能的错误。

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

修复的最简单的方法是在数据库上执行的部分之间放置一个 .ToList（）以及要在做记忆显然，数据库上的数据越多越好。

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.

对于您的过滤器，您可以查看将FilterOn更改为返回Expression树的方法：

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();
}

然后，例如，如果你想让伦敦的所有客户都传递一个LondonCustomFilter：

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";
    }
}

这篇关于LINQ对实体不识别方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！