CODEWITHSUNDEEP
c#linqlinq-to-sql
Cameron Castillo
Cameron Castillo

Reputation: 2832

LINQ to SQL to find values starting with characters from another table

I would like to retrieve a list of values from a SQL table where the records start with a prefix defined in another table.

This post gives an accurate answer, but it is for EF and not Linq to SQL. With SQL I get an error:

Only arguments that can be evaluated on the client are supported for the String.Contains method

Sample code:

var lookupList = dc.LookupTable.Select(p => p.Prefix);
var q = dc.Personnel
          .Where(item => lookupList
          .Any(p => item.Surname.StartsWith(p))).Select(x => x.PersonID);

This works with EF. Yes, I can ToList() my collections but the tables are big and the query becomes very slow. Any suggestions on how to make it work without enumerating my objects?

Upvotes: 1

Views: 669

Answers (1)

Bizhan
Bizhan

Reputation: 17085

This part: .Any(p => item.Surname.StartsWith(p)) gives the error:

Only arguments that can be evaluated on the client are supported for the String.Contains method

It tells you Contains method does not work with the given parameter which can only be evaluated on the server. StartsWith basically uses the same mechanism. So, instead of Contains or StartsWith you should use IndexOf to find out whether or not the containing parameter is occured at the beginning or not:

.Any(p => item.Surname.IndexOf(p) == 0)

According to MSDN:

IndexOf(T):

The index of item if found in the list; otherwise, -1.

This answer is partially taken from here.

Upvotes: 1

Related Questions