c# - Get last index of character with LINQ to Entities

Question

I'm getting the error:

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 tell if a person's last name starts with certain characters:

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

Any clue how to achieve this without using LastIndexOf()? Maybe I have to check for this after I grab results from the database using ToList()?

Answer 1

You are limited by the set of canonical functions that can be translated into an SQL query, so any solution must be achieved with no more than the canonical functions offer.

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

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

This is not exactly like your current version (because it will allow people with more than one name to match their second name, while the former won't), but it's pretty close and IMHO can be acceptable.

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