CODEWITHSUNDEEP
c#entity-framework
D-W
D-W

Reputation: 5341

Entity Framework - String conversion error

I'm getting the following error, but im not sure how to rewrite my statement? Any ideas?

Error:

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

Code:

public Client FindClientByMobile(string mobile, string accountId)
{
    Client client = RepositorySet.Include("Account").FirstOrDefault(c => c.AccountId == accountId && !c.IsDeleted
            && ((Convert(c.TelephoneHome) == mobile) || (Convert(c.TelephoneMobile) == mobile) || (Convert(c.TelephoneWork) == mobile)));
    return client;
}

public static string Convert(string mobile)
{
    var filterNumber = from letter in mobile
                       where char.IsDigit(letter)
                       select letter;

    StringBuilder number = new StringBuilder();
    number.Append(filterNumber.ToArray());

    return number.ToString();
}

Upvotes: 0

Views: 622

Answers (3)

Steve Cooper
Steve Cooper

Reputation: 21480

The first issue is that the Convert call is a C# and can't be translated into SQL. Not all functions can, but some (substring, for example) are 'known' to Entity Framework and it can convert them to the appopriate SQL. So you either need to rewrite your statement to use the string functions EF is aware of, or push the logic down into the database some other way.

@Imad has already suggested using string.Replace, which is known to EF, but of course it's not going to help you remove every possible non-digit character - only ones you are explicitly coding for, like '-', but not other alpha strings like 'extension'.

If you want to make this fast, it might be a good idea to store the 'cleaned' numbers in separate fields. Then the query becomes

Client client = RepositorySet.Include("Account").FirstOrDefault(c => c.AccountId == accountId && !c.IsDeleted
        && (c.TelephoneHomeClean == mobile) || (c.TelephoneMobileClean == mobile) || (c.TelephoneWorkClean == mobile)));
return client;

and you have something that can be made much faster and indexed.

Upvotes: 0

Imad
Imad

Reputation: 7490

Does this suits you, as you mentioned in your comment

@Imad, what im trying to do is validate, so if the number has been stored in the database as 0331-9000-100, I want to remove all non numeric characters, mobile has already had this applied, so mobile = 033319000100

public Client FindClientByMobile(string mobile, string accountId)
{
    Client client = RepositorySet.Include("Account").FirstOrDefault(c => c.AccountId == accountId && !c.IsDeleted
            && ((c.TelephoneHome.Replace("-","") == mobile) || (Convert(c.TelephoneMobile) == mobile) || (Convert(c.TelephoneWork) == mobile)));
    return client;
}

using Replace you can also replace other characters like ( and ) also.

Point to remember: Replace(char, char) won't work but Replace(string, string) will.

Upvotes: 1

Igor
Igor

Reputation: 62213

The error means that Linq needs to translate your expression into a Sql statement. Your custom Convert method is not translatable because it is c# code and not something that also exists on the database server.

As you are already passing in your account id I am going to assume this is either unique OR filters it down enough to where it is close to unique to ensure you are not retrieving a large number of objects. You can then materialize the object graph first and then filter more in c# (linq to objects). This is done by using the ToList() call.

public Client FindClientByMobile(string mobile, string accountId)
{
  var clients = RepositorySet.Include("Account").Where(c => c.AccountId == accountId && !c.IsDeleted).ToList();
  return clients.FirstOrDefault(client => Convert(client.TelephoneHome) == mobile || Convert(client.TelephoneMobile) == mobile || Convert(client.TelephoneWork) == mobile);
}

Upvotes: 4

Related Questions