/// <summary>
/// Filters a query for tracks to those starred by a specified user.
/// </summary>
internal static IQueryable <TrackWithStarred> WhereIsStarredBy(this IQueryable <Track> tracksQuery, MediaInfoContext dbContext, int userId)
{
IQueryable <TrackStar> trackStarsQuery = GetTrackStarsQuery(dbContext, userId);
return(tracksQuery
// include indication if track is starred by user (excludes tracks not starred by
// user)
.Join(trackStarsQuery, t => t.TrackId, s => s.TrackId, (t, s) => new TrackWithStarred
{
Track = t,
Starred = s.Added,
}));
}
/// <summary>
/// Supplements a query for tracks with stars for a specified user.
/// </summary>
internal static IQueryable <TrackWithStarred> WithStarredBy(this IQueryable <Track> tracksQuery, MediaInfoContext dbContext, int userId)
{
IQueryable <TrackStar> trackStarsQuery = GetTrackStarsQuery(dbContext, userId);
return(tracksQuery
// include indication if track is starred by user
.GroupJoin(trackStarsQuery, t => t.TrackId, s => s.TrackId, (t, ss) => new
{
Track = t,
Stars = ss,
})
.SelectMany(e => e.Stars.DefaultIfEmpty(), (e, s) => new TrackWithStarred
{
Track = e.Track,
Starred = s.Added as DateTime?,
}));
}
private static IQueryable <ArtistStar> GetArtistStarsQuery(MediaInfoContext dbContext, int userId)
{
return(dbContext.ArtistStars
.Where(s => s.UserId == userId));
}
/// <summary>
/// Filters a query for tracks to those that have a specified genre.
/// </summary>
internal static IQueryable <Track> WhereHasGenre(this IQueryable <Track> tracksQuery, MediaInfoContext dbContext, int genreId)
{
return(dbContext.TrackGenres
// where track has requested genre
.Where(tg => tg.GenreId == genreId)
.Join(tracksQuery, tg => tg.TrackId, t => t.TrackId, (tg, t) => t));
}
private static IQueryable <ArtistIdWithAlbumsCount> GetAlbumArtistIdsWithAlbumsCountQuery(MediaInfoContext dbContext, IQueryable <Track> tracksQuery)
{
return(dbContext.Albums
// find album artist and album of tracks (excludes artists without albums with
// tracks)
.Join(tracksQuery, a => a.AlbumId, t => t.AlbumId, (a, t) => new
{
a.ArtistId,
t.AlbumId,
})
.Distinct()
.GroupBy(e => e.ArtistId)
// count albums
.Select(grouping => new ArtistIdWithAlbumsCount
{
ArtistId = grouping.Key,
AlbumsCount = grouping.Count(),
}));
}
////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Supplements a query for genres with the number of albums with tracks from a specified
/// query for tracks that have the specified genre and the number of tracks from a specified
/// query for tracks that have specified genre and filters out genres with no applicable
/// tracks.
/// </summary>
internal static IQueryable <GenreWithCounts> WithCounts(this IQueryable <Genre> genresQuery, MediaInfoContext dbContext, IQueryable <Track> tracksQuery)
{
// TODO: Albums count and tracks count should be doable in one query if EF will let us.
var genresWithAlbumsCountQuery = dbContext.TrackGenres
// find genre and album of tracks (also excludes genres without any tracks)
.Join(tracksQuery, tg => tg.TrackId, t => t.TrackId, (tg, t) => new
{
tg.GenreId,
t.AlbumId,
})
.Distinct()
.GroupBy(e => e.GenreId)
// count albums
.Select(grouping => new
{
GenreId = grouping.Key,
AlbumsCount = grouping.Count(),
});
var genresWithTracksCountQuery = dbContext.TrackGenres
// find genre of tracks (also excludes genres without any tracks)
.Join(tracksQuery, tg => tg.TrackId, t => t.TrackId, (tg, t) => new
{
tg.GenreId,
t.TrackId,
})
.GroupBy(e => e.GenreId)
// count tracks
.Select(grouping => new
{
GenreId = grouping.Key,
TracksCount = grouping.Count(),
});
return(genresQuery
.Join(genresWithAlbumsCountQuery, g => g.GenreId, e => e.GenreId, (g, a) => new
{
Genre = g,
a.AlbumsCount,
})
.Join(genresWithTracksCountQuery, e => e.Genre.GenreId, e => e.GenreId, (e, t) => new GenreWithCounts
{
Genre = e.Genre,
AlbumsCount = e.AlbumsCount,
TracksCount = t.TracksCount,
}));
}
/// <summary>
/// Supplements a query for artists with stars for a specified user.
/// </summary>
internal static IQueryable <ArtistWithStarred> WithStarredBy(this IQueryable <Artist> artistsQuery, MediaInfoContext dbContext, int userId)
{
IQueryable <ArtistStar> artistStarsQuery = GetArtistStarsQuery(dbContext, userId);
return(artistsQuery
// include indication if album is starred by user
.GroupJoin(artistStarsQuery, a => a.ArtistId, s => s.ArtistId, (a, ss) => new
{
Artist = a,
Stars = ss,
})
.SelectMany(e => e.Stars.DefaultIfEmpty(), (e, s) => new ArtistWithStarred
{
Artist = e.Artist,
Starred = s.Added as DateTime?,
}));
}
/// <summary>
/// Supplements a query for artists with the number of albums with tracks from a specified
/// track query and filters out those with no applicable albums.
/// </summary>
internal static IQueryable <ArtistWithStarredAndAlbumsCount> WithAlbumsCount(this IQueryable <ArtistWithStarred> artistsQuery, MediaInfoContext dbContext, IQueryable <Track> tracksQuery)
{
IQueryable <ArtistIdWithAlbumsCount> albumArtistIdsWithAlbumsCountQuery = GetAlbumArtistIdsWithAlbumsCountQuery(dbContext, tracksQuery);
return(artistsQuery
// include album aggregation (excludes artists without albums with tracks)
.Join(albumArtistIdsWithAlbumsCountQuery, e1 => e1.Artist.ArtistId, e2 => e2.ArtistId, (e1, e2) => new ArtistWithStarredAndAlbumsCount
{
Artist = e1.Artist,
Starred = e1.Starred,
AlbumsCount = e2.AlbumsCount,
}));
}
internal static IQueryable <ArtistWithStarred> WhereIsStarredBy(this IQueryable <Artist> artistsQuery, MediaInfoContext dbContext, int userId)
{
IQueryable <ArtistStar> artistStarsQuery = GetArtistStarsQuery(dbContext, userId);
return(artistsQuery
// include indication if artist is starred by user (excludes albums not starred by
// user)
.Join(artistStarsQuery, a => a.ArtistId, s => s.ArtistId, (a, s) => new ArtistWithStarred
{
Artist = a,
Starred = s.Added,
}));
}
////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Filters a query for artists to those that have albums that have tracks in a specified
/// library and accessible by a specified user.
/// </summary>
internal static IQueryable <Artist> WhereIsAccessibleBy(this IQueryable <Artist> artistsQuery, MediaInfoContext dbContext, int userId, int?libraryId)
{
IQueryable <Track> tracksQuery = dbContext.Tracks
.WhereIsAccessibleBy(userId, libraryId);
return(artistsQuery
// where artist has tracks in library accessible by user
.Where(a => tracksQuery.Any(t => t.Album.ArtistId == a.ArtistId)));
}
private static IQueryable <AlbumIdWithTracksCount> GetAlbumIdsWithTracksCountQuery(MediaInfoContext dbContext, IQueryable <Track> tracksQuery)
{
return(dbContext.Albums
// find album tracks (excludes albums without tracks)
.Join(tracksQuery, a => a.AlbumId, t => t.AlbumId, (a, t) => new
{
a.AlbumId,
TrackDuration = t.Duration ?? 0,
})
.GroupBy(e => e.AlbumId)
// count album tracks and compute album duration
.Select(grouping => new AlbumIdWithTracksCount
{
AlbumId = grouping.Key,
TracksCount = grouping.Count(),
Duration = grouping.Sum(e => e.TrackDuration),
}));
}
/// <summary>
/// Supplements a query for albums with the number of tracks from a specified query for
/// tracks and filters out those with no applicable tracks.
/// </summary>
internal static IQueryable <AlbumWithStarredAndTracksCount> WithTracksCount(this IQueryable <AlbumWithStarred> albumsQuery, MediaInfoContext dbContext, IQueryable <Track> tracksQuery)
{
IQueryable <AlbumIdWithTracksCount> albumIdsWithTracksCountQuery = GetAlbumIdsWithTracksCountQuery(dbContext, tracksQuery);
return(albumsQuery
// include track aggregations (excludes albums without tracks)
.Join(albumIdsWithTracksCountQuery, e1 => e1.Album.AlbumId, e2 => e2.AlbumId, (e1, e2) => new AlbumWithStarredAndTracksCount
{
Album = e1.Album,
Starred = e1.Starred,
TracksCount = e2.TracksCount,
Duration = e2.Duration,
}));
}
/// <summary>
/// Filters a query for albums to those that have tracks that have a specified genre.
/// </summary>
internal static IQueryable <Album> WhereHasTrackWithGenre(this IQueryable <Album> albumsQuery, MediaInfoContext dbContext, IQueryable <Track> tracksQuery, int genreId)
{
return(albumsQuery
// where album has tracks with requested genre
.Where(a => tracksQuery
.Where(t => t.AlbumId == a.AlbumId)
.Join(dbContext.TrackGenres, t => t.TrackId, tg => tg.TrackId, (t, tg) => tg)
.Any(tg => tg.GenreId == genreId)));
}
