AFAIK,不可能在 LINQ to SQL 查询中使用内存中集合。我可以想到两种可能的解决方法:
选项 1:对每个 ID 执行查询:
var someSetOfIDs = new List<int>() {1, 2, 3, 4, 5};
// queryPerID will have type IEnumerable<IQueryable<'a>>
var queryPerID = from id in someSetOfIDs
select (
from dataEvent in DataEvent.GetByQueryExpression(context)
join attribute in DataEventAttribute.GetByQueryExpression(context)
on dataEvent.DataEventID
equals attribute.DataEventID
where attribute.DataEventKeyID == 1
&& (int)attribute.ValueDecimal == id // Changed from Contains
select new
{
ID = dataEvent.DataEventID,
PluginID = dataEvent.DataOwnerID,
TimeStamp = dataEvent.DataTimeStamp,
DataEventKeyID = attribute.DataEventKeyID,
ValueString = attribute.ValueString,
ValueDecimal = attribute.ValueDecimal
});
// For each of those queries, we an equivalent final queryable
var res = from initialQuery in queryPerID
select (
from dataEvent in DataEvent.GetByQueryExpression(context)
where initialQuery.GroupBy(x => x).Select(x => x.Key.ID).Contains(dataEvent.DataEventID)
select new
{
BasicData =
attributeBaseQuery.Where(
attrValue =>
attrValue.DataEventID == dataEvent.DataEventID &&
attrValue.DataEventKeyID == (short) DataEventTypesEnum.BasicData).FirstOrDefault().
ValueString
}) into finalQuery
from x in finalQuery
select x;
var finalResult = finalQuery.Take(100).ToList();
我不确定这是否可以编译,但应该非常接近。
选项 2:构建谓词表达式someSetOfIDs
传递给 SQL。
var someSetOfIDs = new List<decimal>() { 1, 2, 3, 4, 5 };
Expression<Func<DataEventAttribute, bool>> seed = x => false;
var predicate = someSetOfIDs.Aggregate(seed,
(e, i) => Expression.Lambda<Func<DataEventAttribute, bool>>(
Expression.OrElse(
Expression.Equal(
Expression.Property(
e.Parameters[0],
"ValueDecimal"),
Expression.Constant(i)),
e.Body),
e.Parameters));
本质上我们已经构建了一个 where 子句:
x => ((x.ValueDecimal = 5) || ((x.ValueDecimal = 4) || ((x.ValueDecimal = 3) ||
((x.ValueDecimal = 2) || ((x.ValueDecimal = 1) || False)))))
需要注意的是,这种方法不适用于匿名类型,因此您必须在具有命名类型的可查询对象上使用谓词。如果您稍微重新组织一下,这不是问题(实际上可能会产生更好的查询计划):
var attributes = DataEventAttribute.GetByQueryExpression(context)
.Where(a => a.DataEventKeyID ==1)
.Where(predicate);
var initialQuery = from dataEvent in DataEvent.GetByQueryExpression(context)
join attribute in attributes
select new
{
ID = dataEvent.DataEventID,
PluginID = dataEvent.DataOwnerID,
TimeStamp = dataEvent.DataTimeStamp,
DataEventKeyID = attribute.DataEventKeyID,
ValueString = attribute.ValueString,
ValueDecimal = attribute.ValueDecimal
};