Select FROM Subquery without starting with another context object

Question

I am trying to model the following MSSQL query that I am trying to replicate in netCore 2.2 - EF Core:

SELECT
wonum,
MIN(requestdate)        AS startdate,
MAX(requestdate)        AS enddate,
MIN(laborcode)
FROM
    (
        SELECT
            wo.wonum,
            sw.requestdate,
            wo.wolablnk     AS 'laborcode'
        FROM
            DB1.dbo.web_users           wu  INNER JOIN
            DB2.dbo.workorder           wo on
            wu.laborcode = wo.wolablnk          INNER JOIN
            DB2.dbo.sw_specialrequest   sw  on
            wo.wonum = sw.wonum
    WHERE
            wo.status           in ('LAPPR', 'APPR', 'REC') AND
            sw.requestdate      > GETDATE()
    ) a   
GROUP BY
   wonum
ORDER by

I have the subquery portion built and working but that leaves me at an impasse:

        var workOrders = await _db1Context.Workorder
            .Where(r => r.Status == "LAPPR" || r.Status == "APPR" || r.Status == "REC")
            .ToListAsync();

        var specialRequests = await _db2Context.SwSpecialRequest
            .Where(r => r.Requestdate > DateTime.Now)
            .ToListAsync();

        var subQuery = (from webUser in webUsers
                        join workOrder in workOrders on webUser.Laborcode equals workOrder.Wolablnk
                        join specialRequest in specialRequests on workOrder.Wonum equals specialRequest.Wonum
                        orderby webUser.Laborcode, specialRequest.Requestdate, specialRequest.Wonum
                        select new { workOrder.Wonum, Laborcode = workOrder.Wolablnk, specialRequest.Requestdate, workOrder.Workorderid })
                        .ToList();

I am not sure how to initiate the query I need with the subquery i've built and i'm not sure if I am on the right track even. I've looked at a couple of other examples but i'm not getting it.

Would anyone be able to shed some light on the subject and help?

Thank you!

Answer 1

Write LINQ query identical to the SQL and do not mix with ToListAsync(). After ToListAsync() query is sent to the server. Also you should use only one DbContext for such query.

var webUsers = _db1Context.Webuser;

var workOrders = _db1Context.Workorder
   .Where(r => r.Status == "LAPPR" || r.Status == "APPR" || r.Status == "REC");

var specialRequests = _db1Context.SwSpecialRequest
   .Where(r => r.Requestdate > DateTime.Now);

var subQuery = 
   from webUser in webUsers
   join workOrder in workOrders on webUser.Laborcode equals workOrder.Wolablnk
   join specialRequest in specialRequests on workOrder.Wonum equals specialRequest.Wonum
   select new 
   { 
       workOrder.Wonum, 
       Laborcode = workOrder.Wolablnk, 
       specialRequest.Requestdate
   };

var resultQuery = 
   from a in subQuery
   group a by a.Wonum into g
   select new 
   {
       Wonum = g.Key,
       StartDate = g.Min(x => x.Requestdate),
       EndDate = g.Max(x => x.Requestdate),
       Laborcode = g.Min(x => x. Laborcode)
   };

// final materialization
var result = await resultQuery.ToListAsync();