Convert Cross join of sql To Linq

Question

i have a 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))

and the following query to split on row to two

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

Now i want to implement the same thing in linq ,How to use this logic in LINQ

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you translate the first query. Then a `CROSS APPLY` can be done as a `from`...`from`...`select`. — NetMage, Jun 18 '20 at 00:12
However, I don't think you will be able to run the second query server side, you'll have to pull over the first query and then use LINQ to Objects. — NetMage, Jun 18 '20 at 00:18

score 0 · Answer 1 · answered Jun 18 '20 at 18:08

Once you have translated the TempTable query into LINQ, something like:

var TempTable = from tl in mqTimeReportingTimeLogs ...

then you can do the second query by creating an inline table with Select and Concat (e.g. SELECT and UNION ALL) which LINQ to SQL can translate:

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

Convert Cross join of sql To Linq

1 Answers1