将sql交叉连接转换为linq

gdrx4gfi  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(409)

我有一张table

SELECT JM.jobNum+'.'+CONVERT(VARCHAR,TL.tlPhasenum) AS [COST CODE],EM.empnum+'‌' AS [EMPLOYEE NUMBER],TL.tlDateWorked AS [DATE],tl.tlOT,tl.tlStraightTime INTO #TempTable
FROM mqTimeReportingTimeLogs AS TL
    INNER JOIN mqJobMaster AS JM ON TL.tlJobId=JM.jobId
    INNER JOIN mqEmployeeMaster AS EM ON TL.tlEmployeeId=EM.empId
    WHERE 
    TL.tlCompanyId in (select * from #TempCompanies) 
    AND (@weekending='' or tlWeekEnding=CONVERT(DATE,@weekending)) 
    AND (@jobid=0 or TL.tlJobId=@jobid)
    AND (@startdate='' or TL.tldateworked between convert(date,@startdate) and convert(date,@enddate))

以及以下要拆分为两行的查询

SELECT * INTO #TempTable2
 FROM (
    SELECT [COST CODE],[EMPLOYEE NUMBER],[DATE],value as [Hours],col as [Time Type]
    from #TempTable
    CROSS APPLY
    (
        VALUES('1',tlStraightTime),('2',tlOT) --1:ST  2:OT
    ) C (COL, VALUE)
 ) SRC

现在我想在linq中实现同样的事情,如何在linq中使用这个逻辑

yeotifhr

yeotifhr1#

一旦你翻译了 TempTable 查询到linq,类似于:

var TempTable = from tl in mqTimeReportingTimeLogs ...

然后,您可以通过使用 Select 以及 Concat (例如。 SELECT 以及 UNION ALL )哪些linq到sql可以转换:

var ans = from t in TempTable
          from c in TempTable.Take(1).Select(_ => new { col = 1, value = t.tlStraightTime }).Concat(TempTable.Take(1).Select(_ => new { col = 2, value = t.tlOT }))
          select new {
              t.CostCode,
              t.EmployeeNumber,
              t.Date,
              c.col,
              c.value
          };

相关问题