How do I convert my SQL query to Entity Framework DBContext

[Palak Shah]

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

 

[Neal]

You get a jobList collection returned you can then apply LINQ against jobList.

 

[Palak Shah]

Still the question remains for me is how do I incorporate this multiple subqueries

 

[Skydiver]

I avoid EF like the plague, but my understanding is that EF has the option to just pass in some raw SQL and it will perform the query. Obviously, it won't know how to map the results of the query back into entities, so that is up to you to instantiate the entities based in the results.

 

[Neal]

I love EF especially EF Core, they've made huge improvements. Watch the recent Channel 9 series to learn more. But that's off topic.