Get last character index with LINQ to Entities

Question

I get an error message:

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

When using this code to find out if a person’s last name begins with certain characters:

 persons = persons.Where(c => c.FullName.IndexOf(" ") > 0 && c.FullName.Substring(c.FullName.LastIndexOf(" ")+1).StartsWith(lastNameSearch)); 

Any tips on how to achieve this without using LastIndexOf ()? Perhaps I need to check this after I get the results from the database using ToList ()?

Answer 1

You are limited to a set of canonical functions that can be translated into a SQL query, so any solution must be achieved no more than canonical functions are offered.

Fortunately, one of the supported functions is the instance method bool Contains(string) . You can rewrite your check as

 persons = persons.Where(c => c.FullName.Contains(" " + lastNameSearch)); 

This is not quite like your current version (because it will allow people with multiple names to match their middle name, but the first will not), but it's pretty close, and IMHO might be acceptable.

Of course, it would be much better than any of them to save the last names as a separate column in the database, if at all possible.