在规范中组合C#代码和数据库代码 [英] Combining C# code and database code in a Specification

问题描述

有时您需要定义一些业务规则，规范"模式是一个有用的工具.例如:

 公共类CanBorrowBooksSpec:ISpecification< Customer>{公共布尔满意度(客户){回头客..HasLibraryCard&&！customer.UnpaidFines.Any();}} 

但是，我经常发现我需要将这些规则推入" SQL以提高性能或满足诸如分页记录列表之类的事情.

然后我不得不为规则编写两次代码，一次使用CLR代码，一次使用SQL(或ORM语言).

如何组织这样的代码?

最好将代码放在同一个类中.这样，如果开发人员正在更新业务规则，则他们忘记更新两组代码的机会就更少.例如:

 公共类CanBorrowBooksSpec:ISpecification< Customer>{公共布尔满意度(客户){回头客..HasLibraryCard&&！customer.UnpaidFines.Any();}公共无效AddSql(StringBuilder sql){sql.Append(@"customer.HasLibraryCard并且不存在(从CustomerUnpaidFines中选择的ID，其中CustomerId = customer.Id))；}} 

但是，这对我来说似乎很丑陋，因为我们现在正将各种疑虑混在一起.

另一种替代方法是使用Linq-To-YourORM解决方案，因为LINQ代码可以针对集合运行，也可以转换为SQL.但是我发现，除了最琐碎的情况外，这种解决方案几乎不可能实现.

你做什么?

解决方案

我们在实体框架中使用了规范模式.这是我们的处理方法

 公共接口ISpecification< TEntity>{表达式< Func< TEntity，bool>谓词{get;}}公共类CanBorrowBooksSpec:ISpecification< Customer>{表达式< Func< Customer，bool>谓词{得到{退货客户=>客户.有图书馆卡&&！customer.UnpaidFines.Any()}}} 

然后您可以将其用于LINQ-to-Entities之类的

  db.Customers.Where(canBorrowBooksSpec.Predicate); 

在LINQ-to-Objects中

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

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?

解决方案

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());

这篇关于在规范中组合C#代码和数据库代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！