Select records count from multiple tables in a single query


I have some models (restaurants, shops, products), and i want to select records count for multiple models in a single linq query.

I know how it should be in sql, but i don't know how to translate it in linq:

    (select count(*) from restaurants) as restaurantsCount,
    (select count(*) from shops) as shopsCount,
    (select count(*) from products) as productsCount

Accepted Answer

Considering dual is a dummy table with single row:

var result = new 
    RestaurantsCount = context.Restaurants.Count(),
    ShopsCount = context.Shops.Count(),
    ProductsCount = context.Products.Count()

Single query solution:

        var result = from dummyRow in new List<string> { "X" }
                     join product in context.products on 1 equals 1 into pg
                     join shop in context.shops on 1 equals 1 into sg
                     join restaurant in context.restaurants on 1 equals 1 into rg
                     select new
                         productsCount = pg.Count(),
                         shopsCount = sg.Count(),
                         restaurantsCount = rg.Count()

Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why