How do I return data with a per type count using EF Core in ASP.NET MVC using C#? - Stack Overflow

admin管理员组

文章数量:1287966

I have a database of books. Books have a related genre. Two tables: Book and Genre.

How do I alter this:

var data = await _context.Genres.ToListAsync();

to return a count - along with the data - of how many books are in the database for each genre?

I have a database of books. Books have a related genre. Two tables: Book and Genre.

How do I alter this:

var data = await _context.Genres.ToListAsync();

to return a count - along with the data - of how many books are in the database for each genre?

• c#
• asp-mvc
• entity-framework-core
• ef-core-8.0

Share Improve this question Follow edited Feb 22 at 17:12 marc_s 755k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Feb 22 at 17:08 D.ManD.Man 31144 silver badges1717 bronze badges 0

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 4

This is an untested code:

var data = await _context.Books
    .GroupBy(b => p.Genre)
    .Select(g => new { name = g.Key.name, count = g.Count() })
    .ToListAsync();

Since I did not work with C# and .NET for many years, it's possible that this code has typos. Let me know if that is the case.

You can return a subquery using an anonymous type (or a real type if you prefer) containing both Genre and a count

var data = await _context.Genres
    .Select(g => new {
        Genre = g,
        BookCount = g.Books.Count(),
    })
    .ToListAsync();

If your relationship is only available in the reverse direction (which is wrong anyway), then you can do

var data = await _context.Books
    .GroupBy(b => b.Genre)
    .Select(g => new {
        Genre = g,
        BookCount = g.Count(),
    })
    .ToListAsync();

This produced the results I needed: -

var data = await _context.Books.Include(i => i.Genre)
    .GroupBy(b => b.Genre)
    .Select(g => new { name = g.Key, id = g.Key.Id, 
     description = g.Key.Description, 
     count = g.Count() })
    .ToListAsync(); 

本文标签： How do I return data with a per type count using EF Core in ASPNET MVC using CStack Overflow