Querying the Database Using LINQ

The LightSpeed designer declares a strong-typed unit of work class that exposes properties representing queries for different types of entity.  To query the database using LINQ, we need to create a unit of work of this special type, associated with our specified configuration settings.  We can then issue queries against it using the normal LINQ syntax.

Creating a Strong-Typed Unit of Work

To create a strong-typed unit of work, we use the LightSpeedContext<TUnitOfWork> class, where TUnitOfWork is the strong-typed unit of work class, and call CreateUnitOfWork on that context.  Because the unit of work class implements IDisposable, this should normally be done in a using statement.

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

(In future we won’t normally show the LightSpeedContext.  We’ve shown it here because, when you use LINQ, it’s important to remember to use the strong-typed generic version of LightSpeedContext.)

Writing LINQ Queries Using C# Syntax

The strong-typed unit of work exposes properties named after your entities.  You can write LINQ queries against these properties using the built-in C# LINQ syntax.  (The Visual Basic syntax is similar.)

using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  var todaysOrders = from o in unitOfWork.Orders
                     where o.OrderDate >= DateTime.Today
                     select o;
  Console.WriteLine("Number of orders: " + todaysOrders.Count());
  foreach (var order in todaysOrders) Console.WriteLine("Order reference: " + order.OrderReference);
}

When you write a LINQ query against a LightSpeed query property, the query is translated to SQL and executed on the database.  For example, the LINQ where clause is translated to a SQL WHERE clause.  This means processing is efficient – for example, LINQ does not bring back all Order entities and filter them on the client.

Writing LINQ Queries Using the Standard Query Operators

You can also write LINQ queries against the strong-typed unit of work using the LINQ extension methods or standard query operators.

using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  var todaysOrders = unitOfWork.Orders
                               .Where(o => o.OrderDate >= DateTime.Today);
  Console.WriteLine("Number of orders: " + todaysOrders.Count());
  foreach (var order in todaysOrders) Console.WriteLine("Order reference: " + order.OrderReference);
}

Writing LINQ Queries Against Hand-Coded Entities

If you use the visual designer, it creates a strong-typed unit of work class for you.  If you are writing entity classes by hand, you must create the strong-typed LINQ queries yourself.  To do this, call the Query<T> extension method on IUnitOfWork:

using Mindscape.LightSpeed.Linq; // Bring extension methods into scope
// Method 1: Declare your own strong-typed unit of work class
public class StoreUnitOfWork : UnitOfWork
{
  public IQueryable<Order> Orders
  {
    get { return this.Query<Order>(); }
  }
}
// Method 2: Call Query explicitly on a weak-typed unit of work
IUnitOfWork unitOfWork; // weak typed
var todaysOrders = unitOfWork.Query<Order>()
                             .Where(o => o.OrderDate >= DateTime.Today);

Common LINQ Techniques

To filter a query – that is, to tell LightSpeed which entities you want to return – use the where keyword or the Where extension method.

To sort a query, use the orderby keyword or the OrderBy extension method.  Sorting is in ascending order by default: the orderby keyword allows you to specify the descending modifier, which corresponds to the OrderByDescending method.  The orderby keyword supports sorting on multiple attributes; additional attributes correspond to the ThenBy or ThenByDescending method.

var recentOrders = from o in unitOfWork.Orders
                   where o.CustomerId == customerId
                   orderby o.OrderDate descending
                   select o;

To perform paging of a query, use the Skip and Take extension methods.  If you don’t also specify an order, either explicitly in the LINQ query or implicitly on the entity class, Skip and Take order entities by Id.  You can combine Skip and Take if you want to page through a result set.

var ordersToDisplay = unitOfWork.Orders
                                .OrderBy(o => o.OrderDate)
                                .Skip(pageStart)
                                .Take(pageCount);

To work with the entities returned from a LINQ query, use the foreach keyword to iterate over the query, or use the ToList extension method to load the results into a list.

If you are only interested in a single entity, apply the First or Single extension method to obtain it.  First returns the first matching entity, ignoring any others; Single checks that there is only one matching entity.

List<Order> allOrders = unitOfWork.Orders.ToList();
Order order = unitOfWork.Orders.Single(o => o.Id == orderId);

If you want to know how many entities fit the query criteria, apply the Count extension method.  If you want to know if any entities fit the query criteria, apply the Any extension method.

int pendingOrderCount = unitOfWork.Orders
                                  .Where(o => o.Status == OrderStatus.Pending)
                                  .Count();

To perform a projection – that is, to select only a subset of the entity fields – use the select keyword or the Select extension method.  If you perform a projection, then you will typically project into a non-entity type, and the data will not be associated with the unit of work or cached in the identity map, and changes to the object will not be saved when the unit of work is flushed.  This is therefore typically used for presenting partial, read-only information about an entity.

var orderSummaries = from o in unitOfWork.Orders
                     select new { OrderId = o.Id, o.OrderReference };

To bulk remove based on a query use the Remove extension method in the Mindscape.LightSpeed.Linq namespace. This allows Remove by Query to be specified using a simple expression, such as:

using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  unitOfWork.Orders.Remove();
  unitOfWork.SaveChanges();
}
using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{
  unitOfWork.Orders.Where(o => o.Id > 20).Remove();
  unitOfWork.SaveChanges();
}

To target database views you can use the .WithViewName extension method in the Mindscape.LightSpeed.Linq namespace to specify the view which the query is to operate over.

All of these methods are translated to SQL so that LightSpeed does not waste time and bandwidth pulling back unwanted rows or columns.  For example, if you specify Take(5) then LightSpeed will limit the number of rows returned to 5; if you specify Count() then LightSpeed issues a SQL COUNT query rather than materialising entities on the client.

See also Advanced Querying Techniques later in this book.

LINQ Expressions

LINQ allows you to write queries of arbitrary complexity.  LightSpeed handles only queries that can be translated to SQL on the database at hand.  Consequently, if you write complex queries, you may encounter NotSupportedException at runtime.  This indicates that LightSpeed was not able to translate the LINQ query to SQL.  Consider simplifying the query, and performing further operations on the client.  You can use the ToList() and AsEnumerable() operators to partition work between the database and the client.

For known limitations on what LINQ expressions LightSpeed can translate to SQL, see the Appendices.