title: SQL Unions with table counts using EntityFramework LINQ query
body: <p>I am trying replicate the SQL below using LINQ and Entity Framework and cannot figure out how this should be written.</p>
  <blockquote>
  <p>My simplistic LINQ version does a query per table</p>
  </blockquote>
  <pre class="lang-cs prettyprint-override"><code>public IActionResult Index()
  {
        dynamic view = new ExpandoObject();
  
        view.AppUsers             = Context.AppUsers.Count();
        view.CustomerShops        = Context.CustomerShops.Count();
        view.FavouriteOrders      = Context.FavouriteOrders.Count();
        view.Items                = Context.Items.Count();
        view.ItemVariations       = Context.ItemVariations.Count();
        view.MenuCategories       = Context.MenuCategories.Count();
        view.MenuCategoryProducts = Context.MenuCategoryProducts.Count();
        view.Orders               = Context.Orders.Count();
        view.Products             = Context.Products.Count();
        view.ProductVariations    = Context.ProductVariations.Count();
        view.Shops                = Context.Shops.Count();
        view.Staffs               = Context.Staffs.Count();
  
        return View(view);
  }
  </code></pre>
  <p>I use this pattern from time to time to for reporting on my column counts and thought this should be easy to do in LINQ, but no luck so far.</p>
  <p>This pure SQL UNION would only generate 1 SQL request, instead of a request per table.</p>
  <pre class="lang-sql prettyprint-override"><code>select * from (
    select 'asp_net_roles' as type, count(*) from asp_net_roles
    union
    select 'asp_net_user_roles' as type, count(*) from asp_net_user_roles
    union
    select 'asp_net_users' as type, count(*) from asp_net_users
    union
    select 'app_users' as type, count(*) from app_users
    union
    select 'shops' as type, count(*) from shops
    union
    select 'staffs' as type, count(*) from shops
    union
    select 'items' as type, count(*) from items
    union
    select 'item_variations' as type, count(*) from item_variations
    union
    select 'products' as type, count(*) from products
    union
    select 'product_variations' as type, count(*) from product_variations
    union
    select 'menu_categories' as type, count(*) from menu_categories
  ) as counters
  order by 1;
  </code></pre>
  <p>I saw a partial implementation [linq-group-by-multiple-tables] (<a href="https://stackoverflow.com/a/3435503/473923">https://stackoverflow.com/a/3435503/473923</a>) but this is based of grouping data.</p>
  <p>FYI: I'm new to C#/Linq, so sorry if this seams obvious.</p>
  <p><a href="https://i.stack.imgur.com/YJ5wj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YJ5wj.png" alt="enter image description here" /></a></p>