CODEWITHSUNDEEP
c#asp.net-mvcentity-framework-coreef-core-8.0
D.Man
D.Man

Reputation: 311

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

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?

Upvotes: 2

Views: 87

Answers (3)

Charlieface
Charlieface

Reputation: 72194

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

Upvotes: 2

D.Man
D.Man

Reputation: 311

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

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76943

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.

Upvotes: 4

Related Questions