public async static Task <IList <TResult> > ExecuteAsync <U, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TResult>(this IStoredProcedure <T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TResult> storedProcedure, IList <U> partitionKeys, T1 t1, T2 t2, T3 t3, T4 t4, T5 t5, T6 t6, T7 t7, T8 t8, T9 t9, T10 t10, T11 t11, T12 t12, T13 t13, T14 t14, T15 t15, T16 t16, RequestOptions requestOptions = null)
 {
     return(await Task.WhenAll(
                partitionKeys.Select(partitionKey => storedProcedure.ExecuteAsync(t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, SetPartitionKey(partitionKey, requestOptions)))));
 }
Esempio n. 2
0
        public async Task Query()
        {
            IQuery <string> query       = _database.Products.Query().Join(e => e.Owner).Where(e => e.IsAvailable).OrderBy(e => e.Name).Offset(10).Limit(20).Select(e => e.Name);
            IDelete         batchDelete = _database.Products.Delete().Where(e => e.IsAvailable);
            IInsert         batchInsert = _database.Products.Insert(new Product(), new Product());
            IUpdate         batchUpdate = _database.Products.Update().Where(e => e.IsAvailable).Set(e => new Product {
                IsAvailable = false
            });

            IUpdate singleUpdate = _database.Products.Update().Set(e => e.NetPrice, e => e.NetPrice * 1.1m).Set(e => e.DateAdded, DateTime.UtcNow);

            var(queryResult, deleteAffected, updateAffected, insertAffected) = _database.Execute(query, batchDelete, batchUpdate, batchInsert);
            (queryResult, deleteAffected, updateAffected, insertAffected)    = await _database.ExecuteAsync(query, batchDelete, batchUpdate, batchInsert);

            string querySql  = query.ToSqlString();
            string deleteSql = batchDelete.ToSqlString();
            string updateSql = batchUpdate.ToSqlString();
            string insertSql = batchInsert.ToSqlString();

            var product1 = await _database.Products.Query().Where(e => e.Id == 1).Single().ExecuteAsync();

            var product2 = await _database.Products.Query().Where(e => e.Id == 2).Single().ExecuteAsync();

            var updatedExplicitly = await _database.Products.Update(product1, product2).ExecuteAsync();

            var product3 = await _database.Products.Query().Where(e => e.Id == 3).Single().ExecuteAsync();

            var product4 = await _database.Products.Query().Where(e => e.Id == 4).Single().ExecuteAsync();

            var deletedExplicitly = await _database.Products.Delete(product3, product4).ExecuteAsync();

            var copyInsert = _database.ArchivedProducts.InsertFrom(_database.Products.Query().Where(p => !p.IsAvailable && p.DateAdded < DateTime.UtcNow.AddYears(-1)).Select(e => new ArchivedProduct { /* ... */
            }));
            var copiedRows = await copyInsert.ExecuteAsync();

            queryResult = _database.ExecuteSql <List <string> >(querySql, new { x = 1 });
            queryResult = await _database.ExecuteSqlAsync <List <string> >(querySql, new { x = 1 });

            IStoredProcedure <List <ProductDto> > searchedProductsProcedure = _database.SearchProducts("name");
            var searchedProducts = searchedProductsProcedure.Execute();

            searchedProducts = await searchedProductsProcedure.ExecuteAsync();

            searchedProducts = _database.Execute(searchedProductsProcedure);
            searchedProducts = await _database.ExecuteAsync(searchedProductsProcedure);

            IFunction <decimal> priceFunction = _database.CalculatePrice(123);
            var price = priceFunction.Execute();

            price = await priceFunction.ExecuteAsync();

            price = _database.Execute(priceFunction);
            price = await _database.ExecuteAsync(priceFunction);

            _database.Insert(new Product()).Execute();
            var product5 = _database.Query <Product>().Where(e => e.Id == 5).Execute();

            _database.Update(product5).Execute();
            _database.Delete(product5).Execute();

            var q1 = _database.Products.Query().Where(e => e.OwnerId == 5);
            var q2 = _database.Products.Query().Where(e => e.OwnerId == 6);
            var q3 = _database.Products.Query().Where(e => e.OwnerId == 7);

            List <Product> unionList123 = q1.Union(q2).UnionAll(q3).Execute();
            List <Product> unionList    = _database.Products
                                          .Query().Where(e => e.OwnerId == 5)
                                          .UnionAll()
                                          .Query().Where(e => e.OwnerId == 6)
                                          .Execute();

            var product7 = _database.Query <Product>(options => options.AsEditable().WithComment("my query tag").WithHint("NOLOCK"))
                           .Where(e => e.Id == 7).Execute();

            // WITH CTE explicit
            var cte1      = _database.Query <Product>();
            var cte2      = _database.Query(cte1).Where(e => e.IsAvailable);
            var withQuery = _database.Query(cte2).Where(e => e.NetPrice > 0);

            // Recursive CTE
            var managerQuery = _database.Query <Employee>().Where(e => e.ManagerId == null)
                               .Select(e => new EmployeeDto {
                Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = 1
            });
            var employeeQuery = _database.Query <Employee>().Join(managerQuery, (l, r) => l.ManagerId == r.Id)
                                .Where((e, mq) => e.Id == 234)
                                .Select((e, mq) => new EmployeeDto {
                Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = mq.Level + 1
            });
            var hierarchyUnion = managerQuery.UnionAll(employeeQuery);

            var employeeHierarchy = _database.Query(hierarchyUnion)
                                    .OrderBy(e => e.Level)
                                    .ThenOrderByDescending(e => e.ManagerId)
                                    .Select(e => new EmployeeHierarchy
            {
                Employee = e.Name,
                Level    = e.Level,
                Manager  = _database.Query <Employee>().Where(m => m.Id == e.ManagerId).Select(m => m.Name).First().Execute()
            })
                                    .Distinct()
                                    .Execute();

            // Subquery
            _database.Products.Query().Select(e => new ProductDto()).OrderBy(e => e.Price).Execute();

            // Multiquery
            _database.Query <Product, Owner, Employee>();

            _database.Query(_database.Products, _database.AvailableProducts, hierarchyUnion)
            .Join(managerQuery, (p, _, _, m) => p.Id == m.Id);

            // Group by
            var groupedProducts = _database.Query <Product>()
                                  .GroupBy(p => new { p.Id, p.Name })
                                  .Having(p => p.Key.Id > 4 && p.Count() > 5 && p.Max(e => e.NetPrice) > 50)
                                  .Select(p => new
            {
                p.Key.Id,
                p.Key.Name,
                Count          = p.Count(),
                Max            = p.Max(e => e.NetPrice),
                CountAvailable = p.Where(e => e.IsAvailable).Count(),
                MaxAvailable   = p.Where(e => e.IsAvailable).Max(e => e.NetPrice),
                AllAvailable   = p.Where(e => e.IsAvailable).Select(e => new { e.Name, e.NetPrice }).AsList()
            })
                                  .Execute();

            // Mapping
            Product[] productArray = _database.Query <Product>().AsArray().Execute();
            Dictionary <int, Product> productByOwnerDict = _database.Query <Product>().AsDictionary(e => e.OwnerId).Execute();
        }
 public async static Task <IList <TResult> > ExecuteAsync <U, T1, T2, T3, T4, T5, T6, T7, TResult>(this IStoredProcedure <T1, T2, T3, T4, T5, T6, T7, TResult> storedProcedure, IList <U> partitionKeys, T1 t1, T2 t2, T3 t3, T4 t4, T5 t5, T6 t6, T7 t7, RequestOptions requestOptions = null)
 {
     return(await Task.WhenAll(
                partitionKeys.Select(partitionKey => storedProcedure.ExecuteAsync(t1, t2, t3, t4, t5, t6, t7, SetPartitionKey(partitionKey, requestOptions)))));
 }
 public async static Task <IList <TResult> > ExecuteAsync <U, T1, TResult>(this IStoredProcedure <T1, TResult> storedProcedure, IList <U> partitionKeys, T1 t1, RequestOptions requestOptions = null)
 {
     return(await Task.WhenAll(
                partitionKeys.Select(partitionKey => storedProcedure.ExecuteAsync(t1, SetPartitionKey(partitionKey, requestOptions)))));
 }
 public static Task <TResult> ExecuteAsync <U, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TResult>(this IStoredProcedure <T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16, TResult> storedProcedure, U partitionKey, T1 t1, T2 t2, T3 t3, T4 t4, T5 t5, T6 t6, T7 t7, T8 t8, T9 t9, T10 t10, T11 t11, T12 t12, T13 t13, T14 t14, T15 t15, T16 t16, RequestOptions requestOptions = null)
 {
     requestOptions = SetPartitionKey(partitionKey, requestOptions);
     return(storedProcedure.ExecuteAsync(t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, requestOptions));
 }
 public static Task <TResult> ExecuteAsync <U, T1, T2, T3, T4, T5, T6, T7, T8, TResult>(this IStoredProcedure <T1, T2, T3, T4, T5, T6, T7, T8, TResult> storedProcedure, U partitionKey, T1 t1, T2 t2, T3 t3, T4 t4, T5 t5, T6 t6, T7 t7, T8 t8, RequestOptions requestOptions = null)
 {
     requestOptions = SetPartitionKey(partitionKey, requestOptions);
     return(storedProcedure.ExecuteAsync(t1, t2, t3, t4, t5, t6, t7, t8, requestOptions));
 }
 public static Task <TResult> ExecuteAsync <U, T1, T2, T3, TResult>(this IStoredProcedure <T1, T2, T3, TResult> storedProcedure, U partitionKey, T1 t1, T2 t2, T3 t3, RequestOptions requestOptions = null)
 {
     requestOptions = SetPartitionKey(partitionKey, requestOptions);
     return(storedProcedure.ExecuteAsync(t1, t2, t3, requestOptions));
 }