Skip to content
Advertisement

Sql Query to Linq

How would I convert this query from SQL to Linq:

SELECT status As 'Status',
       count(status) As 'Count'
FROM tbl_repair_order 
WHERE contract = 'con' and 
      (status = 'Parts Arr' or 
       status = 'NA' or 
       status = 'New Call' or 
       status = 'Parts Ord' or 
       status = 'Parts Req' or 
       status = 'F Work')
GROUP BY status

Update

Thanks Guys, this is the code I used. Tested and returns the same as above:

        List<string> statuses = new List<string> { "Parts Arr", "NA", "New Call", "Parts Ord", "Parts Req", "F Work"}; 

        var result = (from x in db.tbl_repair_orders
                     where x.CONTRACT == strContract
                        && statuses.Contains(x.STATUS)
                     group x.STATUS by x.STATUS into grouping
                     select new { Status = grouping.Key, Count = grouping.Count() });

        return result;

Advertisement

Answer

    string[] statuses = new string[] { "Parts Arr", "NA", "New Call", "Parts Ord", "Parts Req", "F Work" };
    var x = (from ro in db.tbl_repair_order
             where ro.contract == "con"
                && statuses.Contains(ro.status)
             group 0 by ro.status into grouping
             select new { Status = grouping.Key, Count = grouping.Count() });

I don’t know if the syntax is correct (especially the last two lines) but it should be pretty close.

I added the 0 between group and by based on Eamon Nerbonne’s correction in the comments. Also, thanks to Ryan Versaw for the link explaining List and arrays for generating IN clauses.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement