SQL Unions with table counts using EntityFramework LINQ query

Question

I am trying replicate the SQL below using LINQ and Entity Framework and cannot figure out how this should be written.

My simplistic LINQ version does a query per table

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

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.

This pure SQL UNION would only generate 1 SQL request, instead of a request per table.

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;

I saw a partial implementation [linq-group-by-multiple-tables] (https://stackoverflow.com/a/3435503/473923) but this is based of grouping data.

FYI: I'm new to C#/Linq, so sorry if this seams obvious.

So your `Context.AppUsers.Count()` doesn't return anything or got problem on view? I got to see that `Context.AppUsers.Count()` it returns total count with `key` and `value` pairs, please let me know if I misunderstood. — Md Farid Uddin Kiron, Sep 09 '21 at 07:06
Context.AppUsers.Count() returns the count (YES), but for 50 tables, I would have to call it 50 times, but with unions I can call it just once, I'm trying to do unions in LINQ — David Cruwys, Sep 09 '21 at 07:30
Check this my answer, it generates this query automatically: https://stackoverflow.com/a/66115862/10646316 — Svyatoslav Danyliv, Sep 09 '21 at 07:31

score 1 · Answer 1 · edited Sep 09 '21 at 15:03

There is nothing wrong with your LINQ query. It's very acceptable approach. However it's not the most efficient.

There is no need to fetch count from individual tables one by one. You can get the counts from all the tables at once using the System tables Sys.Objects and Sys.Partitions. Just try running this query in your database.

SELECT A.Name AS TableName, SUM(B.rows) AS RecordCount  
FROM sys.objects A INNER JOIN sys.partitions B 
ON A.object_id = B.object_id  
WHERE A.type = 'U' AND B.index_id IN (0, 1)
GROUP BY A.Name

For quick response and cleaner code, you can store this SQL query in a string variable, and run the LINQ

var result = dataContext.ExecuteQuery<YOUR_MODEL_CLASS>
             (your_string_query);

You want `AND B.index_id IN (0, 1)` otherwise you get the non-clustered indexes also. And you should consider using better aliases than `A` and `B` — Charlieface, Sep 09 '21 at 09:26

score 1 · Answer 2 · answered Sep 09 '21 at 09:26

Use the this code from my answer

And fill ExpandoObject with result:

var tablesinfo = Context.GetTablesInfo();
var expando = new ExpandoObject();
if (tablesinfo != null)
{
   var dic = (IDictionary<string, object>)expando;
   foreach(var info in tablesinfo)
   {
       dic.Add(info.TableName, info.RecordCount);
   }
}

Idea is that you can UNION counts if you group entities by constant. Schematically function builds the following IQueryable Expression:

var tablesinfo =
   Context.AppUsers.GroupBy(x => 1).Select(g => new TableInfo{ TableName = "asp_net_roles", RecordCount = g.Count() })
   .Concat(Context.MenuCategories.GroupBy(x => 1).Select(g => new TableInfo{ TableName = "menu_categories", RecordCount = g.Count() }))
   .Concat(Context.Items.GroupBy(x => 1).Select(g => new TableInfo{ TableName = "items", RecordCount = g.Count() }))
   ....

score 0 · Answer 3 · answered Sep 09 '21 at 07:05

0

I would put something like this:

Dictionary<string, int> view = new() {
   new() {'asp_net_roles', Context.AppUsers.Count() },
   ...
}
return View(view);

maybe not the most pure way, but does the job (unless I misunderstood what you try to accomplish)

answered Sep 09 '21 at 07:05

Felix

9,248
10
57
89

That is similar to my current way of doing and has the problem of generating a sql request for every table, e.g. 50 tables, 50 requrests. The example using pure SQL UNIONS would only generate 1 SQL request – David Cruwys Sep 09 '21 at 07:09
Oh, I see... There is `UNION` method, but it works on `IEnumerable`s, not `IQueryable`s – Felix Sep 09 '21 at 08:00

SQL Unions with table counts using EntityFramework LINQ query

3 Answers3