LINQ to SQL查找以另一个表中的字符开头的值 [英] LINQ to SQL to find values starting with characters from another table

问题描述

我想从一个SQL表中检索一个值列表，其中的记录以另一个表中定义的前缀开头.

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

这篇文章给出了准确的答案，但这是针对EF，而不是Linq to SQL. 使用SQL时出现错误:

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

仅支持可在客户端上评估的参数 String.Contains方法

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

示例代码:

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);

这适用于EF.是的，我可以ToList()我的集合，但是表很大，查询变得很慢.关于如何在不枚举我的对象的情况下起作用的任何建议?

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?

推荐答案

这部分:.Any(p => item.Surname.StartsWith(p))给出错误:

String.Contains方法仅支持可以在客户端上求值的参数

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

它告诉您Contains方法不起作用，该参数只能在服务器上评估给定的参数. StartsWith基本上使用相同的机制. 因此，应该使用IndexOf代替Contains或StartsWith来查找包含参数是否出现在开头:

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)

---

根据MSDN:

---

According to MSDN:

IndexOf(T):

IndexOf(T):

如果在列表中找到项目的索引；否则为-1.

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

此答案部分来自此处.

这篇关于LINQ to SQL查找以另一个表中的字符开头的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！