Linq combining 2 queries

Question

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 is displayed in the basket, and each egg has a foreign key that is displayed in the basket.

I need to return a table containing these 3 columns:

Basket Name | Number of baskets in a basket | The number of eggs in the basket.

Each table is EF, and I use linq with VB.

So far, I have 2 queries: one that returns the Basket Name columns and the basket counter, and the other - the basket name and the number of eggs. How can I combine these two result tables to 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()

Thank you for your help.

Answer 1

:

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

cart.Baskets Ienumerable(of Baskets), SelectMany .

Answer 2

, Count .

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