using function in Linq

Question

I have this query:

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

which is giving error:

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

Function is :

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

If I don't use GetAccountTypeDescription in LINQ query, It works.

Please suggest solution

From your edit, I don't understand why you don't simply include that in the larger linq query as a join. — J. Steen, May 13 '11 at 13:19
@Steen: How Can I modify query to use this function as join ? — DotnetSparrow, May 13 '11 at 13:22

score 2 · Answer 1 · answered May 13 '11 at 12:46

You LINQ query is not run locally, but is turned into an expression (which is turned into SQL) and executed your database server. The expression parser cannot turn your function into SQL because that function doesn't exist on the server. You can only use LINQ and a few other .NET functions inside a LINQ query that is to be run on the database. You can easily fix this as such

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

actually GetAccountTypeDescription will be called for each account row like this GetAccountTypeDescription(account.accountType) I cant define it before query. any suggestions? — DotnetSparrow, May 13 '11 at 13:07
@DotnetSparrow, You may want to edit your question to include that bit of information, as the code you currently have as your example is not correct. =) — J. Steen, May 13 '11 at 13:12
Then you have to inline the logic your function into your query. — Tim Rogers, May 13 '11 at 14:00

Aducci · Accepted Answer · 2011-05-13T14:13:58.587

Edit - Adding a left join

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

actually, I have added simple function. I have added original function please check the Edit. — DotnetSparrow, May 13 '11 at 13:17
@DotnetSparrow - Updated answer to include a left join on context.AccountTypes — Aducci, May 13 '11 at 14:11

score 0 · Answer 3 · answered May 13 '11 at 13:17

0

I kind of don't understand this. Why don't You provide a property to the class/struct AccountReport that gives YOu AccountTypeDescription... That way You encapsulate the logic, hid the implementation details and generally make the code a lot kosher. Since the Accoutn description is just a transformation on the pulled data that is the best way to do. luke

answered May 13 '11 at 13:17

luckyluke

1,553
9
16

@Luke: I have AccountTypeDescription as property in AccountReport – DotnetSparrow May 13 '11 at 13:22
But it is not doing the right JOB! Encapsulate the logic in the property not just provide a simple data getter and setter. Set the them of AccountName and the property Account Description will give You the correct format. That way when You change the format of accoutn description you change it in one place, and if You have hierarchy of accounts You can have different description for each of them... A fucntion like formatAccountDescritpion is highly reduntant here and is a code smell in my opinion – luckyluke May 13 '11 at 13:28

score 0 · Answer 4 · edited May 23 '17 at 12:12

You cannot use arbitrary function in linq-to-entities. It was explained many times. For example here. If you want to use custom function in Linq-to-entities query you must define it as SQL User defined function (UDF) in your database and map it.

First create function

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

Now you must update your model and import the function (it will be listed among stored procedures). Once you have your function imported in SSDL (storage description in EDMX) you can map the function:

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

This function is only placeholder used in expression tree. It will be replaced by mapped SQL function when store provider translates expression tree to SQL query. Now you can use this in your Linq-to-entities query:

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

I am using Entity Framework 4.1 code first approach and not Edmx — DotnetSparrow, May 13 '11 at 13:32
In such case forget about calling any custom function in Linq-to-entities and rewrite your query. — Ladislav Mrnka, May 13 '11 at 13:33

using function in Linq

4 Answers4