2

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()?

David Sherret
  • 101,669
  • 28
  • 188
  • 178
  • You could get dataset into a list and then use a regular foreach loop and keep LastIndexOf(). Then it will be more readable for people unfamiliar with lambda expressions, and it certainly would not be much slower: http://stackoverflow.com/questions/3156059/linq-statement-faster-than-foreach-loop ... unless of course your dataset is huge. – Mike Trusov Nov 07 '12 at 01:36
  • My dataset is massive :( – David Sherret Nov 07 '12 at 01:45

1 Answers1

7

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.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • I would like to use two columns, but it would complicate things for the application. That's a neat trick! Thanks for your answer and that list of canonical functions. I guess an option would be to use your answer to limit the returned results, then once it's in memory use my original statement above to limit it down a bit more. – David Sherret Nov 07 '12 at 01:44