Background

In business applications, you often need to write boolean logic for your entities, which are calculated from a variety of data sources such as:

• Data properties of the same object (or database record)
• Data properties of associated objects (or database records)
• Other boolean methods or calculated properties

When deciding how and where to write such logic, you have different choices.

Option A: CLR

The easiest option, is to write the logic as C# code that simply operates on the business object in the CLR. For example:

class Product
{
     ...
     public bool IsAvailable()
     {
         return PublishDate <= DateTime.Today && Quantity > 0 && !Category.IsArchived;
     }
}

Problem: If you want to query the database for available products by using this implementation, first all records have to be fetched from the database and then the method gets evaluated, which can be very slow if you have lots of records in the database:

Database.GetList<Product>(x=> x.IsAvailable())

Option B: Database query expressions

Another option is to write the logic as lambda expression (or even direct SQL) so that the logic gets evaluated in the database and only the matching records are fetched into the application memory:

Database.GetList<Product>(x=> x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived);

Problem 1: The availability logic is hardcoded and you lose the IsAvailable abstraction.

Problem 2: The same filter can be required in multiple other database queries. For example if you want to write another query to fetch available products in a specific category, or with a specific price range, you will have to duplicate the same expression multiple times.

Database.GetList<Product>(x=> x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived);
...
Database.GetList<Product>(x=> x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived) && x.Category = myCategory);
...
Database.GetList<Product>(x=> x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived) && x.Price < 100.00);

In this example you see them next to each other, and might think the duplication is not a big deal. But in reality, each one can be written in a completely different parts of your application code, with no obvious connection between them. It's easy to change one, but miss the other ones. They can go out of sync and cause all sorts of hard-to-debug problems. The more complex the logic, the more problems you will have because of this duplication.

Problem 3: In addition to database queries, you may need the same logic invoked from CLR routine as well. For example in the OnSaved event handler of the Product class, you may need to take an action if the product is Available. So, you will have to write the same logic again, but this time as normal C# code (not a database query lambda expression).

Option C: DatabaseQuery extension methods

In this approach, you define the logic as an extension method on DatabaseQuery<Product>. For example:

public static DatabaseQuery<Product> WhereIsAvailable(this DatabaseQuery<Product> query)
{
     return query.Where(x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived);
}

This way, the availability logic will still be evaluated in the database and so it's fast. Also this allows you to reuse it in multiple database queries using the Database.Of<...>() method. For example:

Database.Of<Product>().WhereIsAvailable();
...
Database.Of<Product>().WhereIsAvailable().Where(x=>x.Category = myCategory);
...
Database.Of<Product>().WhereIsAvailable().Where(x=>x.Price < 100.00);

Therefore, this approach will address problems 1 and 2 from option B above.

Problem: You will still have problem 3 in option B above, where you'll need one implementation that operates on the CLR entity, and one that operates on the database. Although both implementations of the same IsAvailable abstraction will be very similar in many cases, but still this brings you some code duplication.

Geeky note: Note that in the above example, the extension method version of the code is an Expression<Func<Product, bool>> rather than Func<Product, bool> and so you won't be able to run it in CLR without doing a slow dynamic compilation.

Solution: Isolation of duplication

We have all learnt, from day one of programming school, that code duplication is bad. Yet, in this case, it's technically inevitable to have a single implementation that can would run both in CLR and in database.

If we can't avoid duplication, our best option is to neutralize its downside. The real problem with duplication is, of course, the possibility of having out-of-sync logic in the two versions. We should make it almost impossible to happen, which we can achieve by using physical proximity and isolation.

One file per method

To achieve both physical proximity and isolation, we propose to move each method into its own file. For example, for the IsAvailable() method of the Product class, we should have one dedicated file named Product**IsAvailable.cs"". The file name is named after the method, and it's inside a directory named after the entity.

Inside that file, we will have 2 partial classes. One for the CLR version of the code, and one for the SQL version.

namespace Domain
{
     using System;
     using ...;         

    partial class Product
    {
           public bool IsAvailable()
          {
                return PublishDate <= DateTime.Today && Quantity > 0 && !Category.IsArchived;
          }
    }

    partial class DatabaseQueryExtensions
    {
          public static DatabaseQuery<Product> WhereIsAvailable(this DatabaseQuery<Product> query)
         {
              return query.Where(x.PublishDate <= DateTime.Today && x.Quantity > 0 && !x.Category.IsArchived);
         }
    }
}

Since this file has nothing except the two implementations of one concept, it's nearly impossible to change one implementation without noticing (and fixing) the other.

Of course, we're talking about naturally error-prone humans, who can do wonders! But for all intents and purposes, this is as close as we can get to a guaranteed consistency.

Introducing this Visual Studio Extension

You will perhaps already have boolean-based methods written directly in your entity classes. With this extension, you will see an action suggestion appearing next to the method definition reading Promote to file.

When you click it, it will automatically create the new file with the above format and move the method from the original class to that. It will leave the 2nd implementation (database query) empty so you can copy the logic and apply any changes required.