Alex Lonyay

Alex Lonyay

  • NA
  • 123
  • 9.6k

How do I convert my SQL query to entity framework dbcontext

Jun 12 2020 4:19 AM
Hello All,
I have one query which I wrote in SQL but now i want to convert it to EF and I am finding difficulty on understanding how do I incorporate multiple select statements in EF
SQL Query:
SELECT TOP {count} * FROM (SELECT co.orderid,co.orderStatusId, co.paymenttransactionstatusid, (SELECT Count(IdProductSelect) FROM orderproductselect ops WHERE ops.orderid = co.orderid AND co.orderstatusid IN ( 2, 9 ) AND ops.quantity - ops.splitquantity > 1) AS LineItemsCount FROM ci_orders co) AS temp WHERE lineitemscount > 1 AND paymenttransactionstatusid = 1 AND orderid NOT IN (SELECT orderid FROM ordersplit) AND orderid Not IN (SELECT ChildOrderId FROM ordersplit) ORDER BY LineItemsCount
Tried EF:
public static List<ci_orders> GetOrdersWithMultipleLineItemAndQTY(int count)
{
using (var context = GetDbContext())
{
var jobList = context.Orders
.OrderByDescending(x => x.orderId)
.Take(count).ToList();
return jobList;
}
}

Answers (2)