Sql Query to Linq

Question

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;

I think you should heed Eamon's warning in the second bullet point of his answer. — AndyMcKenna
– AndyMcKenna, Commented Jul 20, 2009 at 16:09
Actually, the .Contains() call does correctly translate to LinqToSql (and maybe to the EF as well) - blog.wekeroad.com/2008/02/27/… — Ryan Versaw
– Ryan Versaw, Commented Jul 20, 2009 at 16:20
After reading that link, it's also worth noting that using an array instead of List will generate an IN statement. — AndyMcKenna
– AndyMcKenna, Commented Jul 20, 2009 at 16:41

AndyMcKenna · Accepted Answer · 2009-07-20 16:44:32Z

4

 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.

edited Jul 20, 2009 at 16:44

answered Jul 20, 2009 at 15:32

AndyMcKenna

2,6873 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ryan Versaw Over a year ago

It should be grouping.Key - besides that, I think it looks correct.

Ryan Versaw Over a year ago

The main difference I had in my version (similar enough I'm not posting a new answer) is to use a list. List<string> statuses = new List<string> { "Parts Arr", "NA", "New Call", "Parts Ord", "Parts Req", "F Work"}; Then change the where conditions to this: ro.contract = "con" && statuses.Contains(ro.status)

AndyMcKenna Over a year ago

@Ryan, thanks for the Key tip, I also noticed that after posting it.

AndyMcKenna Over a year ago

@Ryan, do you mind if I edit the answer to include your alternative? I like that better than the mass of ORs.

Eamon Nerbonne Over a year ago

@Andy: the syntax is group <expression to include in group> by <group key> into <groupName>

|

Eamon Nerbonne · Accepted Answer · 2009-07-20 15:49:26Z

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).

jpierson · Accepted Answer · 2009-08-04 18:31:12Z

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.

Collectives™ on Stack Overflow

Sql Query to Linq

3 Answers 3

11 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Related