Refining queries in Linq.

The more I play around with Linq, the more it really appeals to me. It really is amazing how powerful it is, and how flexible it can actually make your applications. “Examples” I hear you cry, “give me examples”. Who am I to deny you when you ask so nicely?

Quite often, when an record is deleted in a database, the application design mandates that the record isn’t physically deleted. Instead, it is given a status of Deleted. This means that retrieval of live records needs to take this status into account. Now, in stored procedures you would add a check for the status in each query that you perform against one of these tables. As you can imagine, this quickly becomes very tedious.

So, how can Linq help to make this situation better? Well one of the tricks that you can employ with Linq is refining queries, so you start with a general query and then refine it until you get to a specific query. So, how does this work in practice? Well, take a look at the following query to get all of the records from a table (cunningly called MyTable in this example):

protected virtual IQueryable<MyTable> GetAll() 
{ 
  IQueryable<MyTable> query = from myTable in context.MyTable // The context is the DataContext. 
     select myTable; 
  return query; 
}

Now, as you can see, this query retrieves all of the records from MyTable regardless of status. However, we’ve already stated that we want a query that retrieves records that have been marked as Deleted (in MyTable, there’s a bit field called IsDeleted that states whether or not the record has been deleted). It turns out that it’s really easy to refine these queries. In the next example, we are going to provide methods that refine the original query further and further. We want a method that gets active products, and another method that gets active customers.

protected virtual IQueryable<MyTable> GetAllLiveItems() 
{ 
  IQueryable<MyTable> query = GetAll(); 
  query = from myTable in query 
          where myTable.IsDeleted == false 
          select myTable; 
}  public List<MyTable> GetActiveProducts(string name) 
{ 
  IQueryable<MyTable> query = GetAllLiveItems(); 
  query = from myTable in query 
          where myTable.ProductName == name 
          select myTable; 
  return query.ToList<MyTable>(); 
}  public List<MyTable> GetActiveCustomers(string name) 
{ 
  IQueryable<MyTable> query = GetAllLiveItems(); 
  query = from myTable in query 
          where myTable.CustomerName == name 
          select myTable; 
  return query.ToList<MyTable>(); 
}

I know, the table normalisation sucks big time, but this is intended to highlight how the queries have been refined. As you can see, the GetAllLiveItems method refines the original GetAll query so that it retrieves records that haven’t been deleted. The GetActiveCustomers and GetActiveProducts queries take the query from GetAllLiveItems and refine it further still to return the appropriate list. It’s this ability to refine and extend broad stroke queries that, in my mind, opens Linq up as a real rapid database application development tool.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s