SQL Unions with table counts using EntityFramework LINQ query-CodePudding

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] (

CodePudding user response：

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

CodePudding user response：

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)

CodePudding user response：

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() }))
   ....