CODEWITHSUNDEEP
asp.netlinq
Ram
Ram

Reputation: 923

How to calculate count on of table column using group by clause in linq

I'm new to linq. In c# I'm doing as follows to get the count of one column.

SELECT   DispatcherName,
         ActivityType,
         CONVERT(BIGINT,COUNT(ActivityType)) AS Total
FROM     ACTIVITYLOG
GROUP BY DispatcherName,
         ActivityType
ORDER BY Total DESC

Can any one tell m,how I can achieve the same thing using LINQ.

Update:

HI I did as follows and got the reslut. But I'm not able to convert result to datatable. this is how I did. here dt is datatabe with two columns Dispatchername and ActivityType.

 var query1 = from p in dt.AsEnumerable()
                             group p by new
                             {
                                 DispatcherName = p.Field<string>("Dispatchername"),
                                 Activity = p.Field<string>("ActivityType"),
                             }
                                 into pgroup
                                 let count = pgroup.Count()
                                 orderby count
                                 select new
                                 {
                                     Count = count,
                                     DispatcherName = pgroup.Key.DispatcherName,
                                     Activity = pgroup.Key.Activity
                                 };

pls help me out asap.

Upvotes: 2

Views: 585

Answers (3)

Jamiec
Jamiec

Reputation: 136074

If you want your results returned back to a DataTable, one option is to use the CopyToDataTable method.

Here's a live example: http://rextester.com/XHX48973

This method basically requires you to create a dummy table in order to use its NewRow method - the only way to create a DataRow, which is required by CopyToDataTable.

var result = dt.AsEnumerable()
    .GroupBy(p => new { 
           DispatcherName = p.Field<string>("DispatcherName"),
           Activity = p.Field<string>("ActivityType")})
    .Select(p => {
      var row = dummy.NewRow();
        row["Activity"] = p.Key.Activity;
        row["DispatcherName"] = p.Key.DispatcherName;
        row["Count"] = p.Count();
        return row;
    })
    .CopyToDataTable();

Perhaps a better way might be just fill in the rows directly, by converting to a List<T> and then using ForEach.

DataTable dummy = new DataTable();
dummy.Columns.Add("DispatcherName",typeof(string));
dummy.Columns.Add("Activity",typeof(string));
dummy.Columns.Add("Count",typeof(int));

dt.AsEnumerable()
    .GroupBy(p => new { DispatcherName = p.Field<string>("DispatcherName"),
        Activity = p.Field<string>("ActivityType")})
    .ToList()
    .ForEach(p => {
      var row = dummy.NewRow();
        row["Activity"] = p.Key.Activity;
        row["DispatcherName"] = p.Key.DispatcherName;
        row["Count"] = p.Count();
        dummy.Rows.Add(row);
    });

Live example: http://rextester.com/TFZNEO48009

Upvotes: 1

Adrian Iftode
Adrian Iftode

Reputation: 15663

from c in ACTIVITYLOG
group c by new {c.DispatcherName, c.ActivityType} into g
orderby g.Count() descending
select new { g.Key.DispatcherName, g.Key.ActivityType, Total = g.Count() }

Upvotes: 5

Petar Petkov
Petar Petkov

Reputation: 1479

This should do the trick:

 IList<ACTIVITYLOG> allActivityLogs;
            var result = (from c in allActivityLogs
                          select new
                          {
                              DispatcherName = c.DispatcherName,
                              ActivityType = c.ActivityType,
                              Total = c.ActivityType.Count
                          }).OrderByDescending(x => x.Total)
                         .GroupBy(x => new { x.DispatcherName, x.ActivityType });

You only need to substitute the allActivityLogs collection with the actual collection of your entities.

Upvotes: 0

Related Questions