locked
How do I convert my SQL query to Entity Framework DBContext RRS feed

  • Question

  • 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

    Entity Framework tried

    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;
                }
            }

    Friday, June 12, 2020 9:45 AM

All replies

  • You can try to execute raw T-SQL.

    https://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

    You can put the T-SQL in a stored procedure and execute it.

    https://www.entityframeworktutorial.net/EntityFramework4.3/execute-stored-procedure-using-dbcontext.aspx

    Linq is not a remedy  for everything.

    Saturday, June 13, 2020 4:38 PM