将sql交叉连接转换为linq

gdrx4gfi 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(407)

我有一张table

SELECT JM.jobNum+'.'+CONVERT(VARCHAR,TL.tlPhasenum) AS [COST CODE],EM.empnum+'&#8204;' 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中使用这个逻辑

sql sql-server linq linq-to-sql

来源：https://stackoverflow.com/questions/62428480/convert-cross-join-of-sql-to-linq

1条答案

按热度按时间

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
          };

赞(0）回复(0）举报 2021-07-26

我来回答

将sql交叉连接转换为linq

1条答案

相关问题

热门标签

最新问答