CODEWITHSUNDEEP
c#linqsql-to-linq-conversion
blurfx
blurfx

Reputation: 1360

LINQ Left Join, Group By and Count

I'm trying to counting how many messages sent per hour. but my code returns wrong result

This is original sqlite query that I used, it works right.

SELECT Hours.hour, ifnull(count(Messages.hour),0) as count Hours LEFT JOIN Messages on Messages.hour = Hours.hour by Hours.hour

but I'm a new at LINQ and here is my code and query.

int[] Hours = { 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23 };

var sortByHour = from h in Hours
                 join m in Messages on h equals m.Hour into g
                 orderby h ascending
                 select new
                 {
                     Hour = h,
                     Total = g.Count()
                 };

and it returns

[0] { Hour = 0, Total = 33485 }
[1] { Hour = 1, Total = 0 }
[2] { Hour = 2, Total = 0 }
[3] { Hour = 3, Total = 0 }
...
[23] { Hour = 23, Total = 0 }

first data has number of total rows and others has 0. its wrong.

result should have to be like this

[0] { Hour = 0, Total = 501 }
[1] { Hour = 1, Total = 408 }
[2] { Hour = 2, Total = 181 }
[3] { Hour = 3, Total = 84 }
...
[23] { Hour = 23, Total = 1055 }

how can I fix my code? thanks in advanced.

Upvotes: 0

Views: 1266

Answers (2)

Ivan
Ivan

Reputation: 312

Answer is

var res = from h in Hours
          join m in Messages on h equals m.Hour into jn
          from j in jn.DefaultIfEmpty()
          select new
          {
              hour = h,
              count = j != null ? jn.Count(i => i.Hour == h) : 0
          }

But if you have Database, model, relation between Hours and Messages, and we are talking about linq to sql or lint to entities, it's better use (as usr mentioned at comments) h.Messages.Count() and let Database engine make a request

Upvotes: 3

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

Something like this should solve your problem.

Setup:

int[] Hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
var Messages = new List<Message>()
                {
                    new Message() { MessageId = 1, Hour = 5 },
                    new Message() { MessageId = 2, Hour = 7 },
                    new Message() { MessageId = 3, Hour = 5 },
                    new Message() { MessageId = 4, Hour = 3 },
                    new Message() { MessageId = 5, Hour = 7 },
                    new Message() { MessageId = 6, Hour = 5 },
                };

Query:

 var sortByHour = Hours.Select(h =>
    new { Hour = h, Total = Messages.Count(m => m.Hour == h) } );

Basically, you just select each hour and its count in Messages.

Upvotes: 0

Related Questions