Unexpected results from IEnumerable.Count not matching the actual count with Anonymous return type.
Hello,
My apologies in advance for the length of the following post. It seems like a pretty simple question, but as a support guy myself, I hate it when people ask a question but provide no details or context to help me understand what's going on, so I've tried to illustrate the issue below clearly. To err on the side of verbosity, if you will.
I am a bit baffled, and hope I haven't just overlooked something silly. I have a C# query that returns records from an SQL database. Sometimes I need to drill down into one-to-many linked records, which is simple by simply clicking the green hyperlink the the result column. However, I also want to be able to see at a glance how many of those linked records there are for each row in the returned data. So, my Select returns an anonymous type with the relevant fields, including both the collection property of the linked data, as well as the Count of that property.
The problem is, the Count property returns a value that is not consistent with the number of items in the property being counted. Here's an example - the actual data I'm working with is a little bit customer-specific, so I'll use different object names below to better illustrate my issue. Let's say I want to find duplicate customer records, so I group results by company name, to see how many orders each has:
The problem is, the number displayed for OrderCount is not the same as the number of rows displayed when I click the "Orders" link to see the orders for the customer in question. For example, for customer 1234 above, the Orders shows 2, but clicking the Orders link may return 14 records.
So, why would the Orders.Count() function return a different value than the number of rows in Orders?
My apologies in advance for the length of the following post. It seems like a pretty simple question, but as a support guy myself, I hate it when people ask a question but provide no details or context to help me understand what's going on, so I've tried to illustrate the issue below clearly. To err on the side of verbosity, if you will.
I am a bit baffled, and hope I haven't just overlooked something silly. I have a C# query that returns records from an SQL database. Sometimes I need to drill down into one-to-many linked records, which is simple by simply clicking the green hyperlink the the result column. However, I also want to be able to see at a glance how many of those linked records there are for each row in the returned data. So, my Select returns an anonymous type with the relevant fields, including both the collection property of the linked data, as well as the Count of that property.
The problem is, the Count property returns a value that is not consistent with the number of items in the property being counted. Here's an example - the actual data I'm working with is a little bit customer-specific, so I'll use different object names below to better illustrate my issue. Let's say I want to find duplicate customer records, so I group results by company name, to see how many orders each has:
Customers
.GroupBy(cust => cust.CompanyName) // Group by company name
.Where(g => g.Count() > 1) // Only get records where there are duplicate CompanyNames
.SelectMany(g => g) // Flatten results for display
.Select(cust => new {
CustomerID = cust.CustomerID,
CompanyName = cust.CompanyName,
OrderCount = cust.Orders.Count(), // Counts the Orders records
Orders = cust.Orders // The Orders records
})
.OrderBy(cust => cust.CompanyName)
The results would let me see duplicated CompanyName records, how many orders each duplicate had, and then drill down into them by clicking the Orders hyperlink. This aids me in figuring out which record to keep, and which one I should merge into the primary. For example, if one "Acme, Inc." has 300 orders and one has 0 orders, I know it's likely the 0-order one can be deleted. Example output:
From this, I can see that Big Customer has one account with 341 orders, and one account with 0 orders, making it clear which is the "real" and which is the accidental duplicate. (Obviously, there are other factors that would be taken into account as well, but I'm keeping this simple for illustrative purposes. Also, for those of you cringing at allowing a duplicated company name field in the db, I know - I feel your pain! The actual schema being worked with is actually worse than that. But, we didn't design this, it's just the customer system we have to work with, so here we are.)
CustomerID | CompanyName | OrderCount | Orders
-----------+--------------+------------+-------
1234 | Acme, Inc. | 2 | Orders
-----------+--------------+------------+-------
2345 | Acme, Inc. | 5 | Orders
-----------+--------------+------------+-------
3456 | Big Customer | 341 | Orders
-----------+--------------+------------+-------
5678 | Big Customer | 0 | Orders
The problem is, the number displayed for OrderCount is not the same as the number of rows displayed when I click the "Orders" link to see the orders for the customer in question. For example, for customer 1234 above, the Orders shows 2, but clicking the Orders link may return 14 records.
So, why would the Orders.Count() function return a different value than the number of rows in Orders?