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!

Do yourself a favor and create a proper class model first. With navigation properties, so you don't need manual joins and gain a lot of expressiveness. And show the class model. — Gert Arnold, Nov 06 '20 at 09:43
Thank you and I agree about models! Sometimes I dont start with models when I am learning, maybe I should have. It definitely would make for a more understandable help request for sure. Honestly, I hadn't thought about nav props at first and would love to use that approach. This is a microservice system with 3 separate dbs across 2 separate db servers - the tables are not all linked in 1 database or even server - it's probably possible to make a class with nav props to represent this, but i'm just not there yet. Thanks again, I will look into possibilities and build classes once i'm finished! — ogg130, Nov 06 '20 at 15:43

Svyatoslav Danyliv · Accepted Answer · 2020-11-06T08:57:38.653

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();

Thank you, working through this now. I have three separate databases i'm working with, one using code first one on database server and two using database first on a second database server. I am unsure about how I can or if I can have one context with this model, but will research it and appreciate the help - the bit about when to use ToListAsync() was super — ogg130, Nov 06 '20 at 15:34
@ogg130, You cannot create cross database queries using EF Core. — Svyatoslav Danyliv, Nov 06 '20 at 15:51

Select FROM Subquery without starting with another context object

1 Answers1