Condensing record count using EF

Question

0.00/5 (No votes)

See more:

I have a code block that works just fine, but it is troubling me by looking at it, there has to be a more simplified way, or cleaner way, to get the count of records in a database. I want to build a dashboard with about 10 record sums and I don't want to go down a path if there is a more simplistic code approach. Any thoughts welcome!

What I have tried:

{
      private readonly ApplicationDbContext _context;
      public int blogCount;
      public IList<Model.Blog> Blog { get; set; };

      public IndexModel(ApplicationDbContext context)
      {
          _context = context;
      }

      public async Task OnGetAsync()
      {
          if (_context.Blog != null)
          {
              Blog = await _context.Blog
                  .ToListAsync();
              blogCount = Blog.Count();
              ViewData["BlogCount"] = blogCount.ToString();
          }

      }
  }

Posted 24-Nov-22 7:11am

Member 15825061

Updated 24-Nov-22 23:12pm

Add a Solution

3 solutions

Solution 1

Your query is telling EF to get all of the records from the Blog table, not count them. That's what the .ToListAsync call is doing.

You didn't say which version of EF you're using, so this is just one possibility. If you want just the count, you can execute a raw SQL query instead:

var count = _context.Database.FromSql($"SELECT COUNT(fieldName) FROM Blog").ToList();

Posted 24-Nov-22 14:56pm

Dave Kreskowiak

Comments

Dave Kreskowiak 25-Nov-22 11:31am

For EF6, it's this:

    List<int> counts = _context.Database.SqlQuery<int>("SELECT COUNT(*) FROM Blog");

The documentation on it: https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.sqlquery?view=entity-framework-6.2.0

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2022-11-24T23:12:00

Solution 3

If you just need to count the records rather than loading them:

C#

int count = await _context.Blog.CountAsync();

EntityFrameworkQueryableExtensions.CountAsync Method (Microsoft.EntityFrameworkCore) | Microsoft Learn[^]

That method has been supported since EF Core v1.0.

Posted 24-Nov-22 23:12pm

Richard Deeming

Comments

Member 15825061 25-Nov-22 13:01pm

Works perfect, thanks!

Member 15825061 · Accepted Answer · 2022-11-24T16:36:00

EF 6.0.10 is current version used

var count = _context.Blog.FromSqlRaw($"SELECT COUNT(bId) FROM Blog").ToList();
                ViewData["BlogCount"] = blogCount.ToString();

The sql works fine when I run query in SSMS, but I am getting an error

when running program:

Quote:
InvalidOperationException: The required column 'BlogID' was not present in the results of a 'FromSql' operation

Condensing record count using EF

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Condensing record count using EF

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us