Help with unexpected LINQ query results

Question

I have these 4 tables:

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

            });

Answer 1

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).