Help with unexpected LINQ query results -

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 authors drop down list works expected. problem when try count number of items assigned each author counts every item author has in entire items table.

so example if author 1 has 10 books total, 2 of these books show in above query, still count 10 books. when bind same query control correct data, it's count operation won't work correctly.

to reiterate bound gridview , see 1 book per author, not of books author wrote. appears query should correct.

update: @jon skeet, wasn't able use let operator i'm conditionally building query. solved problem using original query count. here's how modified 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're using i.author.itemauthors.count() goes straight full table. suspect may want like:

var query = ia in db.itemauthors             let count = ia.categoryitems.count(t => t.categoryid == 10))             count > 0             select new             {                 authortext = string.format("{0} ({1})", i.author.text, count),                 authorid = i.author.authorid             }; 

on other hand, expect give 1 result per itemauthor isn't want. if want list of authors, i'd expect query starting in authors table:

var query = 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, each author, find out how many items have @ least 1 category id 10... , report many items.

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