CODEWITHSUNDEEP
c#sqlasp.netentity-frameworklinq
Liron Harel
Liron Harel

Reputation: 11247

Get results matching a list of Ids using Linq

I am trying to get the list of gameIds that satisfy all the genreIds included in a a List<int>.

The tables (partial):

editorial_list:

game_genres (game can belong to several genres):

I need to get the list of the game Ids of games that exists for all the genre_id's in the game_genres table.

For example:

The list of genre_id's includes genre 2 and 3.

If Game Id = 14 exists in game_genres table for both genre_id = 2 and genre_id = 3. So it will be included in the final results.

Here is my code:

 // get the list of game_id's that have an editorial
 var editorialList = (from ee in db.editorials where ee.is_enabled == true select new {
     game_id = ee.game_id
 }).ToList();

// Produce a list of the games in the editorials and their genre Ids that the belong to
 var gameAndGenres = (from el in editorialList join gg in db.game_genres
  on el.game_id equals gg.game_id

     select new {
         game_id = el.game_id,
             genre_id = gg.genre_id
     }
 );

 var res = gameAndGenres.Where(
  x => x.genres.Contains(x.genre_id)) == genres.Count; // stuck here

The end results should be a unique list of game_id's, that each game in the list belongs to all the genres that was listed in the genres List<int>.

I created several steps to help me understand the query, but it might be able to be solved in one line, I just wasn't able to solve it.

Update: This is a new code that I'm trying.

 var res = gameAndGenres.GroupBy(x => x.game_id)
  .Select(g => new {
   game_id = g.Key,
   genreIds = g.Select( c => c.genre_id)

   });

   var res2 = res.Where(x => genres.Intersect(x.genreIds).Count()
  == genres.Count()).ToList();

Upvotes: 1

Views: 3061

Answers (3)

Daniel Stackenland
Daniel Stackenland

Reputation: 3239

Something like this:

        var gamesIds = db.editorials
            .Where(e => db.game_genres.Select(gg => gg.genre_id).Distinct().All(genId => db.game_genres.Any(gg => gg.game_id == e.game_id && gg.genre_id == genId)))
            .Select(e => e.game_id)
            .ToList();

Select game_id from editorials, where the game_id have an entry of all distinct genre_ids in the game_genre table.

If you want all games having all genres in a list instead of all in the table:

List<int> genreIds = new List<int>() {1,2,3};

    var gamesIds = db.editorials
        .Where(e => genreIds.All(genId => db.game_genres.Any(gg => gg.game_id == e.game_id && gg.genre_id == genId)))
        .Select(e => e.game_id)
        .ToList();

Upvotes: 2

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

The relation between Game and Genre is a many-to-many: a Game can belong to zero or more genres and a Genre can have zero or more Games.

You want (the IDs of) all games that belong to all Genres in your game-genres table.

For example, if your list game-genres contains only records with references to genre 2 and genre 3 then you want all games that belong to genres 2 and 3.

Note that a Genre may exist that is not owned by any 'Game'. In that case there is no record in the game-genres table with a reference to this Genre.

Below an example of your Property Entity Framework classes. The actual names of the classes and properties may vary, but you'll get the Id

public class Game
{
    public int GameId {get; set;}
    public virtual ICollection<Genre> Genres {get; set;}
    ... // other properties
}
public class Genre
{
    public int GenreId {get; set;
    public virtual ICollection<Game> Games {get; set;}
    ...
}
public MyDbContext : DbContext
{
    public DbSet<Game> Games {get; set;}
    public DbSet<Genre> Genres {get; set;}
    ...
}

The entity framework model builder will detect that there is a many-to-many relation between Games and Genres and will automatically add a table like your 'game-genres'.

The nice thing, is that if a certain Genre does not belong to any Game, it won't be in your game_genres table. Also the other way round: if you have an element in your game-genres table, than there is at least one game that belongs to that genre.

So you don't want all genres, you only want genres that are used by at least one Game.

IEnumerable<Genre> usedGenres = dbContext.Genres
    .Where(genre => genre.Games.Any());

Now you want only those Games that belong to EVERY genre in usedGenres = every game where every element of usedGenres is in the collection Game.Genres.

To check if a genre is in the collection of Game.Genres, we only have to compare the GenreId of Game.Genres with the GenreId of usedGenreIds

IEnumerable<int> usedGenreIds = usedGenres
    .Select(genre => genre.GenreId);
IEnumerable<Game> gamesWithAllUsedGenres = dbContext.Games
    .Where(game => usedGenreIds.All(genreId => game.Genres.Select(genre => genre.GenreIdContains(genreId));

Upvotes: 2

Michal Nawrocik
Michal Nawrocik

Reputation: 88

You can achieve it by groupping rows from your tables.

Using Lambda expressions:

var res = db.game_genres.GroupBy(gg => gg.game_id)
    .Where(g => g.Count() == db.genres.Count())
    .Select(x => x.Key).ToList();

or using LINQ:

 var res = (from gg in db.game_genres
             group gg by gg.game_id into g
             where g.Count() == db.genres.Count()
             select g.Key).ToList();

Such groupping produces one record (called group) per game_id. The Key of each group is the game_id used in the "group by" clause. Each group is a collection of rows having equal game_id, therefore you can treat it like any other collection of database entities, here you filter it using where statement and call Count() on the collection.

Upvotes: 0

Related Questions