Home » Blog

LINQ queries over a LightSpeed EntityCollection

Tagged as LightSpeed

In LightSpeed, you can define a LINQ query over the entire database using the Query method, or more conveniently using a strong-typed unit of work. LightSpeed pulls back only the entities or aggregates (e.g. count or sum) that you need.

Suppose, though, that you have an entity, say a Customer, and this entity has a child collection, say Orders. Loading that child collection is an all-or-nothing thing: as soon as you reference the Orders property, LightSpeed goes and fetches all the orders in the database associated with the customer in question. What if you just want to get the top ten orders by value? Or the number of orders? Of course, one way is to figure out a suitable database query:

int orderCount = unitOfWork.Orders.Where(o => o.CustomerId == customer.Id).Count();

With recent nightly builds, however, we’ve added a new LINQ Query method that encapsulates this for you. To use this, you apply the Query method to an entity, passing an expression representing the collection:

IQueryable<Order> orders = customer.Query(c => c.Orders);

This doesn’t load the Orders collection: like the normal LINQ Query methods, it just sets up a query. So now you can write:

int orderCount = orders.Count();

This will perform a COUNT query against the database; you still don’t incur the overhead of loading the Orders collection. Similarly, this query:

List<Order> topTenByValue = orders.OrderByDescending(o => o.Value).Take(10).ToList();

retrieves only the ten orders specified.

If you want to add or remove orders, or you want to data bind to the Orders collection, you’ll still have to reference the Orders property and load all the customer’s orders. But if all you want to do is perform queries against the child collection, this enables you to do so more efficiently.

I like queries. How can I make the syntax nicer?

You might find that expression syntax a bit cluttered. If you’re planning to make significant use of this feature, you’ll probably want to encapsulate it in a nice property or extension method. For example, you could write an extension method:

public static class CustomerExtensions
{
  public static IQueryable<Order> Orders(this Customer customer)
  {
    return customer.Query(c => c.Orders);
  }
}

and then you could perform customer order queries like this:

int orderCount = customer.Orders().Count();
List<Order> topTenByValue = customer.Orders().OrderByDescending(o => o.Value).Take(10).ToList();

Notice the brackets after Orders(): this tells C# to call the Orders() extension method rather than accessing the Orders property.

Take it for a spin

Want to try it out? Grab the latest build of the free LightSpeed Express Edition, or if you’re a licensed customer download the nightly from the store, and give it a go!

Add your comments
Posted by Ivan Towlson on 30 August 2009

Leave a Reply