SQL使用Union All选择常量,然后使用IQueryable、EF联接
本文关键字:IQueryable 联接 EF 然后 Union 使用 All 选择 常量 SQL | 更新日期: 2023-09-27 18:25:38
使用EF 6
是否可以通过Linq生成以下SQL:
select partition,
<calculation>,
...
from
(
select ...
from SomeEntity sm
cross join (
select 'June' as [partition] 20140601 as [start], 20140630 as [end] union all
'July', 20140701, 20140731 union all,
'Sommar', 20140601, 20150828
) as cons
....
where sm.SomeProp >= cons.[start] and sm.SomeProp <= cons.[end]
)
group by partition
....
在上调用Queryable.SelectMany bails无法使用我定义为Array.AsQueryable()的cons,因为该对象不是基元或枚举。任何绕过使用Queryable弯曲Enumerable的方法,以便可以使用选择来制作临时cons表。。。联合所有构造?也许可以扩展提供商?
两种方法。一种是将你的问题转移到它的头上,并进行这样的查询:
方法1:
SELECT *
FROM SomeEntity
WHERE sm.SomeProp >= [start1] and sm.SomeProp <= [end1]
UNION ALL
SELECT *
FROM SomeEntity
WHERE sm.SomeProp >= [start2] and sm.SomeProp <= [end2]
...
UNION ALL
SELECT *
FROM SomeEntity
WHERE sm.SomeProp >= [startx] and sm.SomeProp <= [endx]
方法2:
SELECT *
FROM SomeEntity
WHERE 1=0 /* Always false */
OR (sm.SomeProp >= [start1] and sm.SomeProp <= [end1])
OR (sm.SomeProp >= [start2] and sm.SomeProp <= [end2])
..
OR (sm.SomeProp >= [startx] and sm.SomeProp <= [endx])
LINQ中的方法1看起来是这样的:
// Wrap in if(!array.Any()) if you want to handle the case where array is empty
var temp1=array.First(); // will throw exception if array is empty
var query=_db.SomeEntity.Where(s=>s.SomeProp>=temp1.start && s.SomeProp<=temp1.end));
foreach(var pair in array.Skip(1))
{
var temp=pair;
query=query.Union(
_db.SomeEntity.Where(s=>s.SomeProp>=temp.start && s.SomeProp<=temp.end));
}
LINQ中的方法2可能会使用这样的谓词生成器:
var predicate = PredicateBuilder.False<SomeEntity>();
foreach (var pair in array)
{
var temp=pair;
predicate = predicate.Or(s=>s=>s.SomeProp>=temp.start && s.SomeProp<=temp.end));
}
var result=_db.SomeEntity.Where(predicate);