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
– J. Steen, Commented May 13, 2011 at 13:19
@Steen: How Can I modify query to use this function as join ? — DotnetSparrow
– DotnetSparrow, Commented May 13, 2011 at 13:22

Tim Rogers · Accepted Answer · 2011-05-13 12:46:31Z

2

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

answered May 13, 2011 at 12:46

Tim Rogers

21.8k8 gold badges55 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DotnetSparrow Over a year ago

actually GetAccountTypeDescription will be called for each account row like this GetAccountTypeDescription(account.accountType) I cant define it before query. any suggestions?

J. Steen Over a year ago

@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. =)

Tim Rogers Over a year ago

Then you have to inline the logic your function into your query.

Aducci · Accepted Answer · 2011-05-13 14:13:58Z

0

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

edited May 13, 2011 at 14:13

answered May 13, 2011 at 13:11

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

2 Comments

DotnetSparrow Over a year ago

actually, I have added simple function. I have added original function please check the Edit.

Aducci Over a year ago

@DotnetSparrow - Updated answer to include a left join on context.AccountTypes

luckyluke · Accepted Answer · 2011-05-13 13:17:12Z

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, 2011 at 13:17

luckyluke

1,5639 silver badges17 bronze badges

2 Comments

DotnetSparrow Over a year ago

@Luke: I have AccountTypeDescription as property in AccountReport

luckyluke Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:12:03Z

0

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("...")

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered May 13, 2011 at 13:27

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

2 Comments

DotnetSparrow Over a year ago

I am using Entity Framework 4.1 code first approach and not Edmx

Ladislav Mrnka Over a year ago

In such case forget about calling any custom function in Linq-to-entities and rewrite your query.

Collectives™ on Stack Overflow

using function in Linq

4 Answers 4

3 Comments

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related