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)); }