linq group by and count

Question

select  uc.adminid, count(*)
from Users uc
join UsersMessage ucm on uc.admincallid = ucm.admincallid
where uc.CallDate between '2016-08-01' and '2016-09-01' and ucm.type = 4
group by uc.adminid
order by count(*)

And here is what I've tried:

 public static Dictionary<int, int> ReturnSomething(int month)
        {

            Dictionary<int, int> dict = new Dictionary<int, int>();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                LinqMetaData meta = new LinqMetaData(adapter);
                dict = (from uc in meta.Users
                        join ucm in meta.meta.UsersMessage on uc.AdminCallId equals ucm.AdminCallId 
                        where ucm.type == 4 && uc.CallDate.Month == month
                        group uc by uc.AdminId into g
                        select new { /* ???? adminid = g. */ }).ToDictionary(x => new Dictionary<int, int>(/* ????? x, x.Name*/));
            }

            return dict;
        }

how can I achieve what I need?

you need `g.Key` and `g.Count()`. Have look at [this question](http://stackoverflow.com/questions/7285714/linq-with-groupby-and-count). — Adil Mammadov, Sep 02 '16 at 07:52
Check the documentation for group by result (`IGrouping`). For instance, you have `g.Key`, `g.Count()` etc. — Ivan Stoev, Sep 02 '16 at 07:52

score 2 · Accepted Answer · edited May 23 '17 at 12:01

2

The key of the dictionary is the key of the GroupBy and the value is the Count(), so you need:

// ...
.ToDictionary(g => g.Key, g => g.Count()); // key is AdminCallId and value how often this Id occured

Since you've asked how to order it decending:

You are building a dictionary which has no order(well, It should read: it is non-deterministic). So the ordering is completely unnecessary. Why it's unordered? Read this

But if you would create something else and you wanted to know how to order by Count DESC you could use this:

from uc in meta.Users
join ucm in meta.meta.UsersMessage on uc.AdminCallId equals ucm.AdminCallId 
where ucm.type == 4 && uc.CallDate.Month == month
group uc by uc.AdminId into g
orderby g.Count() descending
select new { adminid = g.Key, count = g.Count() })

edited May 23 '17 at 12:01

Community

1
1

answered Sep 02 '16 at 08:05

Tim Schmelter

450,073
74
686
939

Yep, it works. Btw, if I want to order descending by Count(), how to change my static function? – Florin M. Sep 02 '16 at 08:09
@FlorinM.: you are building a dictionary which has no order(well, It should read: it is non-deterministic). So the ordering is completely unnecessary. Why it's unordered? [Read this](http://stackoverflow.com/questions/6384710/why-is-a-dictionary-not-ordered) – Tim Schmelter Sep 02 '16 at 08:20
What if I do want to count only those entries with some distinct field value ( eg.: LinkedId ) ? I did try something like this but it doesn't work as expecteD: select new { adminid = g.Key, count = g.Select(x => x.LinkedId).Distinct().Count() } – Florin M. Sep 02 '16 at 11:10
@FlorinM.: what means it did not work? YOu want to count distinct ID's or you want only entries which have unique `LinkedId`? In the former case your apporach should work. – Tim Schmelter Sep 02 '16 at 11:16

linq group by and count

1 Answers1