CODEWITHSUNDEEP
c#.netlinqentity-frameworkasp.net-mvc-3
DotnetSparrow
DotnetSparrow

Reputation: 27996

using function in Linq

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

Upvotes: 2

Views: 4568

Answers (4)

Aducci
Aducci

Reputation: 26664

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

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

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

Upvotes: 0

luckyluke
luckyluke

Reputation: 1563

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

Upvotes: 0

Tim Rogers
Tim Rogers

Reputation: 21713

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

Upvotes: 2

Related Questions