Combining C# code and database code in a Specification

Question

Sometimes you need to define some business rules and the Specification pattern is a useful tool. For example:

public class CanBorrowBooksSpec : ISpecification<Customer>
{
    public bool Satisfies(Customer customer)
    {
         return customer.HasLibraryCard
              && !customer.UnpaidFines.Any();
    }
}

However, I often find that I need to 'push' these rules into SQL to improve performance or to cater for things like paged lists of records.

I am then left with having to write code for the rules twice, once in CLR code, and once in SQL (or ORM language).

How do you go about organising code like this?

It seems best if the code was kept together in the same class. That way, if the developer is updating the business rules they have less chance of forgetting to update both sets of code. For example:

public class CanBorrowBooksSpec : ISpecification<Customer>
{
    public bool Satisfies(Customer customer)
    {
         return customer.HasLibraryCard
              && !customer.UnpaidFines.Any();
    }

    public void AddSql(StringBuilder sql)
    {
        sql.Append(@"customer.HasLibraryCard 
                     AND NOT EXISTS (SELECT Id FROM CustomerUnpaidFines WHERE CustomerId = customer.Id)");
    }
}

However this seems quite ugly to me as we are now mixing concerns together.

Another alternative would be using a Linq-To-YourORM solution, as the LINQ code could either be run against a collection, or it could be translated into SQL. But I have found that such solutions are rarely possible in anything but the most trivial scenarios.

What do you do?

Autobiography

Related post - Entity Framework Specification Pattern Implementation – Perspicacious

Answer 1

We used Specification pattern with Entity Framework. Here's how we approached it

public interface ISpecification<TEntity>
{
    Expression<Func<TEntity, bool>> Predicate { get; }
}


public class CanBorrowBooksSpec : ISpecification<Customer>
{
    Expression<Func<Customer, bool>> Predicate 
    { 
       get{ return customer => customer.HasLibraryCard
              && !customer.UnpaidFines.Any()} 
    }
}

Then you can use it against LINQ-to-Entities like

db.Customers.Where(canBorrowBooksSpec.Predicate);

In LINQ-to-Objects like

customerCollection.Where(canBorrowBooksSpec.Predicate.Compile());

Syverson

Basically, you should have a linq specification equivalence. – Tomfoolery

OK, yes that works as long as your ORM can handle the required LINQ specification. I guess it depends on the ORM, but this can be hard. – Autobiography