CODEWITHSUNDEEP
c#linqentity-frameworkentityentity-relationship
Aaron Davis
Aaron Davis

Reputation: 281

Linq to Entities Where In Clause Many to Many

I am trying to get a Where In clause to work with linq to entities and have run into problems. My issue is that I have several lists of various entities and I would like to select a set of Game entities off of these lists essentially setting up a SQL WHERE IN clause for them. The issue is that the lists of entities are being compared to the game entity's related entities which have a many to many relationship. I have done a lot of research and tried a bunch of solutions to solve this but just cannot get it quite right, I would really appreciate some help from the stackoverflow community.

Thanks in advance.

Edit:

I have tried doing this instead of the unions: 

     var relatedGames = from game in ugdb.Games
                        where game.Developers.Any(d => relatedDevelopers.Any(r => r.DeveloperID == d.DeveloperID))
                        || game.Publishers.Any(d => relatedPublishers.Any(r => r.PublisherID == d.PublisherID))
                        || game.Genres.Any(d => relatedGenres.Any(r => r.GenreID == d.GenreID))
                        select game;

With both the unions and this I recieve the following error message:

base {System.SystemException} = {"Unable to create a constant value of type 'UltimateGameDB.Domain.Entities.Developer'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}

Does anyone have any idea how to go about doing this without

My current code:

  public IQueryable<Video> GetSimilarVideos(Guid videoId)
  {
     IQueryable<Video> result = null;
     List<Video> similarVideoList = null;

     Video currentVideo = ugdb.Videos.Find(videoId);

     List<Genre> relatedGenres = new List<Genre>();
     List<Developer> relatedDevelopers = new List<Developer>();
     List<Publisher> relatedPublishers = new List<Publisher>();

     foreach (var game in currentVideo.Games)
     {
        relatedDevelopers.AddRange(game.Developers);
        relatedPublishers.AddRange(game.Publishers);
        relatedGenres.AddRange(game.Genres);
     }

     relatedDevelopers = relatedDevelopers.Distinct().ToList<Developer>();
     relatedPublishers = relatedPublishers.Distinct().ToList<Publisher>();
     relatedGenres = relatedGenres.Distinct().ToList<Genre>();

     //This is the piece I am having trouble with!
     var relatedGames = from game in ugdb.Games
                        where game.Developers.Union(relatedDevelopers).Count() > 0
                          || game.Genres.Union(relatedGenres).Count() > 0
                          || game.Publishers.Union(relatedPublishers).Count() > 0
                        select game;

     foreach (var game in relatedGames)
     {
        similarVideoList.AddRange(game.Videos.Where(v => v.VideoType.Equals(currentVideo.VideoType)));
     }

     result = similarVideoList.Distinct().AsQueryable<Video>();

     return result;
  }

Upvotes: 0

Views: 1324

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28728

The pattern is normally to use a nested Any, or an Any with a Contains

var relatedDeveloperIds = relatedDevelopers.Select(r => r.Id).ToList();
game.Developers.Any(d => relatedDeveloperIds.Contains(d.Id))

Upvotes: 2

Mark Oreta
Mark Oreta

Reputation: 10416

You can't pass local entities - they won't translate to sql, in your case relatedDevelopers. There's not a way for Linq to Entities to translate relatedDevelopers.Contains to a Sql IN.

You can either cast the list of games and do the call in memory so that it's not having to be translated into sql - this is easier, but if your database is huge, it could be bad to load it all, just add ToList() at the end of ugdb.Games

 var relatedGames = from game in ugdb.Games.ToList()
                    where game.Developers.Any(d => relatedDevelopers.Any(r => r.DeveloperID == d.DeveloperID))
                    || game.Publishers.Any(d => relatedPublishers.Any(r => r.PublisherID == d.PublisherID))
                    || game.Genres.Any(d => relatedGenres.Any(r => r.GenreID == d.GenreID))
                    select game;

Alternatively, you can create a list of Id's and pass that, as that's a primitive type that SQL can recognize:

     var relatedDevelopers = currentVideo.Games.SelectMany( g => g.Developers ).Select( g => g.DeveloperID ).ToArray();
     var relatedPublishers = currentVideo.Games.SelectMany( g => g.Developers ).Select( g => g.DeveloperID ).ToArray();
     var relatedGenres = currentVideo.Games.SelectMany( g => g.Developers ).Select( g => g.DeveloperID ).ToArray();


     //This is the piece I am having trouble with!
     var relatedGames = from game in ugdb.Games
                        where game.Developers.Any( d => relatedDevelopers.Contains(d.DeveloperID) )
                        || game.Publishers.Any( d => relatedPublishers.Contains(d.PublisherID))
                        || game.Genres.Any( d => relatedGenres.Contains(d.GenreID) )
                        select game;

Upvotes: 3

Related Questions