1

For an admin page I need to provide several tables' total counts.

This is my current code:

[HttpGet("stats")]
public async Task<StatsVM> GetStats()
{
    var result = new StatsVM();

    result.TotalTable1 = await dbContext.Table1.CountAsync(...);
    result.TotalTable2 = await dbContext.Table2.CountAsync(...);
    result.TotalTable3 = await dbContext.Table3.CountAsync(...);
    result.TotalTable4 = await dbContext.Table4.CountAsync(...);

    return result;
}

To my understandings this will result in four DB trips (and there will be more in future). Is there a way to do it in one DB trip?

Update:

Will the following code result in one DB trip? The query on Table1 could be any non-empty table.

var result = await dbContext.Table1
    .AsNoTracking()
    .Select(c => new StatsVM
    {
        TotalTable1 = dbContext.Table1.Count(),
        TotalTable2 = dbContext.Table2.Count(),
        //...
    })
    .FirstAsync();
Mara09
  • 65
  • 6
  • I don't think standard EF supports this. Every Count is a database call. I think this is one of the rare cases where a stored procedure or raw sql call would help. But this is just a hunch. Maybe EF has a trick up its sleeve. – JHBonarius Jan 29 '22 at 13:10
  • Raw ADO.NET or Dapper for sugar coating - not EF – ErikEJ Jan 29 '22 at 15:20
  • Probably this [solution may help](https://stackoverflow.com/questions/66112074/how-to-loop-through-dbcontext-all-dbset-in-entity-framework-core-to-get-count/66115862#66115862) – Svyatoslav Danyliv Jan 29 '22 at 20:48
  • I have a piece of code that I want to know if it solves the problem. Should I update this question or ask a new one? – Mara09 Jan 30 '22 at 07:33
  • Well, I don't know if the code works. It's a question that if such code results in one DB trip. – Mara09 Jan 30 '22 at 19:18
  • You can add the code to your question, it won't invalidate the current answer. – Gert Arnold Feb 01 '22 at 21:06
  • You can monitor executed SQL right? Shouldn't be hard to find out how many roundtrips there are. – Gert Arnold Feb 02 '22 at 07:46
  • Could you please provide a link? – Mara09 Feb 02 '22 at 08:11

1 Answers1

0

You can map an entity type to a stored procedure that returns all the counts as a single row.

pjs
  • 363
  • 1
  • 7