Exemplo n.º 1
0
        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));
        }
Exemplo n.º 2
0
        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));
        }