Optimal Nen Queries, Part 1: Delayed Execution

Nen is  a LINQ provider and as such uses delayed execution. Delayed execution means that whenever a query is written it’s not actually performed until the result set is being accessed, which is often by enumeration. Consider the following:
[code]var workOrdersQuery = DataContext.Current.Get<EventWorkOrder>()
    .Where(t => t.WONumber == 3);    // No query has been performed yet!

var workOrdersCount = workOrdersQuery.Count(); // This runs the query of SELECT COUNT(*)
         FROM slx_EventWorkOrder ....
var workOrdersList = workOrdersQuery.ToList(); // This runs a query joining in all related
 tables and selecting all columns

foreach (var workOrder in workOrdersQuery)   // This is also accessing an enumeration 
of the data set! This performs the same query as ToList()
    Console.WriteLine(workOrder.Subject);
[/code]
As you see in the comments, 3 queries were performed across what is inevitably the same result set. Rather suboptimal. The best idea is to store an enumerated data set. This way the results are stored in memory and the enumerable LINQ provider is used, instead of the Nen data provider which will re-access the database.
[code]
var workOrdersList = DataContext.Current.Get<EventWorkOrder>()
    .Where(t => t.WONumber == 3)
    .ToList();  // Our query is performed and the result set gets stored in memory

var workOrdersCount = workOrdersList.Count();  // The count is performed on the collection
  in memory, not in the database!

foreach (var workOrder in workOrdersList)    // This also does not perform a query, but 
instead  uses the collection from memory
    Console.WriteLine(workOrder.Subject);
[/code]
This can be used to move operations to/from the database. Webservers are often more scalable than database servers, and ordering operations can be costly, so it’s often a good idea for the ordering operation to be performed by the webserver.  This can not be done if a limited subset needs to be returned from the database (using Take/Skip)
[code]
var workOrdersQuery = DataContext.Current.Get<EventWorkOrder>()
    .Where(t => t.WONumber == 3)
    .ToList()   // The query is performed here
    .OrderBy(t => t.Subject)
    .ThenBy(t => t.RequestorName);   // The OrderBy and ThenBy are being performed by
the internal Enumerable LINQ operator, and thusly, happen on the webserver, not the database.
[/code]
Functions which filter data should return an IQueryable<T> to prevent enumeration from occurring prematurely. The following examples never perform queries when called.
[code]
public IQueryable<EventWorkOrder> GetThirdWorkOrders()
{
    return DataContext.Current.Get<EventWorkOrder>().Where(t => t.WONumber == 3);
}

public IQueryable<EventWorkOrder> GetThirdWorkOrders(IQueryable<EventWorkOrder> query)
{
    return query.Where(t => t.WONumber == 3);
}
[/code]
It is always wisest to reduce the number of round trips when ever possible.
[code]
var thirdWorkOrders = DataContext.Current.Get<EventWorkOrder>().Where(t => t.WONumber == 3).ToList();
var fourthWorkOrders = DataContext.Current.Get<EventWorkOrder>().Where(t => t.WONumber == 4).ToList();
[/code]
This will cause two round trips to happen. Optimally, one trip would be performed then they would be split on the web server as such:
[code]
var workOrders = DataContext.Current.Get<EventWorkOrder>().Where(t => t.WONumber == 3 
|| t.WONumber == 4).ToList();
var thirdWorkOrders = workOrders.Where(t => t.WONumber == 3);
var fourthWorkOrders = workOrders.Where(t => t.WONumber == 4);
[/code]
It’s really that simple. For further reading googling the topics of expression trees, delayed exection, and reading up on creating LINQ data providers are all good sources.

Leave a Reply

Your email address will not be published. Required fields are marked *

America's Best Software Engineers, On-Demand, at an Affordable Price
Surge Forward With Us