linq count with join on multiple tables

Question

Let's say I have 3 tables: carts, baskets and eggs where a basket can contain many eggs and where carts contain many baskets. Each basket has a foreign key that maps to a cart and each egg has a foreign key that maps to a basket.

I need to return a table that contains these columns:

-Cart Name -Number of Baskets in Cart -Number of Eggs in cart.

Each table is an EF and I'm using linq. It's a combination of joins and counts and I'm moving in circles.

Thanks for your help.

Answer 1

I am a LINQ-to-SQL man myself, but I believe EF auto-generates one-to-many properties as IEnumerables, so this should work for you:

from cart in Carts
select new
{
    CartName = cart.Name,
    BasketCount = cart.Baskets.Count(),
    EggCount = cart.Baskets.SelectMany(b => b.Eggs).Count()
}