Help with unexpected LINQ query results

Question

I have these 4 tables: enter image description here

This query

 var query = db.Authors .Where(x=> x.ItemAuthors .Any(z=> z.Item.CategoryItems .Any(b=> b.categoryID == 10))) .Select(ci=> new { authorText = string.Format("{0} ({1})", ci.text, ci.ItemAuthors.Count()), authorId = ci.authorID });

Which populates an Authors drop down list which works as expected. The problem is when I try to count the number of items assigned to each author it counts every item the author has in the entire items table.

So for example if author one has 10 books total, but only 2 of these books show up in the above query, I still get a count for 10 books. When I bind that same query to a control I get back the correct data, it's just the count operation that won't work correctly.

To reiterate I bound it to a gridview and I only see one book per author, not all of the books the author wrote. So it appears my query should be correct.

Update: @Jon Skeet, I wasn't able to use the let operator as I'm conditionally building my query. I solved my problem by using the original query to get the count. Here's how I modified my original Count() syntax:

var query = authors2.Select(x => new { authorText = string.Format("{0} ({1})",x.text, x.ItemAuthors .Where(qq=> qq.Item.CategoryItems .Any(xt=> xt.categoryID == 10)) .Count()), authorId = x.authorID });

You haven't shown the code where you're trying to do the count... please show the code which doesn't work. — Jon Skeet
– Jon Skeet, Commented Apr 12, 2011 at 6:38
It's in my select new statement, via authorText you have to scroll over. — The Muffin Man
– The Muffin Man, Commented Apr 12, 2011 at 6:39
Whoops - I hadn't seen it because of the scrolling. It's worth formatting so that we don't have to do that... answering now. — Jon Skeet
– Jon Skeet, Commented Apr 12, 2011 at 6:41

Jon Skeet · Accepted Answer · 2011-04-12 06:49:09Z

You're using i.Author.ItemAuthors.Count() which goes straight back to the full table. I suspect you may want something like:

var query = from ia in db.ItemAuthors let count = ia.CategoryItems.Count(t => t.categoryID == 10)) where count > 0 select new { authorText = string.Format("{0} ({1})", i.Author.text, count), authorId = i.Author.authorID };

On the other hand, I would expect that to give one result per ItemAuthor which isn't really what you want. If you want a list of authors, I'd expect a query starting in the Authors table:

var query = from author in db.ItemAuthors let count = author.Items .Count(ia => ia.Item.CategoryItems .Any(ci => ci.CategoryID == 10)) select new { authorText = string.Format("{0} ({1})", author.text, count), authorId = author.authorID };

In other words, for each author, find out how many items have at least one category with ID 10... and then report that many items.

It's somewhat complex because of the various 1-many relationships (each book having potentially multiple authors and multiple categories).

It's saying author.Itemsdoesn't contain a definition for count.
I believe it should be Item which is the items table class. I've updated my code in my question to reflect the authors table.
@Nick: What you haven't shown is what all the relationships between the classes are. You've shown the tables, but not what the relations are in the entities. But hopefully I've given you enough of a start to figure the rest out yourself.
I updated my question to reflect how I solved the problem. I will however accept your answer as it got me on the right track. Thanks.

Collectives™ on Stack Overflow

Help with unexpected LINQ query results

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related