internal EntityCollection <TEntity> Fetch(IDataAccessAdapter adapter, SortExpression sortExpression,
ExcludeIncludeFieldsList excludedIncludedFields,
IPrefetchPath2 prefetchPath, IRelationPredicateBucket predicateBucket,
int pageNumber,
int pageSize, int limit, int cacheTimeInSeconds,
out int totalItemCount)
{
var qf = new QueryFactory();
var q = qf.Create().Select(qf.Create <TEntity>().Where(predicateBucket.PredicateExpression).CountRow());
if (cacheTimeInSeconds > 0)
{
q = q.CacheResultset(cacheTimeInSeconds);
}
totalItemCount = adapter.FetchScalar <int>(q);
var entities = new EntityCollection <TEntity>(new TEntityFactory());
var parameters = new QueryParameters
{
CollectionToFetch = entities,
FilterToUse = predicateBucket.PredicateExpression,
RelationsToUse = predicateBucket.Relations,
SorterToUse = sortExpression,
ExcludedIncludedFields = excludedIncludedFields,
PrefetchPathToUse = prefetchPath,
CacheResultset = cacheTimeInSeconds > 0,
CacheDuration = cacheTimeInSeconds > 0
? TimeSpan.FromSeconds(cacheTimeInSeconds)
: TimeSpan.Zero,
RowsToTake = limit > 0 ? limit : pageSize,
RowsToSkip = limit > 0 ? 0 : pageSize * (pageNumber - 1)
};
adapter.FetchEntityCollection(parameters);
return(entities);
}
/// <summary>
/// Loads the statistics data from the DB into this object.
/// </summary>
/// <returns>filled NWStatistics object</returns>
public void LoadData(IDataAccessAdapter adapter)
{
if (adapter == null)
{
throw new ArgumentNullException("adapter");
}
var qf = new QueryFactory();
this.NumberOfCustomers = adapter.FetchScalar <int>(qf.Create().Select(qf.Customer.CountRow()));
// calculate average order price in the DB.
var avgQ = qf.Create()
.Select(() => new { AvgPrice = qf.Field("OrderPrice").Source("T").Avg().As("AvgPrice").ToValue <Decimal>() })
.From(qf.Create("T")
.Select(OrderDetailFields.OrderId,
(OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).Sum().As("OrderPrice"))
.GroupBy(OrderDetailFields.OrderId));
var avgResults = adapter.FetchQuery(avgQ);
this.AverageOrderPrice = avgResults[0].AvgPrice;
// calculate max, the orderid with that max, the customer of that order in 1 query on the DB using a desc order + limit 1 query.
var maxQ = qf.Create()
.Select(() => new
{
OrderPrice = qf.Field("OrderPrice").Source("T").ToValue <decimal>(),
CustomerId = CustomerFields.CustomerId.ToValue <string>(),
CompanyName = CustomerFields.CompanyName.ToValue <string>()
})
.From(qf.Create("T")
.Select(OrderDetailFields.OrderId,
(OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).Sum().As("OrderPrice"))
.GroupBy(OrderDetailFields.OrderId)
.InnerJoin(qf.Order).On(OrderFields.OrderId == OrderDetailFields.OrderId.Source("T"))
.InnerJoin(qf.Customer).On(CustomerFields.CustomerId == OrderFields.CustomerId))
.OrderBy(qf.Field("OrderPrice").Source("T").Descending())
.Limit(1);
var maxResults = adapter.FetchFirst(maxQ);
this.MaxOrderPrice = maxResults.OrderPrice;
this.MaxOrderCustomerCustomerId = maxResults.CustomerId;
this.MaxOrderCustomerCompanyName = maxResults.CompanyName;
// get number of max orders of one customer + the customerid of that customer in 1 query on the DB.
var mostOrdersQ = qf.Create()
.Select(() => new
{
NumberOfOrders = qf.Field("NumberOfOrders").Source("T").ToValue <int>(),
CustomerId = CustomerFields.CustomerId.ToValue <string>(),
CompanyName = CustomerFields.CompanyName.ToValue <string>()
})
.From(qf.Create("T")
.Select(OrderFields.CustomerId, Functions.CountRow().As("NumberOfOrders"))
.GroupBy(OrderFields.CustomerId)
.InnerJoin(qf.Customer).On(OrderFields.CustomerId.Source("T") == CustomerFields.CustomerId))
.OrderBy(qf.Field("NumberOfOrders").Source("T").Descending())
.Limit(1);
var mostOrdersResults = adapter.FetchFirst(mostOrdersQ);
this.MostOrdersCustomerCustomerId = mostOrdersResults.CustomerId;
this.MostOrdersCustomerCompanyName = mostOrdersResults.CompanyName;
this.MostOrdersForOneCustomer = mostOrdersResults.NumberOfOrders;
}
