CODEWITHSUNDEEP
linq-to-sql
firepol
firepol

Reputation: 1751

LINQ to SQL: IQueryable - string contains - method has no supported translation to SQL

Dear LINQ or linq2sql experts, can you help me to solve this case?

I have Book class, every book has a List and what I want is to find the Books whose Authors lastnames in the Authors list contain a particular searchAuthor string, as follows:

string searchAuthor = "foo";
IQUeryable books = BookStaticRepository.SelectAll();

books.Where(r => r.Authors.Any(x => searchAuthor.Contains(x.lastname, StringComparison.OrdinalIgnoreCase)));

What I get it this error:

Method 'Boolean Contains(System.String, System.String, System.StringComparison)' has no supported translation to SQL.

I already tried the same but using IEnumerable, and it worked. I had to switch to IQueryable for performance issues... I need it to work with IQueryable.

Thanks for your help.

Upvotes: 5

Views: 8813

Answers (2)

firepol
firepol

Reputation: 1751

Hi guys thanks I found the solution myself:

books.Where(r => r.Authors.Any(x => x.lastname.Contains(searchAuthor)));

The mistake -I think- was that I was doing a sort of "opposite work": I was saying like "the lastname has to contain the searchAuthor string" and not viceversa (the lastname has to contain the SearchAuthor string... which now gives me the results I'm expecting).

The funny thing is that the exact same query I posted in my initial question was working fine using IEnumerable.

Upvotes: 2

Hux
Hux

Reputation: 3122

That particular overload of Contains does not work, but ignoring the StringComparison will make it translate to SQL, which will generate a LIKE %SearchInput%. This will be case-insenstive by default, unless your DB is set to be case senstive.

Like so:

string searchAuthor = "foo";
IQUeryable books = BookStaticRepository.SelectAll();

books.Where(r => r.Authors.Any(x => searchAuthor.Contains(x.lastname)));

Upvotes: 6

Related Questions