0

What did I miss? I have the following Linq on C#:

var topfive = (from a in _db.ActivityRecords
                       join b in _db.ActivityRecordProgresses on a.ActivityRecordId equals b.ActivityRecordId into leftjoin
                       from c in leftjoin.DefaultIfEmpty()
                       join d in _db.Customers on a.CustomerId equals d.CustomerId
                       group c by a.CustomerId into grouped
                       select new { 
                           label = grouped.Key,
                           datas = grouped.Count() 
                       }
            ).ToList();

The result is:

{"tfive":[{"label":2,"datas":5},{"label":4,"datas":1},{"label":140,"datas":1}]}

If I swap the a and b. The result become:

{"tfive":[{"label":2,"datas":5},{"label":140,"datas":1}]}

It should be 3 records:

`{"tfive":[{"label":2,"datas":5},{"label":4,"datas":1},{"label":140,"datas":1}]}`

The data is different with my sql script:

select      COUNT(a.ActivityRecordId) cTotal, c.CustomerName 
from        ActivityRecord a 
            left join ActivityRecordProgress b on a.ActivityRecordId = b.ActivityRecordId
            inner join Customer c on a.CustomerId = c.CustomerId
group by    c.CustomerName
order by    cTotal DESC

It should be:

{"tfive":[{"label":CustomerA,"datas":5},{"label":CustomerB,"datas":1},{"label":CustomerC,"datas":1}]}

What did I wrong? Advice please.
Thank you.

Haminteu
  • 1,292
  • 4
  • 23
  • 49
  • Can you please try with `group d by a.CustomerId into grouped`. Replace `c` with `d`. – Karan Sep 14 '21 at 04:23
  • @Karan, Yes. It works for the data label. It has been changed to `CustomerName`. But the result is only 2 records. It should be 3 records. I think its because the `CountTotal` is the same. The result now is `{"topfive":[{"label":"CustomerA","datas":5},{"label":"CustomerB","datas":1}]}` – Haminteu Sep 14 '21 at 04:26
  • What does "If I swap the `a` and `b`" mean? – NetMage Sep 15 '21 at 19:57
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Sep 15 '21 at 19:58

0 Answers0