Selecting most popular Foo by count of Bars

Question

Restated question:
I have foo.
Foo has many fizzs.
Fizz has Foo.
Bar has one fizz.

I want to find foo, which has fizz with the highest count of bars that reference it using LINQ .

It would be perfect, if linq would be NHibernate.Linq compatible. But that's already a different story.

Old question:
I know - question is simple. I've always been struggling with sql selects.

I have foo.
Foo has many fizzs.
Fizzs have many bars.

I want to find foo, which has fizz with the highest count of bars using LINQ .

P.s. sorry for asking to do my job.

Edit:
damn... it's different. Bar has fizz, fizz does not have bars. :/

Answer 1

This is one way to do it:

 var x = (from f in foos
         orderby f.fizzs.SelectMany(fi => fi.bars).Count() descending
         select f).First();

Edit: fixed query not returning correct results.

Answer 2

var foo = (from f in foos
           from fz in f.Fizzs
           let bCount = fz.Bars.Count()
           orderby bCount descending
           select f).First();

This is not necessarily terribly efficient, although it would help a little if Bars is an ICollection<Bar>, since that would allow you to use the Count property instead of the Count extension method.

In this example, however, I assumed that Bars was an IEnumerable<Bar>.

Answer 3

var orderedByMaxNumOfBars =
    from foo in foos
    let maxNumOfBars = foo.Fizzes.Select(fizz => fizz.Bars.Count()).Max()
    orderby maxNumOfBars descending
    select foo;

var fooWithFizzWithMostBars = orderedByMaxNumOfBars.FirstOrDefault();