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.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 <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 <CustomerAddress> > ReadCustomerAddress()
{
PerformQueryResult <CustomerAddress> res = PerformQuery(null);
return(new QueryResult <CustomerAddress>(await res.Data.ToListAsync(), totalCount: null));
}
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));
}
