How to create an EF query which returns a nested list with navigation properties based on different-CodePudding

I have 3 nested entities, TvShow, Season and Episodes:

public class TvShow
{
    public Id { get; set; }
    public List<Season> Seasons { get; set; }
}

public class Season
{
    public Id { get; set; }
    public int TvShowId { get; set; }
    public TvShow TvShow { get; set; }
    public List<Episode> Episodes { get; set; }
}

public class Episode
{
    public Id { get; set; }
    public int TvShowId { get; set; }
    public TvShow TvShow { get; set; }
    public int SeasonId { get; set; }
    public Season Season { get; set; }
}

I'm trying to write a query in which I can give the following input:

    public List<int> TvShowIds { get; }
    public List<int> TvShowSeasonIds { get; }
    public List<int> TvShowEpisodeIds { get; }

Here is where I get stuck, the query should include all different types of ids with its child navigation included.

E.g. For every TvShowId, it should return the TvShow entity with all of its Seasons and Episodes included. For every SeasonId it should include all of its Episodes and its parent TvShow. For every EpisodeId, it should include its parent Season and TvShow while not including any other Episodes that belong to the same Season.

Then all of this should be returned as a merged (union?) nested List<TvShow>() without any duplicates.

I have tried different approaches but I get stuck every time. This should not be difficult, maybe I'm missing something in my thought process?

Help is very much appreciated!

CodePudding user response：

Given you have (up to) 3 distinct filtering requirements, this should probably be done with separate queries as there are a few caveat conditions that your code will need to handle. For example I might select an episode ID from a season or show that I had specified a show or season ID for, in which case I don't want to double-up that show.

Then all of this should be returned as a merged (union?) nested List() without any duplicates.

There is also the possibility that for a given show I might select it via a Season filter Say Season 2 of Babylon 5, but then give an episode filter that selects the 3rd episode from Season 4. From the sounds of things you would probably want to retrieve Babylon 5 (show) with all of Season 2, but then also Season 4 for just Episode #3.

Because we may need to select multiple seasons composed of their respective episodes it is probably best to attack this from the Episode then re-compose the view from the show-side which should be listing just the respective Seasons needed.

One warning about this approach rather than projection is that the resulting entities will no longer be a complete, or complete-able representation of the data. By Selecting the show and season solely with a subset of desired episodes that Show and Season(s) no longer reflect the complete list of episodes. Be careful if passing this entity to any methods expecting to act upon a complete representation of a Show /w seasons and episodes.

List<Episodes> episodes = new List<Episode>();
IEnumerable<int> existingEpisodeIds = new List<int>();

if (filter.TvShowsIds.Any())
{
    episodes.AddRange(Context.Episodes
        .Include(x => x.Season)
            .ThenInclude(x => x.TvShow)
         .Where(x => filter.TvShowIds.Contains(x.Season.TvShow.Id))
         .ToList());
    existingEpisodeIds = episode.Select(x => x.id).ToList();
}
if (filter.SeasonIds.Any())
{
    episodes.AddRange(Context.Episodes
        .Include(x => x.Season)
            .ThenInclude(x => x.TvShow)
        .Where(x => !existingEpisodeIds.Contains(x.Id) 
            && filter.SeasonIds.Contains(x.Season.Id)
        .ToList());
    existingEpisodeIds = episodes.Select(x => x.id).ToList();
}
if (filter.Episodes.Any())
{
    episodes.AddRange(Context.Episodes
        .Include(x => x.Season)
            .ThenInclude(x => x.TvShow)
        .Where(x => !existingEpisodeIds.Contains(x.Id) 
            && filter.EpisodeIds.Contains(x.Id)))
         .ToList();
}

var shows = episodes.Select(x => x.Season.Show).Distinct().ToList();

By attacking this from the episode and eager loading the respective Season and Show in that direction, the related seasons will appear for each selected episode, as would the TvShow. This means that the only seasons that appear for a resulting show would be ones that resulted in at least one episode being selected. We track the selected episode IDs and refresh it with subsequent searches to avoid doubling up episodes returned. (I.e. Episode 3 in Season 2 if all Season 2 episodes were already selected, or all episodes for that show)

The caveat of this approach is that it assumes that all shows have seasons and all seasons have episodes which I believe is a safe assumption. For instance if you did have a TvShow Id #99 that had no seasons or episodes and had a TvShowId filter of 99, it would not be returned since we are querying against episodes.

CodePudding user response：

Assuming I've understood your question correctly, I doubt EF core will be able to translate unless it's done in memory, but you could try this:

await Db.TvShows
        .Where(x => TvShowIds.Contains(x.Id))
            .Select(x => { x.Seasons = x.Seasons.Where(y => TvShowSeasonsIds.Contains(y.Id))
                .Select(y => { y.Episodes = y.Episodes.Where(z => TvShowEpisodeIds.Contains(z.Id)).ToList(); return y; }).ToList(); return x; })
        .ToListAsync();

EDIT, I've just tried the below in .Net Core 3.1 and it seems to work. If you need the related entities going upwards, you can just add to the select. You should probably be using DTOs instead of the actual EF core entities as well Create Data Transfer Objects (DTOs)

await Db.TvShows
        .Where(x => TvShowIds.Contains(x.Id))
        .Select(x => new TvShow
         {
             Id = x.Id,
             Seasons = x.Seasons.Where(y => TvShowSeasonsIds.Contains(y.Id))
                                .Select(y => new Season
                                {
                                    Id = y.Id,
                                    TvShowId = y.TvShowId,
                                    Episodes = y.Episodes.Where(z => TvShowEpisodeIds.Contains(z.Id)).ToList(),
                                }).ToList()
         }).ToListAsync();