Florin M. Florin M. - 3 months ago 12
ASP.NET (C#) Question

linq group by and count

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?

Answer

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() })