在 Linq 中使用函数

Question

我有这个查询：

var accounts =
    from account in context.Accounts
    from owner in account.AccountOwners
    join business in context.Businesses
        on account.CreditRegistryId
        equals business.RegistryId
    join astatus in context.AccountStatuses
        on account.AccountStatusId
        equals astatus.AccountStatusId     
    join LS in context.LegalStatuses
        on account.LegalStatusId 
        equals LS.LegalStatusId
    where !excludeTypes.Contains(account.AccountType)               
    select new AccountsReport
    {
        AccountTypeDescription = GetAccountTypeDescription(account.AccountType),  
        AccountNumber = 1, 
        AccountStatus = "aasd", 
        CreditorAddress = "address", 
        CreditorCity = "my city", 
        CreditorName = "creditor name", 
        CreditorState = "my state", 
        LegalStatus = "my status", 
        RegistryId = 121323
    };

给出错误：

LINQ to Entities does not recognize the method 'System.String GetAccountTypeDescription(System.String)' method, and this method cannot be translated into a store expression. 

功能是：

public string GetAccountTypeDescription(string accountType)
{
    var result = context.AccountTypes.Where(x => x.AccountTypeCode == accountType).Select(x => x.Abbreviation).SingleOrDefault();

    if (string.IsNullOrEmpty(result))
    {
        result = accountType;
    }

    return result;
}

如果我不在 LINQ 查询中使用 GetAccountTypeDescription，它会起作用。

请建议解决方案

Answer 1

您的 LINQ 查询不是在本地运行，而是转换为表达式（又转换为 SQL）并在您的数据库服务器上执行。表达式解析器无法将您的函数转换为 SQL，因为该函数在服务器上不存在。您只能在要在数据库上运行的 LINQ 查询中使用 LINQ 和其他一些 .NET 函数。你可以很容易地解决这个问题

var accountDescription = GetAccountTypeDescription("sdfsdf");
var accounts =
            from account in context.Accounts
            from owner in account.AccountOwners
             join business in context.Businesses
             on account.CreditRegistryId
             equals business.RegistryId
             join astatus in context.AccountStatuses
             on account.AccountStatusId
             equals astatus.AccountStatusId     
             join LS in context.LegalStatuses
             on account.LegalStatusId 
             equals LS.LegalStatusId
             where !excludeTypes.Contains(account.AccountType)               
            select new AccountsReport { AccountTypeDescription= accountDescription,  AccountNumber = 1, AccountStatus = "aasd", CreditorAddress = "address", CreditorCity = "my city", CreditorName = "creditor name", CreditorState = "my state", LegalStatus = "my status", RegistryId = 121323 };

Answer 2

编辑 - 添加左连接

var accounts =
    from account in context.Accounts
    from owner in account.AccountOwners
    join business in context.Businesses
        on account.CreditRegistryId
        equals business.RegistryId
    join astatus in context.AccountStatuses
        on account.AccountStatusId
        equals astatus.AccountStatusId     
    join LS in context.LegalStatuses
        on account.LegalStatusId 
        equals LS.LegalStatusId
    from accountType in context.AccountTypes
                               .Where(at => at.AcountTypeCode == account.AccountType)
                               .DefaultIfEmpty()
    where !excludeTypes.Contains(account.AccountType)               
    select new AccountsReport
    {
        AccountTypeDescription = accountType.Abbreviation == null ? account.AccountType : accountType.Abbreviation,  
        AccountNumber = 1, 
        AccountStatus = "aasd", 
        CreditorAddress = "address", 
        CreditorCity = "my city", 
        CreditorName = "creditor name", 
        CreditorState = "my state", 
        LegalStatus = "my status", 
        RegistryId = 121323
    };

Answer 3

我有点不明白这个。为什么不为类/结构 AccountReport 提供一个属性，该属性为您提供 AccountTypeDescription...这样您就封装了逻辑，隐藏了实现细节，并且通常使代码更加干净。由于帐户描述只是对提取的数据进行转换，因此这是最好的方法。
卢克

Answer 4

您不能在 linq-to-entities 中使用任意函数。已经解释过很多次了。例如此处。如果要在 Linq-to-entities 查询中使用自定义函数，则必须将其定义为数据库中的 SQL 用户定义函数 (UDF) 并映射它。

首先创建函数

CREATE FUNCTION dbo.udf_GetAccountTypeDescription (@Param VARCHAR(50))
RETRUNS VARCHAR(100)
AS
BEGIN
    RETURN @Param + ' ' + 'Item type'
END

现在您必须更新模型并导入函数（它将在存储过程中列出）。将函数导入 SSDL（EDMX 中的存储描述）后，您可以映射函数：

public static class EdmFunctions
{
    // First parameter is namespace of SSDL (open EDMX as XML if you are not sure about namespace)
    [EdmFunction("TestModel.Store", "udf_GetAccountTypeDescription")]
    public static string GetAccountTypeDescription(string parameter)
    {
        throw new NotSupportedException("This function is only for L2E query.");
    }
}

此函数只是表达式树中使用的占位符。当存储提供者将表达式树转换为 SQL 查询时，它将被映射的 SQL 函数替换。现在您可以在 Linq-to-entities 查询中使用它：

AccountTypeDescription = EdmFunctions.GetAccountTypeDescription("...")