Querying the Database Using Query Objects

LightSpeed offers another way of querying the database, using query objects.  In the query objects API, instead of writing your query using the LINQ syntax or operators, you construct objects that represent the query specification, and pass them to an appropriate method on IUnitOfWork.  IUnitOfWork also provides some handy overloads to make common queries more convenient.

Creating a Unit of Work

When you use query objects, you don’t need a strong-typed unit of work: you just use the IUnitOfWork base interface.  Consequently, you don’t need to use the generic LightSpeedContext, and can instead use the non-generic base context class.

public class Program
{
  private static readonly LightSpeedContext _context = new LightSpeedContext("Test");
  public static void UseUnitOfWork()
  {
    using (IUnitOfWork unitOfWork = _context.CreateUnitOfWork())
    {
      // Do work here
    }
  }
}

Query Expressions

The most commonly used query object is the QueryExpression object.  You can pass a QueryExpression directly to IUnitOfWork.Find<T> to load entities by criteria, like the LINQ Where operator.

To create a QueryExpression, use the Entity.Attribute static method to represent the attribute you want to query on, then apply comparison operators such as ==, <, and so on.

IUnitOfWork unitOfWork; // weak typed
IList<Order> orders = unitOfWork.Find<Order>( Entity.Attribute("CustomerId") == customerId);

You can combine query expressions using Boolean operators such as && and ||:

IList<Order> orders = unitOfWork.Find<Order>( Entity.Attribute("CustomerId") == customerId && Entity.Attribute("OrderDate") == DateTime.Today);

Query expressions also support the In, Like and Between methods for criteria that can’t be represented using the built-in operators:

IList<Order> orders = unitOfWork.Find<Order>(Entity.Attribute("CustomerId").In(1, 3, 5));

Query expressions support traversal into associated entities using the dot syntax:

IList<Order> orders = unitOfWork.Find<Order>(Entity.Attribute("Customer.Name") == "Bob");

Sorting and Paging

To sort and page a query, pass Order and Page objects to the Find<T> method.  You can construct these objects using static methods and fluent builder methods on the Order and Page classes.

IList<Order> orders = unitOfWork.Find<Order>( Entity.Attribute("CustomerId") == customerId,
  Order.By("OrderDate"),
  Page.Offset(20).LimitTo(10));

Query Objects

In some cases you need to specify additional querying options over and above the criteria, sort order and paging.  In these cases, you must create a Query object and pass this to Find.  The Query object allows you to specify projections, perform full text searches and customise entity load graphs.  Specific functions are covered in the relevant sections of this user guide, or see the Query object in the API reference.

Single Entity Queries

The Find method returns a list of entities.  If you expect a query to return only a single entity, you can use the FindOne method to avoid the overhead of extracting that entity from the list:

Order order = unitOfWork.FindOne<Order>(Entity.Attribute("OrderReference") == orderRef);

If you want to look up an entity by Id, there is a special FindById method:

Order order = unitOfWork.FindById<Order>(123);

Always use FindById for identity lookups, because it tries the lookup in the unit of work’s identity map first, and queries the database only if the lookup fails.  This greatly improves efficiency if the entity is already part of the unit of work.

Count Queries

If you only need to know how many entities meet your query criteria, without bringing back those entities, you can use the Count method instead of Find.

long orderCount = unitOfWork.Count<Order>(new Query( Entity.Attribute("Customer.Name") == "Bob"));

The Count method doesn’t have an overload that allows you to pass a QueryExpression; you must pass a full Query object.  You can pass a QueryExpression in the Query constructor.

Choosing Between LINQ and Query Objects

Because LightSpeed offers both LINQ and Query-based APIs for queries, you may wonder which you should choose.  In general, most developers prefer LINQ, because it is tightly integrated into C# and Visual Basic – for example providing Intellisense support – and is familiar from LINQ to Objects or other data access technologies such as LINQ to SQL.  LINQ also makes it much easier to write group and join queries, and to perform projections through its convenient initialisation and anonymous type syntax.

However, in some cases LINQ’s static nature makes it unsuitable.  For example, if the user can choose a field to filter on, then it may be easier to pass that field name to Entity.Attribute than to construct a LINQ query at run time.  In addition, because LINQ queries are translated into query objects, LINQ incurs a small translation overhead, though this is normally insignificant compared to the cost of the database query.