使用 LINQ-to-Entities 和表达式进行动态查询

引言

我最近一直在开发一个面向服务的 WCF 应用程序，该应用程序将所有数据访问封装在一个服务层中。本文将展示的内容不仅限于 SOA 或 WCF 应用程序，同样可以用于面向对象或组件化的项目，这些项目将其所有数据访问都设计在一个层中，该层的职责仅限于从数据库获取和存储数据。从技术上讲，我使用的是 LINQ-to-Entities，它对后续代码产生了重要影响，因此如果您使用的是其他技术（除了 LINQ-to-SQL），本文可能不适用。

问题

数据访问层中需要解决的有趣问题是检索经过筛选、排序和分页的记录集，以便在 UI 网格中显示。如果您只想按默认方式显示所有数据并进行排序和筛选，那么没有什么特别有趣的。然而，用户通常希望能够指定类似这样的内容：“显示我所有姓氏为‘Smith’、名字为‘John’、当前在系统中处于活动状态的客户。按电子邮件升序排序，然后按上次访问日期降序排序。并且只显示第一页的 10 条记录。”在您应用程序的不同 UI 网格中，您将拥有不同的数据过滤器、排序列和分页条件。在我看来，这是一个应用抽象来得出处理重复逻辑的通用代码的绝佳时机。

安装

我将尽量将其简化到最基本的部分，所以请忽略我实际使用的是 WCF。只需将这些视为检索数据的类，它们存在于任何 .NET 程序集中。另外请注意，我不会讨论 UI 网格的细节，只涉及其通用、显而易见的需要，因为我可能需要支持移动、Web 和 Windows 客户端。

所以，我们假设有一个名为 Customer 的数据库表，其列包括：customerId、email、firstName、lastName、createdDate、lastAccessedDate、isActive、address、city、state、zip，以及您能想象到的其他任何列。通常，您会使用 VS2012 连接到数据库并生成一个 EDMX，该 EDMX 将数据库实体映射到 C# 类型。

我们还假设您不希望将我的 EDMX 实体暴露给数据访问层之外。虽然这需要一些额外的编码，但并不困难，并且能提供数据检索类和数据传输类之间的重要分离。尤其是在 SOA 环境中，您希望将服务之间的载荷最小化，而 LINQ-to-Entities 类在内部相当庞大。因此，您创建一个轻量级的 DTO（数据传输对象）称为 CustomerDto。CustomerDto 只有您希望在 UI 网格中显示的属性。请记住，它的作用只是包含从服务 A 传输到服务 B 的数据。

public class CustomerDto
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    // etc.
}

一个返回 Customer 表中所有记录的简单数据访问方法可以这样实现：

public CustomerDto[] SelectCustomers()
{
    using (var context = new MyContext())
    {
        var dbCustomers = context.Customers;
        var dtoCustomers = dbCustomers.ToDto();
        return dtoCustomers;
    }
}

好的，我在这里嵌入了一个我喜欢使用的技巧，用于封装对象转换并使我的代码更简洁易读。ToDto() 只是我编写的一个扩展方法，用于扩展 Customer[] 类型。该方法简单地创建一个 CustomerDto[]，并将每个 Customer 转换为一个类似的 CustomerDto。

public static CustomerDto[] ToDto(this Customer[] dbCustomers)
{
    var customerDtos = new CustomerDto[dbCustomers.Length];
    for (var i = 0; i < dbCustomers.Length; i++)
    {
        var currentDbCustomer = dbCustomer[i];
        var customerDto = new CustomerDto
        {
            FirstName = currentDbCustomer.firstName,
            LastName = currentDbCustomer.lastName,
            IsActive = currentDbCustomer.isActive
            // etc.
        }
        customerDtos[i] = customerDto;
    }
    return customerDto;
}

此时，代码只是检索了表中所有客户，并按默认顺序排列，这对于大量数据来说将带来糟糕的性能影响，对于希望高效完成工作的用户来说，则会带来糟糕的用户体验。

添加筛选、排序和分页

显然 SelectCustomers() 需要一些参数来指定要选择哪些客户，而且我们知道我们想要排序和分页，因此似乎我们也想将这些作为参数来指定。从 SQL 的角度考虑，我希望传入 WHERE 和 ORDER BY 的值，以及一个只获取任意行块的 SELECT。在 LINQ 中，这些分别转换为 Where()、OrderBy()、Skip() 和 Take()。有很多方法可以实现这一点，但我选择将签名修改为如下：

public CustomerDto[] SelectCustomers(
       CustomerSearchCriteriaDto searchCriteria, 
       SortingPagingDto sortingPaging)
{
    // More to come.
}

对于任何 UI 网格，排序和分页的要求几乎都是相同的，所以我创建了一个 DTO（在我所有的 SOA 中，任何在服务之间移动数据的类都可以称为 DTO），名为 SortingPagingDto，它只是：

public class SortingPagingDto
{
    public SortOrderDto[] SortOrders { get; set; }
    public int PageNumber { get; set; }
    public int NumberRecords { get; set; }
}
public class SortOrderDto
{
    public enum SortOrder
    {
        Ascending,
        Descending
    }
    public string ColumnName { get; set; }
    public SortOrder ColumnOrder { get; set; }
}

在考虑设计时，我考虑到 WHERE 子句就是一系列列名，带有期望的值或值的范围。以下仅处理单个值，将其扩展到值的范围留给读者练习（我一直想说“留给读者练习”）。对于给定的 UI 网格，我设想有一种方法可以指定用户想要的客户过滤器：“名字类似于‘john’，电子邮件类似于‘@gmail.com’，等等。”因此，如果我创建 CustomerSearchCriteriaDto，所有字段都为可空类型，我可以逐个字段检查并确定是否使用它进行过滤。这很明显，但这是那个 DTO：

public class CustomerSearchCriteriaDto
{
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public bool? IsActive {get; set;}
    public DateTime? CreatedDate {get; set;}
    // etc.
}

现在，我已经准备好使用 LINQ-to-Entities 创建动态筛选、排序和分页的记录集了。或者还没有？

两个有用的实用工具

有两个出色的实用代码可以方便地为 Entity Framework 生成动态的 Where() 和 OrderBy() 方法。首先是 **LinqKit**，您可以在此处阅读其优点：http://www.albahari.com/nutshell/predicatebuilder.aspx PredicateBuilder 是 LinqKit NuGet 包的一部分，一个简单的类，它允许您通过“and”和“or”来动态构建 WHERE 子句。同样，**Marc Gravell** 在此处发布了一些对 OrderBy、OrderByDescending、ThenBy 和 ThenByDescending 的扩展：http://stackoverflow.com/questions/41244/dynamic-linq-orderby，这使得可以轻松地即时构建“order by”子句。

第一阶段完成

使用 LinqKit 和 Marc Gravell 的扩展，已经足以构建一个功能性的实现。

public CustomerDto[] SelectCustomers(CustomerSearchCriteriaDto 
       searchCriteria, SortingPagingDto sortingPaging)
{
    using (var context = new MyContext())
    {
        var predicate = PredicateBuilder.True<Customer>();
        if (!string.IsNullOrWhiteSpace(searchCriteria.FirstName))
        {
            predicate = predicate.And(x => x.firstName.Contains(searchCriteria.FirstName));
        }
        if (!string.IsNullOrWhiteSpace(searchCriteria.LastName))
        {
            predicate = predicate.And(x => x.lastName.Contains(searchCriteria.LastName));
        }
        if (searchCriteria.IsActive != null)
        {
            predicate = predicate.And(x => x.isActive == searchCriteria.IsActive);
        }
        // etc., etc. with all criteria

        var query = context.Customers.AsExpandable().Where(predicate) as IOrderedQueryable<Customer>;

        var firstPass = true;
        foreach (var sortOrder in sortingPaging.SortOrders)
        {
            if (firstPass)
            {
                firstPass = false;
                query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending 
                    ? query.OrderBy(sortOrder.ColumnName) : 
                      query.OrderByDescending(sortOrder.ColumnName);
            }
            else
            {
                query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
                    ? query.ThenBy(sortOrder.ColumnName) : 
                      query.ThenByDescending(sortOrder.ColumnName);
            }
        }

        var dbCustomers = query.Skip((sortingPaging.PageNumber - 1)*
          sortingPaging.NumberRecords).Take(sortingPaging.NumberRecords).ToArray();
        var customerDtos = dbCustomers.ToDto();

        return customerDtos;
    }
}

这工作得很棒，但打了太多字。我立刻发现代码中有两处问题。最明显的问题是排序和分页将是所有选择操作共有的。我提前知道了这一点，这就是为什么创建 SortingPagingDto。应该可以轻松地将这部分提取到一个我可以重用的公共例程中。另一件让我眼前一亮的是代码前半部分的**模式**，即一种启发式方法，如“如果指定了搜索条件，就将其应用于谓词”，如此反复。在我看来，这违反了 DRY 原则：不要重复自己。

简单部分：排序和分页

将排序和分页提取到扩展方法中没有什么难度，但为了文章的完整性，下面展示了实现。

public static T[] ApplySortingPaging<T>(this IOrderedQueryable<T> query, SortingPagingDto sortingPaging)
{
    var firstPass = true;
    foreach (var sortOrder in sortingPaging.SortOrders)
    {
        if (firstPass)
        {
            firstPass = false;
            query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
                        ? query.OrderBy(sortOrder.ColumnName) : 
                          query.OrderByDescending(sortOrder.ColumnName);
        }
        else
        {
            query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
                        ? query.ThenBy(sortOrder.ColumnName) : 
                          query.ThenByDescending(sortOrder.ColumnName);
        }
    }

    var result = query.Skip((sortingPaging.PageNumber - 1)*
      sortingPaging.NumberRecords).Take(sortingPaging.NumberRecords).ToArray();

    return result;
}

它是通用的，因此我可以将其重用于 Customers、Orders、Schools、Busses 等等。真正让我高兴的是，与 ToDto() 扩展类似，我将所有这些代码都简化为一个方法调用。

var dbCustomers = query.ApplySortingPaging(sortingPaging);

困难部分：动态 Where

这花了我整整一天的时间来弄清楚，阅读文档，浏览 stackoverflow.com，调试等等。当我成功时，我非常兴奋，因此有了这篇文章。如果您是 Expression 的专家，这可能很明显，但我不算是，对我来说，理解这一点非常费脑筋。同样，基本问题是采用重复的逻辑并将其抽象为通用重用。逻辑基本上是：“我是否有值，如果有，就将其应用于谓词。”在某些情况下，“应用于谓词”意味着对字符串执行 Contains()，但它可能意味着对布尔值执行 Equals()。同样，日期范围或数字小于/大于等操作留给读者练习。

我最初解决这个问题的方法是将“if-not-null-apply”代码块减少为一行，但我仍然为每个要应用的过滤器都有一行，这意味着可能有 20 行几乎相同的代码。我真的很希望谓词构建减少到**一行**。可能吗？当然可以！

下面的代码有相当详细的注释，但我会稍作解释。其思想是获取一个给定的 SearchCriteriaDto（即一个只有可空字段的类，其中非空字段表示“应用这些字段”）并为数据库类型（即表）构建一个谓词。在这种情况下，我认为用户指定的条件越多，他们想要返回的结果就越少。也就是说，名字类似于“John”且姓氏类似于“Smith”。您可能希望构建一个类似的例程来执行“or”操作，作为进一步的练习。然后，它涉及遍历 SearchCriteriaDto 的每个属性，并根据属性的类型处理该属性。

为了简化工作，我还做了另一件事，因为我并不总是同意数据库构建工程师使用的命名约定，那就是允许将 SearchCriteriaDto 的属性映射到 EF/数据库字段。我通过一个自定义属性来实现这一点，当需要时，我可以将其应用于 SearchCriteriaDto 的每个属性。GetDbFieldName() 方法会检查该属性，如果未指定该属性，则使用属性名本身。

剩下的… 嗯，这只是 Expression 的魔术。在 Visual Studio 中键入“Expression[dot]”，您会看到所有 IntelliSense。我完全不知道。BuildPredicate() 使用反射来获取有关类型和字段的信息，而 Apply 方法主要依赖于 Expression 参数、成员访问器、常量、调用和 lambda 表达式。

private static readonly MethodInfo StringContainsMethod = 
  typeof (string).GetMethod(@"Contains", BindingFlags.Instance | 
  BindingFlags.Public, null, new[] {typeof (string)}, null);
private static readonly MethodInfo BooleanEqualsMethod = 
  typeof (bool).GetMethod(@"Equals", BindingFlags.Instance | 
  BindingFlags.Public, null, new[] {typeof (bool)}, null);

public static Expression<Func<TDbType, bool>> 
  BuildPredicate<TDbType, TSearchCriteria>(TSearchCriteria searchCriteria)
{
    var predicate = PredicateBuilder.True<TDbType>();

    // Iterate the search criteria properties
    var searchCriteriaPropertyInfos = searchCriteria.GetType().GetProperties();
    foreach (var searchCriteriaPropertyInfo in searchCriteriaPropertyInfos)
    {
        // Get the name of the DB field, which may not be the same as the property name.
        var dbFieldName = GetDbFieldName(searchCriteriaPropertyInfo);
        // Get the target DB type (table)
        var dbType = typeof (TDbType);
        // Get a MemberInfo for the type's field (ignoring case
        // so "FirstName" works as well as "firstName")
        var dbFieldMemberInfo = dbType.GetMember(dbFieldName, 
            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance).Single();
        // STRINGS
        if (searchCriteriaPropertyInfo.PropertyType == typeof (string))
        {
            predicate = ApplyStringCriterion(searchCriteria, 
              searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
        }
        // BOOLEANS
        else if (searchCriteriaPropertyInfo.PropertyType == typeof (bool?))
        {
            predicate = ApplyBoolCriterion(searchCriteria, 
              searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
        }
        // ADD MORE TYPES...
    }

    return predicate;
}

private static Expression<Func<TDbType, bool>> ApplyStringCriterion<TDbType, 
    TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo, 
    Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
{
    // Check if a search criterion was provided
    var searchString = searchCriterionPropertyInfo.GetValue(searchCriteria) as string;
    if (string.IsNullOrWhiteSpace(searchString))
    {
        return predicate;
    }
    // Then "and" it to the predicate.
    // e.g. predicate = predicate.And(x => x.firstName.Contains(searchCriterion.FirstName)); ...
    // Create an "x" as TDbType
    var dbTypeParameter = Expression.Parameter(dbType, @"x");
    // Get at x.firstName
    var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
    // Create the criterion as a constant
    var criterionConstant = new Expression[] {Expression.Constant(searchString)};
    // Create the MethodCallExpression like x.firstName.Contains(criterion)
    var containsCall = Expression.Call(dbFieldMember, StringContainsMethod, criterionConstant);
    // Create a lambda like x => x.firstName.Contains(criterion)
    var lambda = Expression.Lambda(containsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
    // Apply!
    return predicate.And(lambda);
}

private static Expression<Func<TDbType, bool>> ApplyBoolCriterion<TDbType, 
  TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo, 
  Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
{
    // Check if a search criterion was provided
    var searchBool = searchCriterionPropertyInfo.GetValue(searchCriteria) as bool?;
    if (searchBool == null)
    {
        return predicate;
    }
    // Then "and" it to the predicate.
    // e.g. predicate = predicate.And(x => x.isActive.Contains(searchCriterion.IsActive)); ...
    // Create an "x" as TDbType
    var dbTypeParameter = Expression.Parameter(dbType, @"x");
    // Get at x.isActive
    var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
    // Create the criterion as a constant
    var criterionConstant = new Expression[] {Expression.Constant(searchBool)};
    // Create the MethodCallExpression like x.isActive.Equals(criterion)
    var equalsCall = Expression.Call(dbFieldMember, BooleanEqualsMethod, criterionConstant);
    // Create a lambda like x => x.isActive.Equals(criterion)
    var lambda = Expression.Lambda(equalsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
    // Apply!
    return predicate.And(lambda);
}

private static string GetDbFieldName(PropertyInfo propertyInfo)
{
    var fieldMapAttribute = 
         propertyInfo.GetCustomAttributes(typeof (FieldMapAttribute), false).FirstOrDefault();
    var dbFieldName = fieldMapAttribute != null ? 
            ((FieldMapAttribute) fieldMapAttribute).Field : propertyInfo.Name;
    return dbFieldName;
}

就这样！它很容易扩展到其他情况，现在数据访问选择实现被减少到几行代码，其中大部分是高度可重用的。

public CustomerDto[] SelectCustomer(CustomerSearchCriteriaDto searchCriteria, 
                     SortingPagingDto sortingPaging)
{
    using (var context = new MyContext())
    {
        var predicate = ExpressionExtensions.BuildPredicate<Customer, 
                               CustomerSearchCriteriaDto>(searchCriteria);

        var query = context.Customers.AsExpandable().Where(predicate) 
                            as IOrderedQueryable<Customer>;

        var dbCustomers = query.ApplySortingPaging(sortingPaging);

        var customerDtos = dbCustomers.ToDto();

        return customerDtos;
    }
}