linq combining 2 queries

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 3 columns:

Cart Name | Count of Baskets in Cart | Count of Eggs in Cart.

Each table is an EF and I'm using linq with VB.

So far, I have 2 queries: one that returns the columns Cart Name and Count of Basket and another one that returns Cart Name and Count of Eggs. How can I combine these two result tables so that I get the results in one table?

           Dim query1 = (From cart In myEntities.Carts
           Where cart.UserID = TheUserID
           Join baskets In my开发者_运维问答Entities.Baskets On baskets.CartID Equals cart.CartID
           Select cart.CartName, cart.baskets.Count()).Distinct()

           Dim query2 = (From cart In myEntities.Carts
           Where cart.UserID = TheUserID
           Join baskets In myEntities.Baskets On baskets.CartID Equals cart.CartID
           Select cart.CartName, baskets.Eggs.Count()).Distinct()

Thanks for your help.

The below query gives the desired result:

from cart in Carts _
select cart.CartName, BasketsInCart = cart.Baskets.Count(), EggsInCart = cart.Baskets.SelectMany(Function(basket)basket.Eggs).Count()

Because cart.Baskets is an Ienumerable(of Baskets) , the SelectMany operator can be used to get all the eggs from all the baskets.

If you have the relationship defined, you could just use the Count method on the nested entities.

var cartCounts = from c in myEntities.Carts
                 select new 
                 { 
                     c.CartName, 
                     BasketCount = c.Baskets.Count(),
                     EggCount = c.Baskets.Eggs.Count() 
                 };