EMIE EMIE - 2 years ago 80
SQL Question

Group and Count using LINQ

I have a sql db that every time a device goes bad, it creates a record. Based on what happens, a different FaultCode is assigned.

I want to group and count the number of times an FaultCode exists.

FaultCode Count
1 6
2 20

I've written most of the code, I can query the db and execute a linq query. However, I can only return a list of Fault codes of the Counts. But not both.

Here is the code:

private static DataTable FaultCodeByCluster(DataTable referenceDt)
DataTable output = new DataTable();

foreach (DataColumn dtColum in (InternalDataCollectionBase)referenceDt.Columns)
output.Columns.Add(new DataColumn(dtColum.ColumnName, dtColum.DataType));

var query = from results in referenceDt.AsEnumerable()
group results by new
FaultCode = results.Field<int>("FaultCode"),
into newFaultCodes
orderby newFaultCodes.Key.FaultCode

select newFaultCodes.Count(); <--- count of fault codes
//select newFaultCodes.Key.FaultCode; <--- list out fault codes by group

foreach (var newFaultCodes in query)
Console.WriteLine("Value is {0}", newFaultCodes);

return output;

Answer Source

I haven't tested it, but try using something like this as your select statement:

select new { FaultCode = newFaultcodes.Key, Count = newFaultcodes.Count()};

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download