LINQ to Entities不识别自定义IsConsideredActive函数，求解决方案

原运行正常的LINQ to Entities查询代码：

var model = db.Cases.Join(db.Parcels,
            c => c.CaseKey, p => p.CaseKey,
            (c, p) => new { cases = c, parcels = p })
            .Join(db.TaxYears,
            cp => cp.parcels.ParcelKey, t => t.ParcelKey,
            (cp, t) => new { caseparcels = cp, taxyears = t })
            .Where(q =>
                (
                    q.taxyears.YearStatus.ToLower() != "disc" &&
                    q.taxyears.YearStatus.ToLower() != "dnf" &&
                    q.taxyears.YearStatus.ToLower() != "perm" &&
                    q.taxyears.YearStatus.ToLower() != "dismissed" &&
                    q.taxyears.YearStatus.ToLower() != "w/d")
                  &&
                   (q.caseparcels.cases.CaseStatus.ToLower() != "dnf" &&
                    q.caseparcels.cases.CaseStatus.ToLower() != "sub" &&
                    q.caseparcels.cases.CaseStatus.ToLower() != "stop" &&
                    q.caseparcels.cases.CaseStatus.ToLower() != "closed") &&
                    (q.caseparcels.cases.FileNum >= FromFileNum && q.caseparcels.cases.FileNum <= ToFileNum) &&
                    (q.caseparcels.cases.PrimaryContactId >= iContactCodeFrom && q.caseparcels.cases.PrimaryContactId <= iContactCodeTo) 
                );

现有两张状态表，结构及数据如下：

CaseStatus表 #

定义：

CaseStatus
----------
StatusDescription Varchar(50)
Active int
IsConsideredActive int

数据：

TaxYearStatus表 #

定义：

TaxYearStatus
-------------
StatusDescription Varchar(50)
Active int
IsConsideredActive int

数据：

为替换硬编码的状态判断，编写了自定义静态函数：

public static int IsConsideredActive(string whichTable, string passedStatus)
{
    int returnValue = 0;
    var db = new TaxCertEntities();
    if (whichTable== "cases")
    {
        var IsActive = db.CaseStatus.Where(x => x.StatusDescription.ToLower() == passedStatus.ToLower()).FirstOrDefault();
        returnValue = (int)IsActive.IsConsideredOpen;
    } else
    {
        var IsActive = db.TaxYearStatus.Where(x => x.StatusDescription.ToLower() == passedStatus.ToLower()).FirstOrDefault();
        returnValue = (int)IsActive.IsConsideredOpen;
    }
    return returnValue;
}

修改后的查询代码：

var model = db.Cases.Join(db.Parcels,
            c => c.CaseKey, p => p.CaseKey,
            (c, p) => new { cases = c, parcels = p })
            .Join(db.TaxYears,
            cp => cp.parcels.ParcelKey, t => t.ParcelKey,
            (cp, t) => new { caseparcels = cp, taxyears = t })
            .Where(q =>
                    (q.caseparcels.cases.FileNum >= FromFileNum && q.caseparcels.cases.FileNum <= ToFileNum) &&
                    (q.caseparcels.cases.PrimaryContactId >= iContactCodeFrom && q.caseparcels.cases.PrimaryContactId <= iContactCodeTo) &&
                    (ActiveStatus.IsConsideredActive("cases", q.caseparcels.cases.CaseStatus) == 0) &&
                    (ActiveStatus.IsConsideredActive("taxyear", q.taxyears.YearStatus) == 0) 
                );

运行时报错：

LINQ to Entities does not recognize the method 'Int32 IsConsideredActive(System.String, System.String)'

核心原因 #

LINQ to Entities会将查询表达式转换为SQL语句在数据库端执行，但它无法识别自定义的.NET静态方法，无法将其翻译成对应的SQL逻辑，因此抛出该错误。

方案1：通过Join关联状态表实现过滤 #

直接在查询中关联CaseStatus和TaxYearStatus表，利用表字段进行判断，所有逻辑都能被转换为SQL：

var model = db.Cases
    .Join(db.CaseStatus, 
          c => c.CaseStatus.ToLower(), 
          cs => cs.StatusDescription.ToLower(), 
          (c, cs) => new { Case = c, CaseStatus = cs })
    .Where(ccs => ccs.CaseStatus.IsConsideredActive == 0)
    .Join(db.Parcels, 
          ccs => ccs.Case.CaseKey, 
          p => p.CaseKey, 
          (ccs, p) => new { ccs.Case, ccs.CaseStatus, Parcel = p })
    .Join(db.TaxYears, 
          cpp => cpp.Parcel.ParcelKey, 
          ty => ty.ParcelKey, 
          (cpp, ty) => new { cpp.Case, cpp.CaseStatus, cpp.Parcel, TaxYear = ty })
    .Join(db.TaxYearStatus, 
          cpty => cpty.TaxYear.YearStatus.ToLower(), 
          tys => tys.StatusDescription.ToLower(), 
          (cpty, tys) => new { cpty.Case, cpty.Parcel, cpty.TaxYear, TaxYearStatus = tys })
    .Where(q => 
        q.Case.FileNum >= FromFileNum && q.Case.FileNum <= ToFileNum &&
        q.Case.PrimaryContactId >= iContactCodeFrom && q.Case.PrimaryContactId <= iContactCodeTo &&
        q.TaxYearStatus.IsConsideredActive == 0)
    .Select(q => new { cases = q.Case, parcels = q.Parcel, taxyears = q.TaxYear });

方案2：先查询需排除的状态值，再用Contains过滤 #

先从状态表中查出需要排除的状态（IsConsideredActive = 1的），然后在主查询中用Contains判断是否不在排除列表里：

// 先获取需要排除的Case状态
var excludedCaseStatuses = db.CaseStatus
    .Where(cs => cs.IsConsideredActive == 1)
    .Select(cs => cs.StatusDescription.ToLower())
    .ToList();

// 先获取需要排除的TaxYear状态
var excludedTaxYearStatuses = db.TaxYearStatus
    .Where(tys => tys.IsConsideredActive == 1)
    .Select(tys => tys.StatusDescription.ToLower())
    .ToList();

// 主查询
var model = db.Cases.Join(db.Parcels,
            c => c.CaseKey, p => p.CaseKey,
            (c, p) => new { cases = c, parcels = p })
            .Join(db.TaxYears,
            cp => cp.parcels.ParcelKey, t => t.ParcelKey,
            (cp, t) => new { caseparcels = cp, taxyears = t })
            .Where(q =>
                q.caseparcels.cases.FileNum >= FromFileNum && q.caseparcels.cases.FileNum <= ToFileNum &&
                q.caseparcels.cases.PrimaryContactId >= iContactCodeFrom && q.caseparcels.cases.PrimaryContactId <= iContactCodeTo &&
                !excludedCaseStatuses.Contains(q.caseparcels.cases.CaseStatus.ToLower()) &&
                !excludedTaxYearStatuses.Contains(q.taxyears.YearStatus.ToLower())
            );

额外注意点 #

• 自定义函数里的IsConsideredOpen字段属于笔误，表定义中实际是IsConsideredActive，需要修正。
• 自定义函数每次调用都新建TaxCertEntities上下文，会造成不必要的资源消耗，建议复用上下文或直接在主查询中处理逻辑。

内容的提问来源于stack exchange，提问作者Jonathan Small