如何编写类似于 SQL IN 查询的查询表达式?
我正在尝试做类似以下的事情:
let customerNumbers = set ["12345"; "23456"; "3456"]
let customerQuery = query {
for c in dataContext.Customers do
where(customerNumbers.Contains(c.CustomerNumber))
select c
}
但是我遇到了一个错误:
System.NotSupportedException: Method 'Boolean Contains(System.String)' has no supported translation to SQL.
查看 http://msdn.microsoft.com/en-us/library/hh225374.aspx 中的查询表达式文档,我应该使用另一个查询来处理包含部分,但是这段代码不起作用,示例已经损坏:
// Select students where studentID is one of a given list.
let idQuery = query { for id in [1; 2; 5; 10] do select id }
query {
for student in db.Student do
where (idQuery.Contains(student.StudentID))
select student
}
idQuery实际上不包含任何"Contains"方法。
我也尝试过:
let customerNumbers = set ["12345"; "23456"; "3456"]
let customerQuery = query {
for c in dataContext.Customers do
where (query { for x in customerNumbers do exists (c.CustomerNumber=x)})
select r
}
但是这会产生以下错误信息:
System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator
我在进一步测试后注意到,除了Gene的建议之外,以下方法也可以正常工作:
let customerNumbers = set ["12345"; "23456"; "3456"]
query {
for customer in dataContext.Customer do
where (query { for x in customerNumbers do contains customer.CustomerNumber})
select customer
}