C# - LINQ to Entities does not recognize the method

Question

I'm trying to do somethig like that:

string queryText = searchtext.ToUpper().RemoveDiacritics();

var result = from p in People
             where p.Name.ToUpper().RemoveDiacritics().Contains(queryText)
             select p;

And I get this error: LINQ to Entities does not recognize the method RemoveDiacritics.

I need to apply the method RemoveDiacritics to the field Name from database.

Somebody knows how I can do that.

Thanks, regards!

You will have to get the data first and then call the custom method to filter. Which is not efficient. So, you need to find how you can search on the data without calling the custom function on the table data. — Harsh, Aug 09 '17 at 10:09

Harald Coppoolse · Accepted Answer · 2017-08-09T13:34:48.933

You have to be aware of the difference between IEnumerable and IQueryable

The advantage of IQueryable is that your query will be executed by the prodiver of the IQueryable, which is usually a database. Transferring your data from the database to local memory is a relatively slow process, so it is best to limit the data that must be transferred by letting the database do as much as possible.

The disadvantage of IQueryable is that it only knows the funcitonality of the provider. In case of SQL check the list of

Supported and Unsupported LINQ Methods (LINQ to Entities)

This means that you can't use your own functions like RemoveDiacritics.

The solution of your problem depends on how often you have to do this query, and whether this query must be performed very fast

If the query isn't done very often and it doesn't have to be at light speed, it is enough to add AsEnumerable()

var result = People
    .AsEnumerable()
    .Where(p => p.Name.ToUpper().RemoveDiacritics()....);

If your query is done very often and has to be really fast, consider extending your People table with a value that contains the value of Name.ToUpper().RemoveDiacritics()

class People
{
   public string Name {get; set;}
   public string NameWithoutDiacritics {get; set;}
   ...
}

Whenever your model Adds or Updates a People object, make sure NameWithoutDiacritics contains the correct value

public void Update(People people)
{
     using (var dbContext = new MyDbContext())
     {
          var foundPeople = people.Find(people.Id);
          foundPeople.Name = people.Name;
          foundPeople.NameWithoutDiacritics = people.Name
               .ToUpper()
               .RemoveDiacritics();
          ...
          dbContext.SaveChanges();
     }
}

Change your query:

var result = People.Where(p => p.NameWithoutDiacritics.Contains(queryText));

Thanks Herald, very useful your comment. I solved my issue using something like you propose. Best regards! — pleongomez, Aug 09 '17 at 12:51

score 0 · Answer 2 · answered Aug 09 '17 at 11:00

It is impossible because EF does NOT know how to translate 'RemoveDiacritics' method to SQL. So If You need to do this logic from database then maybe You should create stored procedure, otherwise You need to get all records to memory and then apply this filtering.

C# - LINQ to Entities does not recognize the method

2 Answers2