public override async Task <IList <File> > FindAsync(FileQueryOptions options, CancellationToken cancellationToken)
{
var whereClauses = new List <string>();
var cmdParameters = new List <SqlParameter>();
if (options.Ids.Any())
{
var idsString = string.Join(',', options.Ids);
whereClauses.Add(" [Id] IN (SELECT convert(int, value) FROM string_split(@ids, ',')) ");
cmdParameters.Add(Connection.CreateCommandParameter("@ids", SqlDbType.NVarChar, idsString));
}
if (options.DocumentIds.Any())
{
var documentIdsString = string.Join(',', options.DocumentIds);
whereClauses.Add(" [DocumentId] IN (SELECT value FROM string_split(@documentIds, ',')) ");
cmdParameters.Add(Connection.CreateCommandParameter("@documentIds", SqlDbType.NVarChar, documentIdsString));
}
if (options.Type != null)
{
whereClauses.Add(" [Type] = @type ");
cmdParameters.Add(Connection.CreateCommandParameter("@type", SqlDbType.NVarChar, options.Type));
}
if (options.Indexes.Any())
{
var indexesString = string.Join(',', options.Indexes);
whereClauses.Add(" [Index] IN (SELECT value FROM string_split(@indexes, ',')) ");
cmdParameters.Add(Connection.CreateCommandParameter("@indexes", SqlDbType.NVarChar, indexesString));
}
var skipClause = options.Offset > 0 || options.Limit > 0 ? $" OFFSET {options.Offset} ROWS " : string.Empty;
var takeClause = options.Limit > 0 ? $" FETCH NEXT {options.Limit} ROWS ONLY " : string.Empty;
var orderByClause = skipClause.Length > 0 || takeClause.Length > 0 ? " ORDER BY Id ASC" : string.Empty;
var findQuery = $"{FileSqlScripts.Find} { (whereClauses.Any() ? $" WHERE {string.Join(" AND ", whereClauses)}" : string.Empty)} {orderByClause} {skipClause} {takeClause}";
return(await Connection !.ExecuteQueryAsync(Connection.CreateCommand(findQuery, cmdParameters), FileHelpers.ReadFiles, cancellationToken));
}
public async void Find()
{
// create files
var docId1 = 1001; // be unqiue in the test file
var docId2 = 1002; // be unqiue in the test file
var files = new List <File>();
files.Add(await Storage.CreateRandomFileAsync(docId1, FileType.Native));
files.Add(await Storage.CreateRandomFileAsync(docId2, FileType.Native));
for (int i = 1; i <= 10; i++)
{
files.Add(await Storage.CreateRandomFileAsync(docId1, FileType.Image));
}
for (int i = 1; i <= 5; i++)
{
files.Add(await Storage.CreateRandomFileAsync(docId2, FileType.Image));
}
// test options
// DocumentIds
var opts1 = new FileQueryOptions
{
DocumentIds = { docId1 }
};
var q1 = await Storage.Metadata.File.FindAsync(opts1, CancellationToken.None);
Assert.Equal(q1.Select(f => f.Id), files.Where(f => f.DocumentId == docId1).Select(f => f.Id));
// FileIds
var fileIds = files.Select(f => f.Id).Take(2).ToList();
var opts2 = new FileQueryOptions
{
Ids = { fileIds[0], fileIds[1] }
};
var q2 = await Storage.Metadata.File.FindAsync(opts2, CancellationToken.None);
Assert.Equal(q2.Select(f => f.Id), files.Where(f => fileIds.Contains(f.Id)).Select(f => f.Id));
// FileType
var opts3 = new FileQueryOptions
{
Type = FileType.Native
};
var q3 = await Storage.Metadata.File.FindAsync(opts3, CancellationToken.None);
Assert.Equal(q3.Select(f => f.Id), files.Where(f => f.Type == FileType.Native).Select(f => f.Id));
// Indexes
var indexes = new[] { 1, 2, 3 };
var opts4 = new FileQueryOptions
{
Type = FileType.Image,
Indexes = { 1, 2, 3 }
};
var q4 = await Storage.Metadata.File.FindAsync(opts4, CancellationToken.None);
Assert.Equal(q4.Select(f => f.Id), files.Where(f => f.Type == FileType.Image && indexes.Contains(f.Index)).Select(f => f.Id));
// Take, Skip
var opts5 = new FileQueryOptions
{
Offset = 5,
Limit = 5
};
var q5 = await Storage.Metadata.File.FindAsync(opts5, CancellationToken.None);
Assert.Equal(q5.Select(f => f.Id), files.Skip(5).Take(5).Select(f => f.Id));
}