public async Task <int> SongCountAsync()
{
using (var context = new MusCatDbContext(_connectionString))
{
return(await context.Songs.CountAsync().ConfigureAwait(false));
}
}
public async Task <Song> SelectSongAsync()
{
using (var context = new MusCatDbContext(_connectionString))
{
// find out the maximum song ID in the database
var maxSid = context.Songs.Max(s => s.Id);
var songId = _songSelector.Next() % maxSid;
var song = await context.Songs
.FirstAsync(s => s.Id >= songId)
.ConfigureAwait(false);
// include the corresponding album of our song
song.Album = await context.Albums
.FirstAsync(a => a.Id == song.AlbumId)
.ConfigureAwait(false);
// do the same thing with performer for included album
song.Album.Performer = await context.Performers
.FirstAsync(p => p.Id == song.Album.PerformerId)
.ConfigureAwait(false);
return(song);
}
}
public async Task <IDictionary <string, int> > GetPerformerCountriesAsync()
{
using (var context = new MusCatDbContext(_connectionString))
{
return(await
context.Performers.AsNoTracking()
.GroupBy(p => p.Country.Name)
.Where(g => g.Key != null)
.ToDictionaryAsync(g => g.Key, g => g.Count())
.ConfigureAwait(false));
}
}
public async Task <IEnumerable <Album> > GetLatestAlbumsAsync(int latestCount = 7)
{
using (var context = new MusCatDbContext(_connectionString))
{
return(await
context.Albums.Include("Performer").AsNoTracking()
.OrderByDescending(a => a.Id)
.Take(latestCount)
.ToListAsync()
.ConfigureAwait(false));
}
}
public async Task <IEnumerable <Performer> > GetTopPerformersAsync(int count = 7, string country = null)
{
using (var context = new MusCatDbContext(_connectionString))
{
return(await
context.Performers.Include("Country").AsNoTracking()
.Where(p => p.Country.Name == country)
.OrderByDescending(p => p.Albums.Sum(a => a.Rate))
.Take(count)
.ToListAsync()
.ConfigureAwait(false));
}
}
public Song DefaultSong()
{
using (var context = new MusCatDbContext(_connectionString))
{
var song = context.Songs.First();
// include the corresponding album of our song
song.Album = context.Albums.First(a => a.Id == song.AlbumId);
// do the same thing with performer for included album
song.Album.Performer = context.Performers.First(p => p.Id == song.Album.PerformerId);
return(song);
}
}
public async Task <IEnumerable <DecadeAlbumsStats> > GetAlbumDecadesAsync()
{
using (var context = new MusCatDbContext(_connectionString))
{
return(await
context.Albums.AsNoTracking()
.GroupBy(a => a.ReleaseYear / 10)
.Select(g => new DecadeAlbumsStats
{
Decade = g.Key + "0s",
TotalCount = g.Count(),
MaxRatedCount = g.Count(x => x.Rate == 10)
})
.OrderBy(d => d.Decade)
.ToListAsync()
.ConfigureAwait(false));
}
}
