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

Question

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?

The "this post" link redirects to a website asking for credentials to download something. You sure it's a correct link? — V0ldek, Sep 05 '18 at 10:37
please see this post: https://stackoverflow.com/q/1703767/366064 for workaround — Bizhan, Sep 05 '18 at 10:44
@Bizhan: your link seems to provide the correct answer. If you post as answer, I will accept and you can get the creds. — Cameron Castillo, Sep 05 '18 at 10:54
@CameronCastillo sure, I added an answer. please take a look — Bizhan, Sep 05 '18 at 11:35

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

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

1 Answers1

Linked