In c#, how do you convert CAST CASE WHEN and SUM from sql to linq?

Question

Good day, everyone!

I'm working on a c# test automation script, and I have a sql query that returns a few values based on a few checks with cast, Case When, and Sum - but now we need to transfer it to Entity Framework as a linq query, and I'm not sure how to do so.

Could someone please assist me with this?

Here is my sql query

    SELECT
 co.orderId AS OrderId
 ,(SELECT
        COUNT(*)
    FROM ci_orders (NOLOCK) co
    INNER JOIN Customer (NOLOCK) cust
    ON cust.IdCustomer = co.customerId
    WHERE cust.Email = c.Email
    AND co.PaymentTransactionStatusID = 1)
AS CustomerOrdersCount

,co.PitStopStatus AS PitStopStatus

,(SELECT
        SUM(ops.Price + ops.QuantityDiscount)
    FROM OrderProductSelect (NOLOCK) ops
    WHERE ops.OrderId = 2257327)
AS NetPrice
,CAST(CASE WHEN Exists (SELECT
        oi.Id
    FROM OrderIssues (NOLOCK) oi
    LEFT JOIN PitStops ps
        ON ps.Id = oi.PitStopId
    WHERE oi.OrderId = co.orderId
    AND IssueType = 'HighValueOrder') THEN 1
    ELSE 0 END AS BIT)
AS IsHighValuePitStop
FROM ci_orders (NOLOCK) co
INNER JOIN Customer (NOLOCK) c
    ON c.IdCustomer = co.customerId
 WHERE co.orderId =2257327

@eldar - I tried below way

  using (var context = GetDbContext())
        {
            var yellowOrder = context.YellowOrders.FirstOrDefault(x => x.OrderId == orderId);

            var result = (from co in context.Orders
                          join c in context.Customers
                          on co.customerId equals c.IdCustomer
                          where co.orderId == orderId
                          select new
                          {
                              OrderId = orderId,
                              IsHighValuePitStop = (from oi in context.OrderIssues
                                                    join ps in context.PitStops
                                                    on oi.PitStopId equals ps.Id
                                                    where oi.OrderId == orderId && ps.IssueType == "HighValueOrder")
                          }).Any();

            return yellowOrder != null;
        }

Answer 1

You can achieve Case When in linq with a ternary (?:) operator. And since your queries are mostly inner selects your linq should look like this :

var result = await (
from co in context.ci_orders 
join c in context.Customers on co.customerId eqals c.IdCustomer
where co.orderId =2257327
select new { // here your inner selects go
OrderId = co.orderId,
IsHighValuePitStop = (from oi in context.OrderIssues 
                      join ps in context.PitStops on oi.PitStopId equals ps.Id
                      where oi.OrderId = co.orderId and oi.IssueType = 'HighValueOrder').Any(),  // you could use ternary operator but Any already returns a boolean value ? true : false would be meaningless
NetPrice = context.OrderProductSelect.Where(r=> r.OrderId = 2257327).Sum(ops=> ops.Price + ops.QuantityDiscount),
co.PitStopStatus,
CustomerOrdersCount = (from co in context.ci_orders
                       join cust context.Customer on co.customerId equals cust.IdCustomer
                       where cust.Email = c.Email
    && co.PaymentTransactionStatusID = 1
select co).Count()
}).ToArrayAsync()