Combining C # code and database code in a specification

Question

Sometimes you need to define some business rules, and a specification template 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 in SQL to improve performance or to handle things like paged record lists.

It remains for me to leave the code for the rules twice, once in the CLR code and once in SQL (or the ORM language).

How are you going to organize such code?

It seems better if the code was stored together in the same class. Thus, if a developer updates business rules, they are less likely to forget 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 pretty ugly to me, as we are mixing problems together now.

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

What do you do?

Answer 1

We used the specification template with the Entity Framework. That's how we got to him

 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 for LINQ-to-Entities, for example

 db.Customers.Where(canBorrowBooksSpec.Predicate); 

In LINQ-to-Objects, for example

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