public async Task <PaginationQueryResult <Beatmap> > GetRecentList(
int page = 0,
int countPerPage = 50,
BeatmapOrderOptions options = BeatmapOrderOptions.UpdateTime)
{
var list = RecentList.AsNoTracking();
var queryable = options switch
{
BeatmapOrderOptions.UpdateTime => list.OrderByDescending(k => k.UpdateTime),
_ => throw new ArgumentOutOfRangeException(nameof(options), options, null)
};
var count = await queryable.CountAsync();
var collection = queryable
.Include(k => k.Beatmap);
var result = await collection
.Skip(page *countPerPage)
.Take(countPerPage)
.Select(k => k.Beatmap)
.ToListAsync();
return(new PaginationQueryResult <Beatmap>(result, count));
}
private static string GetOrderAndTakeQueryAndArgs(BeatmapOrderOptions beatmapOrderOptions, int page, int countPerPage)
{
string orderBy = beatmapOrderOptions switch
{
BeatmapOrderOptions.Title => " ORDER BY TitleUnicode, Title ",
BeatmapOrderOptions.CreateTime => " ORDER BY CreateTime DESC ",
BeatmapOrderOptions.UpdateTime => " ORDER BY UpdateTime DESC ",
BeatmapOrderOptions.Artist => " ORDER BY ArtistUnicode, Artist ",
_ => throw new ArgumentOutOfRangeException(nameof(beatmapOrderOptions), beatmapOrderOptions, null)
};
string limit = $" LIMIT {page * countPerPage}, {countPerPage} ";
return(orderBy + limit);
}
}
public async Task <PaginationQueryResult <Beatmap> > SearchBeatmapByOptions(
string searchText,
BeatmapOrderOptions beatmapOrderOptions,
int page,
int countPerPage)
{
var sqliteParameters = new List <SqliteParameter>();
var command = " SELECT * FROM Beatmaps WHERE ";
var keywordSql = GetKeywordQueryAndArgs(searchText, ref sqliteParameters);
var sort = GetOrderAndTakeQueryAndArgs(beatmapOrderOptions, page, countPerPage);
var sw = Stopwatch.StartNew();
try
{
var sql = command + keywordSql;
var totalCount = await Beatmaps
.FromSqlRaw(sql, sqliteParameters.Cast <object>().ToArray())
.CountAsync();
var s = sql + sort;
var beatmaps = await Beatmaps
.FromSqlRaw(s, sqliteParameters.Cast <object>().ToArray())
.ToListAsync();
return(new PaginationQueryResult <Beatmap>(beatmaps, totalCount));
}
catch (Exception ex)
{
Logger.Error(ex, "Error while calling SearchBeatmapByOptions().");
throw;
}
finally
{
Logger.Debug("查询花费: {0}", sw.ElapsedMilliseconds);
sw.Stop();
}
}
public async Task <PaginationQueryResult <Beatmap> > GetBeatmapsFromCollection(Collection collection,
int page = 0,
int countPerPage = 50,
BeatmapOrderOptions options = BeatmapOrderOptions.CreateTime)
{
if (collection.Id == Guid.Empty)
{
Console.WriteLine("No collection found.");
return(new PaginationQueryResult <Beatmap>(new List <Beatmap>(), 0));
}
collection = await Collections.FindAsync(collection.Id);
if (collection == null)
{
Console.WriteLine("No collection found.");
return(new PaginationQueryResult <Beatmap>(new List <Beatmap>(), 0));
}
var relations = Relations
.AsNoTracking()
.Where(k => k.CollectionId == collection.Id);
var beatmaps = relations.Join(Beatmaps, k => k.BeatmapId, k => k.Id, (k, x) => x);
var count = await beatmaps.CountAsync();
var enumerable = options switch
{
BeatmapOrderOptions.UpdateTime => beatmaps.OrderByDescending(k => k.UpdateTime),
BeatmapOrderOptions.CreateTime => beatmaps.OrderByDescending(k => k.CreateTime),
_ => throw new ArgumentOutOfRangeException(nameof(options), options, null)
};
var result = await enumerable
.Skip(page *countPerPage)
.Take(countPerPage)
.ToListAsync();
return(new PaginationQueryResult <Beatmap>(result, count));
}
