How do I convert my SQL query to Entity Framework DBContext

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
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:

SQL:
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:

C#:
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;
    }
}
 
Last edited by a moderator:

Neal

Forum Admin
Staff member
Joined
Apr 15, 2011
Messages
103
Location
VA
Programming Experience
10+
You get a jobList collection returned you can then apply LINQ against jobList.
 

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
Still the question remains for me is how do I incorporate this multiple subqueries
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
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

Forum Admin
Staff member
Joined
Apr 15, 2011
Messages
103
Location
VA
Programming Experience
10+
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.
 
Top Bottom