Linq query optimization/summarize-CodePudding

I have the following query:

var countA=await _importContext.table1.CountAsync(ssc => ssc.ImportId == importId)
var countB=await _importContext.table2.CountAsync(ssc => ssc.ImportId == importId)
var countC=await _importContext.table3.CountAsync(ssc => ssc.ImportId == importId)
var countD=await _importContext.table4.CountAsync(ssc => ssc.ImportId == importId)

There are 9 more count from different tables. Is there a way to summarize the query in terms of optimizing & removing redundancy? I tried wrapping up the queries like:

var result = new 
{ 
    countA = context.table1.Count(),
    countB = context.table2.Count(),
.....
};

but this takes more time than the first one above.

CodePudding user response：

You can't really optimise it as you seem to need the counts from all of the tables. Your second method of getting the data still calls the database the same amount of times as the first but also creates an object containing all of the counts so is likely to take longer.

The only thing you can really do to make it faster is to get the data in parallel but this might be overkill. I would just go with your first option unless it's really slow.

CodePudding user response：

You can create such query via gouping by constant and Concat operator:

Helper class:

public class TableResult
{
      public string Name { get; set; }
      public int Count { get; set; }
}

Query:

var query =   _importContext.table1.Where(ssc.ImportId == importId).GroupBy(x => 1).Select(g => new TableResult { Name = "table1", Count = g.Count() })
      .Concat(_importContext.table2.Where(ssc.ImportId == importId).GroupBy(x => 1).Select(g => new TableResult { Name = "table2", Count = g.Count() }))
      .Concat(_importContext.table3.Where(ssc.ImportId == importId).GroupBy(x => 1).Select(g => new TableResult { Name = "table3", Count = g.Count() }))
      .Concat(_importContext.table4.Where(ssc.ImportId == importId).GroupBy(x => 1).Select(g => new TableResult { Name = "table4", Count = g.Count() }));

var result = await query.ToListAsync();