CODEWITHSUNDEEP
c#sqllinq
EMIE
EMIE

Reputation: 117

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.

  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;
}

Upvotes: 0

Views: 100

Answers (2)

Abhinav Galodha
Abhinav Galodha

Reputation: 9878

Another way using Linq..

Adding assemblySystem.Data.DataSetExtensions, you can convert DataTable to List

            var listOfFaultCode = dt1.Rows     
                                     .OfType<DataRow>()
                                     .Select(dr => dr.Field<int>("FaultCode")).ToList();

            var faultCodeGroupedByCount = listOfFaultCode.GroupBy(x => x);

            foreach (var item in faultCodeGroupedByCount)
            {
                Console.WriteLine("FaultCode:" + item.Key + " FaultCount:" + item.Count());
            }

Upvotes: 0

m4gik
m4gik

Reputation: 450

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

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

Upvotes: 1

Related Questions