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.