LINQ to Entities 扩展;多个子查询、连接和 case 语句
本文关键字:查询 连接 语句 case Entities to 扩展 LINQ | 更新日期: 2023-09-27 18:32:37
我已经彻底搜索了我最新的LINQ工作的例子("嗨,我是LINQ新手"(。我不会告诉你我访问过的页面列表。
以下是我想要实现的示例 SQL:
use myDB;
go
declare @requestedDay datetime = convert(datetime, (convert(varchar(10), getdate(), 101)), 101)
declare @type1 int = 20
;with currentQuery as ( select
case when z.someID = @type1 and bet.someValue is null then 1
else 0 end as count1,
case when z.someID = @type1 and bet.someValue = alef.otherID then 1
else 0 end as count2,
from dbo.Work as dalet
left join dbo.Workers as z on alef.workerAssignedID = z.ID
left join dbo.Contracts as alef on alef.workOrderID = gimel.ID
left join dbo.Subcontracts as bet on alef.WorkOrderID = alef.WorkOrderID
and alef.WorkerAssignedID = dalet.WorkerID
where convert(varchar(10),alef.dateTimeofWork,101) = @requestedDay),
futureQuery as ( select
case when gimel.text_EN like '%blah%' and bet.someValue is null then 1
else 0 end as count3,
case z.someID = @type1 and bet.someValue = alef.otherID then 1
else 0 end as count4,
from dbo.Work as dalet
left join dbo.Workers as z on dalet.workerAssignedID = z.ID
left join dbo.Contracts as alef on dalet.workOrderID = alef.ID
left join dbo.Subcontracts as bet on dalet.WorkOrderID = bet.WorkOrderID and wa.WorkerAssignedID = wr.WorkerID
left join dbo.Lookups as gimel on dalet.skillID = gimel.ID
where convert(datetime,(convert(varchar(10),alef.dateTimeofWork,101)),101) > @requestedDay)
select sum(count1) as prop1name, sum(count2) as prop2name,
sum(count3) as prop3name, sum(count4) as prop4name
from currentQuery, futureQuery
当然,这是一个更大查询的缩短版本。它只包含我需要的基础知识。这些名字可能令人困惑,但我正在寻找一些独特的东西。它们还与下面的 LINQ 表亲匹配。
。也就是说,这就是我卡住的地方:("到目前为止我尝试过的东西:"(我包括了一些评论来描述我试图思考的问题。
//snip injection stuff above
public IQueryable<DailyCasaLatinaReport> DailyCasaLatina(DateTime dateRequested)
{
IQueryable<DailyCasaLatinaReport> query;
var daletQ = waRepo.GetAllQ();
var zQ = zRepo.GetAllQ();
var alefQ = alefRepo.GetAllQ();
var betQ = betRepo.GetAllQ();
var gimelQ = gimelRepo.GetAllQ();
int type1 = 20;
int type2 = 21;
query = daletQ
.GroupJoin(gimelQ, dalet => dalet.skillID, look => look.ID,
(dalet, look) => new
{
dalet,
enSkillText = look.FirstOrDefault().text_EN
}) //currently envisioning a left outer join of
//all .skillID, with English text available
//for column and condition matches from the
//next three joins.
.GroupJoin(betQ, gimel => gimel.dalet.workOrderID, wr => wr.WorkOrderID,
(gimel, wr) => new
{
gimel,
reqWorkerID = wr.FirstOrDefault().WorkerID,
reqOrderID = wr.FirstOrDefault().WorkOrderID
}) //now envisioning a join on the original table
//where any match in workerID is joined. THIS
//IS A PROBLEM, I actually need to join on two
//conditions to avoid duplicates.
.GroupJoin(alefQ, bet => bet.gimel.dalet.workOrderID, wo => wo.ID,
(bet, wo) => new
{
bet,
timeOfWork = wo.FirstOrDefault().dateTimeofWork
}) //now envisioning yet another join where the
//dateTimeofWork property from woQ is stamped
//onto all matches of woQ's ID column. since
//woQ.ID is the common point of reference for
//like, everything, there should be no nulls.
.GroupJoin(zQ, alef => alef.bet.gimel.dalet.workerAssignedID, w => w.ID,
(alef, w) => new
{
alef,
listDWC = alef.bet.someValue == 0 ? (w.FirstOrDefault().someID == type1 ? 1 : 0) : 0,
propioDWC = alef.bet.someValue == alef.bet.gimel.dalet.workerAssignedID ?
(w.FirstOrDefault().someID == type1 ? 1 : 0) : 0,
})
// here I'm stuck because there's no way to do
//the future conditions, i.e., what would be my
//second subquery
.Where(x => x.alef.timeOfWork == dateRequested)
.GroupBy(y => y.alef.bet.gimel.dalet.ID)
.Select(group => new dailyReport
{
count1 = group.Sum(z => z.listDWC),
count2 = group.Sum(z => z.propioDWC),
count3 = //???
count4 = //???
});
return query;
}
//snip class definition below
所以,很抱歉这个问题很长(虽然我已经看得更长了(,但是关于如何在这里挤压我的第二个子查询的任何想法都会有所帮助。我对LINQ不是那么了解,老实说,我不知道我是否可以放一个单独的.其中子句和/或第二.选择子句。需要注意的一点是,dailyReport 是一个定义的类,并且必须(在我们的示例中(定义 count1 到 count4。
感谢任何帮助,
哈伊姆
根据注释,确定分解这个怪物SQL查询将更有利于他的代码的可维护性,如果需要,将创建一个新问题。