CODEWITHSUNDEEP
sqllinqlinq-to-sql
MartGriff
MartGriff

Reputation: 2851

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;

Upvotes: 2

Views: 1437

Answers (3)

jpierson
jpierson

Reputation: 17384

As far a s converting SQL statements to equivalent Linq to SQL statements you should check out the Linqer tool which does just that. I don't think this app is good to use for re-writting your whole application but it can be a very useful tool for learning Linq to SQL in general.

Upvotes: -1

AndyMcKenna
AndyMcKenna

Reputation: 2647

    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.

Upvotes: 4

Eamon Nerbonne
Eamon Nerbonne

Reputation: 48146

Assuming you wire up your table's appropriately, something like

var statusCounts =
    from row in youDbNameHere.tbl_repair_order
    where row.contract == "con"
        && (row.status == "Parts Arr"
        || row.status == "NA"
        || row.status == "New Call"
        || row.status == "Parts Ord"
        || row.status == "Parts Req"
        || row.status == "F Work")
    group 0 by row.status into g
    select new { Status = g.Key, StatusCount = g.Count() };

...and I see Andy beat me to it ;-)

Notes:

  • You need to include an expression between "group" and "by", this expression is will be evaluated to form the set of values accessible under the group's key (in your case it's irrelevant, so a zero is fine).
  • If you wish to use Linq-to-Sql or Linq-to-Entities (or some other IQueryable implementation), be aware that your code will not execute directly in C#, but rather be translated (as it should be) into sql -- so avoid using .NET specific calls that cannot be translated, as these will generally cause a run-time exception or (rarely) cause the resulting query to be partially evaluated client-side (at a potentially hefty performance cost).

Upvotes: 2

Related Questions