Linq到SQL，其中两列必须从列表中匹配

Question

上一次查询

var orderNameList = GetOrderNames().ToList();
var orderTypes = orderTypeRepository.Query().Where(x => orderNameList.Contains(x.Name)).ToList();

New Query(New Requirement-Get OrderTypes where name and Region match from the list(

var orderNameAndRegionList = document.OrderTypes
.Select(x=> new { x.Name, x.RegionCode}).ToList(); //Anonymous Order list {Name, RegionCode}
var orderTypes = orderTypeRepository.Query()
.Where(x => orderNameAndRegionList.Any(p => p.Name == x.Name && p.RegionCode == x.RegionCode))
.ToList()

这个新查询抛出NHibernate异常

System.NotSupportedException: : ( : p1 )
at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName(IASTNode querySource)

我试图避免将查询放入foreach循环中，如下所示：

orderNameAndRegionList.ForEach(x =>
{
var orderType = orderTypeRepository.Query().SingleOrDefault(r => r.Name == x.Name && r.RegionCode == x.RegionCode);
orderTypes.Add(orderType);
});

有没有更好的解决方案来避免循环内部的查询？

Answer 1

了解LINQ是如何工作的是您案例中的关键。如果GetOrderNamesWithRegion返回IQueryable<gt；类型，您可以简单地在第一个解决方案中使用它。下面是一个愚蠢的例子，解释了什么时候会发生。

NHibernate不支持您使用List<anon<字符串，字符串>gt；在LINQ查询内部，但应允许使用IQueryable。

using System;
using System.Linq;
namespace LINQExplanation
{
class Program
{
static void Main(string[] args)
{
string[] db = new string[] { "value 1", "value 2", "value 3" };
// Stage 1: My nonsense query but you get the picture
var query = db
.Where(v => v == "value 1")
.Where(v => v == "value 2")
.Where(v => v == "value 3");
query.Where(v => v == "Non existent");
// Until now nothing is read from the array;
// Stage 2: the .ToList() resolves the query and the array is read.
var result = query.ToList();
Console.ReadKey();
}
}
}