Linq Help

Question

How to write this sql query in Linq below

SELECT tableExample.FilledByID, tableExample.ServiceType
FROM tableExample
GROUP BY tableExample.FilledByID, tableExample.ServiceType
HAVING (((tableExample.ServiceType)<>"ST-Service"));

Vulpes · Answer

Try: var query = from te in datacontext.tableExample group te by new {te.FilledByID te.ServiceType} into g where g.Key.ServiceType != ST-SERVICE select g.Key; // iterate results foreach(var q in query) { Console.WriteLine({0} {1} q.FilledByID q.ServiceType); }

Vulpes · Answer

Unless you're sure that it's what you really want I'd be careful using: CountOfFilledByID = g.Count(te => te.FilledByID > 0) which is not the same as Count(te.FilledByID) in SQL. The latter will only exclude NULL values not zero or negative values. Regarding the next part of the query you only need to use an anonymous type if there's more than one value to be returned - otherwise just use a 'scalar' value. Also you have a WHERE clause here rather than a HAVING clause so it needs to come before the grouping takes place. I'd therefore suggest: var query= from te in table where te.ServiceType == CS-Transportation || te.ServiceType == CS-Shopping group te by te.FilledByID into g select g.Key;

Vulpes · Answer

For that one I'd try: var query = from te in datacontext.tableExample group te by te.ServiceType into g where g.Key != ST-SERVICE select new {ServiceType = g.Key CountOfFilledByID = g.Count(te => te.FilledByID != null)}; // iterate results foreach(var q in query) { Console.WriteLine({0} {1} q.ServiceType q.CountOfFilledByID); }

David Smith · Answer

two more parts to the query Next part  I am still trying to translate into linq SELECT te.FilledByID FROM te WHERE (te.ServiceType)=CS-Transportation Or (te.ServiceType)=CS-Shopping GROUP BY te.FilledByID; so far i have this below I am trying to incorporate the where clause above in blue in the linq below. var query= from te in table group te by new { te.FilledById } into g select new { Num = g.Key.FilledById};

David Smith · Answer

I got it. Review below. CountOfFilledByID = g.Count(te => te.FilledByID > 0)};

Vulpes · Answer

Only if it's a nullable int. If it can never be null just use: CountOfFilledByID = g.Count() rather than: CountOfFilledByID = g.Count(te => te.FilledByID != null)

David Smith · Answer

You can check for null on an int. te.FilledByID is an integer. I am really stuck on this part of the linq CountOfFilledByID = g.Count(te => te.FilledByID != null)};

David Smith · Answer

The second part of the query have we get the return dataset how to execute the count in the linq look below. SELECT tableExample.ServiceType Count(tableExample.FilledByID) AS CountOfFilledByID FROM tableExample GROUP BY tableExample.ServiceType HAVING tableExample.ServiceType ST-Service

David Smith · Answer

getting ready to test out now