7

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
    (select count(*) from restaurants) as restaurantsCount,
    (select count(*) from shops) as shopsCount,
    (select count(*) from products) as productsCount
from
    dual
alexmac
  • 19,087
  • 7
  • 58
  • 69

1 Answers1

7

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()
                     };
Community
  • 1
  • 1
Anatolii Gabuza
  • 6,184
  • 2
  • 36
  • 54
  • In your case, will be executed three queires, instead one. Each for every Count(). – alexmac Jan 17 '14 at 09:46
  • @Alexander You do have same in your `SQL`. If the only problem is to have *single query* which actually doesn't change the way data will be fetched - I'll update the answer. – Anatolii Gabuza Jan 17 '14 at 10:12
  • @AnatoliiGabuza is there no performance improvement by doing it in a single query? – Anton Toshik Aug 19 '18 at 20:41
  • In my case - the second solution (single query) was much slower - first it loaded all data and then count it.. instead of asking DB to just give counts (easy action) in separate queries – Yitzchak Apr 23 '20 at 03:29