linq combining 2 queries

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

Answer 1

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() 
                 };

Answer 2

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.