Group and Count in Linq issue

Question

I am working on a C# project using LINQToSQL,But right now I am having a problem with the following query:

var groupByQuery =  (from c in db.Customers
                            join o in db.Orders on c.CustomerID equals o.CustomerID into lf1
                            from or in lf1.DefaultIfEmpty()
                            group c by c.CustomerID into g
                            select new
                            {
                                CustomerID = g.Key,
                                Count = g.Count()
                            }
                            ).OrderBy(x => x.Count);

As you can see I am doing a LEFT OUTER JOIN and grouping by the CustomerID, and everything goes well so far. But when I see the results I realize that the Customers that does not have any Order, has a "1" in their Count field.

Why is that? The Count field is supposed to have a "0" in these cases, What am I doing wrong?

I found this questions here:

linq-count-query-returns-a-1-instead-of-a-0

linq-to-sql-joining-query-returning-1-instead-of-0

but none of them has been helpful to me, I hope someone can help, thank you in advance.

score 1 · Answer 1 · answered Sep 24 '12 at 22:35

1

There will still be a record, even if it's null - a set that contains "NULL" is still a row - that's why it's saying there is 1 record. If you give the Count method an argument for what records to count, it should work better.

Count = g.Count(a => a.SomeNullableField != null)

answered Sep 24 '12 at 22:35

eouw0o83hf

9,438
5
53
75

Thank you for your reply. But that's just my problem: from the `.Count()` method I cannot access any field from `Orders` table which stops me from knowing whether it has any Orders or not – Dante Sep 24 '12 at 22:40
Ah - group `or` by CustomerID instead of `lf1` to access the joined-in table. – eouw0o83hf Sep 24 '12 at 22:43

score 1 · Answer 2 · answered Sep 25 '12 at 01:19

You're making things too complicated. join...into performs a group join, so the Orders are grouped by CustomerId within your first two lines of code. This should work:

var groupByQuery =  (from c in db.Customers
                     join o in db.Orders on c.CustomerID equals o.CustomerID into lf1
                     select new
                     {
                         CustomerID = c.Id,
                         Count = lf1.Count()
                     }
                     ).OrderBy(x => x.Count);

Group and Count in Linq issue

2 Answers2

Linked