public async Task <QueryResult <Product> > ReadProduct(int[] param1, string param2) { // var queryInfo = RequestContext.CurrentQueryInfo; PerformQueryResult <Product> productsResult = PerformQuery((countQuery) => countQuery.CountAsync()); int?totalCount = await productsResult.CountAsync(); List <Product> productsList = new List <Product>(); if (totalCount > 0) { productsList = await productsResult.Data.ToListAsync(); } int[] productIDs = productsList.Select(p => p.ProductId).Distinct().ToArray(); QueryResult <Product> queryResult = new QueryResult <Product>(productsList, totalCount); SubResult subResult = new SubResult { dbSetName = "SalesOrderDetail", Result = await DB.SalesOrderDetail.AsNoTracking().Where(sod => productIDs.Contains(sod.ProductId)).ToListAsync() }; // include related SalesOrderDetails with the products in the same query result queryResult.subResults.Add(subResult); // example of returning out of band information and use it on the client (of it can be more useful than it) queryResult.extraInfo = new { test = "ReadProduct Extra Info: " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") }; return(queryResult); }
public async Task <QueryResult <LookUpProduct> > ReadProductLookUp() { PerformQueryResult <Product> res = PerformQuery <Product>((countQuery) => countQuery.CountAsync()); int?totalCount = await res.CountAsync(); List <LookUpProduct> products = new List <LookUpProduct>(); if (totalCount > 0) { products = await res.Data.Select(p => new LookUpProduct { ProductId = p.ProductId, Name = p.Name }).ToListAsync(); } return(new QueryResult <LookUpProduct>(products, totalCount)); }
public async Task <QueryResult <Customer> > ReadCustomer(bool?includeNav) { IQueryable <Customer> customers = DB.Customer; QueryRequest queryInfo = this.GetCurrentQueryInfo(); // AddressCount does not exists in Database (we calculate it), so it is needed to sort it manually SortItem addressCountSortItem = queryInfo.sortInfo.sortItems.FirstOrDefault(sortItem => sortItem.fieldName == "AddressCount"); if (addressCountSortItem != null) { queryInfo.sortInfo.sortItems.Remove(addressCountSortItem); if (addressCountSortItem.sortOrder == SortOrder.ASC) { customers = customers.OrderBy(c => c.CustomerAddress.Count()); } else { customers = customers.OrderByDescending(c => c.CustomerAddress.Count()); } } int?totalCount = queryInfo.pageIndex == 0 ? 0 : (int?)null; // perform query PerformQueryResult <Customer> customersResult = this.PerformQuery(customers.AsNoTracking(), queryInfo.pageIndex == 0 ? (countQuery) => countQuery.CountAsync() : (Func <IQueryable <Customer>, Task <int> >)null); System.Collections.Generic.List <Customer> customersList = await customersResult.Data.ToListAsync(); // only execute total counting if we got full page size of rows, preventing unneeded database call to count total if (queryInfo.pageIndex == 0 && customersList.Any()) { int cnt = customersList.Count; if (cnt < queryInfo.pageSize) { totalCount = cnt; } else { totalCount = totalCount = await customersResult.CountAsync(); } } QueryResult <Customer> queryRes = new QueryResult <Customer>(customersList, totalCount); if (includeNav == true) { int[] customerIDs = customersList.Select(c => c.CustomerId).ToArray(); System.Collections.Generic.List <CustomerAddress> customerAddress = await DB.CustomerAddress.AsNoTracking().Where(ca => customerIDs.Contains(ca.CustomerId)).ToListAsync(); int[] addressIDs = customerAddress.Select(ca => ca.AddressId).ToArray(); SubResult subResult1 = new SubResult { dbSetName = "CustomerAddress", Result = customerAddress }; SubResult subResult2 = new SubResult { dbSetName = "Address", Result = await DB.Address.AsNoTracking().Where(adr => addressIDs.Contains(adr.AddressId)).ToListAsync() }; // since we have loaded customer addresses - update server side calculated field: AddressCount // (which i have introduced for testing purposes as a server calculated field) ILookup <int, CustomerAddress> addressLookUp = customerAddress.ToLookup(ca => ca.CustomerId); customersList.ForEach(customer => { customer.AddressCount = addressLookUp[customer.CustomerId].Count(); }); // return two subresults with the query results queryRes.subResults.Add(subResult1); queryRes.subResults.Add(subResult2); } return(queryRes); }
public async Task <QueryResult <Customer> > ReadCustomer(bool?includeNav) { IQueryable <Customer> customers = DB.Customers; QueryRequest queryInfo = this.GetCurrentQueryInfo(); // AddressCount does not exists in Database (we calculate it), so it is needed to sort it manually SortItem addressCountSortItem = queryInfo.sortInfo.sortItems.FirstOrDefault(sortItem => sortItem.fieldName == "AddressCount"); if (addressCountSortItem != null) { queryInfo.sortInfo.sortItems.Remove(addressCountSortItem); if (addressCountSortItem.sortOrder == SortOrder.ASC) { customers = customers.OrderBy(c => c.CustomerAddresses.Count()); } else { customers = customers.OrderByDescending(c => c.CustomerAddresses.Count()); } } // perform query PerformQueryResult <Customer> customersResult = this.PerformQuery(customers, (countQuery) => countQuery.CountAsync()); int?totalCount = await customersResult.CountAsync(); List <Customer> customersList = await customersResult.Data.ToListAsync(); QueryResult <Customer> queryRes = new QueryResult <Customer>(customersList, totalCount); if (includeNav == true) { int[] customerIDs = customersList.Select(c => c.CustomerID).ToArray(); List <CustomerAddress> customerAddress = await DB.CustomerAddresses.Where(ca => customerIDs.Contains(ca.CustomerID)).ToListAsync(); int[] addressIDs = customerAddress.Select(ca => ca.AddressID).ToArray(); SubResult subResult1 = new SubResult { dbSetName = this.GetSetInfosByEntityType(typeof(CustomerAddress)).Single().dbSetName, Result = customerAddress }; SubResult subResult2 = new SubResult { dbSetName = this.GetSetInfosByEntityType(typeof(Address)).Single().dbSetName, Result = await DB.Addresses.AsNoTracking().Where(adr => addressIDs.Contains(adr.AddressID)).ToListAsync() }; // since we have preloaded customer addresses then update server side calculated field: AddressCount // (which i have introduced for testing purposes as a server calculated field) customersList.ForEach(customer => { customer.AddressCount = customer.CustomerAddresses.Count(); }); // return two subresults with the query results queryRes.subResults.Add(subResult1); queryRes.subResults.Add(subResult2); } return(queryRes); }
public async Task <QueryResult <CustomerJSON> > ReadCustomerJSON() { IQueryable <Customer> customers = DB.Customer.AsNoTracking().Where(c => c.CustomerAddress.Any()); QueryRequest queryInfo = this.GetCurrentQueryInfo(); int?totalCount = queryInfo.pageIndex == 0 ? 0 : (int?)null; // calculate totalCount only when we fetch first page (to speed up query) PerformQueryResult <Customer> custQueryResult = this.PerformQuery(customers, queryInfo.pageIndex == 0 ? (countQuery) => countQuery.CountAsync() : (Func <IQueryable <Customer>, Task <int> >)null); List <Customer> custList = await custQueryResult.Data.ToListAsync(); // only execute total counting if we got full page size of rows, preventing unneeded database call to count total if (queryInfo.pageIndex == 0 && custList.Any()) { int cnt = custList.Count; if (cnt < queryInfo.pageSize) { totalCount = cnt; } else { totalCount = await custQueryResult.CountAsync(); } } var custAddressesList = await(from cust in custQueryResult.Data from custAddr in cust.CustomerAddress join addr in DB.Address on custAddr.AddressId equals addr.AddressId select new { CustomerId = custAddr.CustomerId, ID = addr.AddressId, Line1 = addr.AddressLine1, Line2 = addr.AddressLine2, City = addr.City, Region = addr.CountryRegion }).ToListAsync(); var custAddressesLookup = custAddressesList.ToLookup((addr) => addr.CustomerId); // since i create JSON Data myself because there's no entity in db // which has json data in its fields IEnumerable <CustomerJSON> res = custList.Select(c => new CustomerJSON() { CustomerId = c.CustomerId, Rowguid = c.Rowguid, // serialize to json Data = Serializer.Serialize(new { Title = c.Title, CompanyName = c.CompanyName, SalesPerson = c.SalesPerson, ModifiedDate = c.ModifiedDate, Level1 = new { FirstName = c.CustomerName.FirstName, MiddleName = c.CustomerName.MiddleName, LastName = c.CustomerName.LastName, // another level of nesting to make it more complex Level2 = new { EmailAddress = c.CustomerName.Contact.EmailAddress, Phone = c.CustomerName.Contact.Phone } }, Addresses = custAddressesLookup[c.CustomerId].Select(ca => new { ca.Line1, ca.Line2, ca.City, ca.Region }) }) }); return(new QueryResult <CustomerJSON>(res, totalCount)); }