How to group by Games and select each game's list of Genres

Question

I am trying to create a view for a many-to-many ViewModel GameGenre but I am unsure how to design the mvc to select all games and group by games but show all genres for each game.

I was looking how to achieve my purpose through LINQ and group-by but most answers that I found were using it for one model.

Also, I do not feel confident with the view model that I have right now:

    public class GameStoreViewModel
    {
        [Key]
        public Guid GameId { get;}
        public string GameName { get;}
        public Genre Genre { get; set; }
        public IEnumerable<Genre> GenreList { get; set; }
    }

These are my models:

    public class Game
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    public class Genre
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    public class GameGenre
    {
        public Guid Id { get; set; }
        public Guid GameId { get; set; }
        public Guid GenreId { get; set; }
        public Game Game { get; set; }
        public Genre Genre { get; set; }
    }

The basic query to run on SQL is:

SELECT ga.Id, ga.Name, ge.Name FROM Game ga
LEFT JOIN GameGenre gg ON ga.Id = gg.GameId
LEFT JOIN Genre ge ON ge.Id = gg.GenreId;

The result

But that results in games being repeated multiple times which will not look good for a front store. So, I would like to make the view to get each game with all its genres like

I am more concerned about the view model and the controller code than the html view.

Apologies if my explanation is not clear, please let me know. I appreciate any assistance and thank you in advance!

daremachine · Answer 1 · 2019-11-03T03:23:40.157

You want this?

public class Game
{
    public string GameId { get; set; }
    public string GenreId { get; set; }
}

var list = new List<Game>{
    new Game { GameId = "Final Fantasy VII", GenreId = "RPG" },
    new Game { GameId = "Dark Souls", GenreId = "RPG" },
    new Game { GameId = "Dark Souls", GenreId = "Action" },
    new Game { GameId = "World of Warcraft", GenreId = "MMORPG" },
    new Game { GameId = "Resident Evil 2", GenreId = "Survival" },
    new Game { GameId = "Resident Evil 2", GenreId = "Horror" }
};

var groupedList = list.GroupBy(
    g => g.GameId,
    g => g.GenreId,
    (key, group) => new
        {
            GameId = key,
            Genres = group.ToList()
        })
     .ToList();

How to group by Games and select each game's list of Genres

1 Answers1