EMIE EMIE - 7 months ago 98
SQL Question

C# Linq Count and Group

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.

Example:
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

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

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