Select FROM Subquery without starting with another context object

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top