将LINQ查询封装在导航属性中以供重复使用？ [英] Encapsulating LINQ queries in navigation properties for re-use?

问题描述

我正在SQL Server上使用Entity Framework Code First，并具有与以下类似的域实体：

I'm using Entity Framework Code First with SQL Server, with a domain entity that is similar to this:

public class Item
{
  public ICollection<ItemLocation> ItemLocations { get; set; }
}

一个项目在整个生命周期中都可以分配到多个位置，但只能分配一个可以随时处于活动状态，我们使用它来获取商品的实际位置：

An item can be assigned to many locations throughout it's life, but only one can be active at any time, and we use this to get the actual location of the item:

public Location
{
  get
  {
    return ItemLocations.Where(x => x.IsActive).Select(x => x.Location).FirstOrDefault()
  }
}

如果加载整个项目对象，此属性将按预期工作：

This property works as expected if I load the entire item object:

var item = (from i in db.Items select i).FirstOrDefault();
Console.WriteLine(item.Location.Name);

但是，我无法在需要返回匿名类型的LINQ查询中使用它，像这样：

However, I can't use this in my LINQ queries where I need to return an anonymous type, like this:

var items = from i in db.Items
            select new
                   {
                     ItemId = i.ItemId,
                     LocationName = i.Location.Name
                   };

相反，我每次都必须使用完整查询：

Instead, I have to use the full query every time:

var items = from i in db.Items
            select new
                   {
                     ItemId = i.ItemId,
                     LocationName = i.ItemLocations.Where(x => x.IsActive).Select(x => x.Location).FirstOrDefault().Name
                   };

理想情况下，我想保留在一个地方检索项目位置的逻辑（例如属性），而不必将它们分散在各处。

Ideally, I'd like to keep the logic for retrieving an item location in one place (like a property), rather than having to scatter these all over the place.

实现此目标的最佳方法是什么？

What is the best way to achieve this?

推荐答案

所以首先，如果我们希望能够将此子查询与另一个查询结合，则需要定义它作为 Expression 对象，而不是C＃代码。如果已将其编译为IL代码，则查询提供程序将无法对其进行检查以查看执行了哪些操作并将其转换为SQL代码。创建表示此操作的表达式很容易：

So to start with, if we want to be able to combine this sub-query with another query then we need to define it as an Expression object, rather than as C# code. If it has already been compiled into IL code then the query provider cannot inspect it to look at what operations are performed and translate that into SQL code. Creating an Expression representing this operation is easy enough:

public static readonly Expression<Func<Item, ItemLocation>> LocationSelector =
    item => item.ItemLocations.Where(x => x.IsActive)
            .Select(x => x.Location)
            .FirstOrDefault();

现在，我们有一个表达式可以从某个项目中获取位置，我们需要将其与您的使用此位置从项目中选择匿名对象的自定义表达式。为此，我们需要一个 Combine 方法，该方法可以使用一个表达式将一个对象选择到另一个对象中，还可以使用另一个表达式将原始对象作为对象的结果。第一个表达式，并计算新结果：

Now that we have an expression to get a location from an item, we need to combine that with your custom expression for selecting out an anonymous object from an item, using this location. To do this we'll need a Combine method that can take one expression selecting an object into another object, as well as another expression that takes the original object, the result of the first expression, and computes a new result:

public static Expression<Func<TFirstParam, TResult>>
    Combine<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TFirstParam, TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], param)
        .Replace(second.Parameters[1], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

在内部，这仅将第二个表达式的参数的所有实例替换为第一的主体；其余代码只是在整个过程中确保单个参数并将结果包装回新的lambda中。此代码取决于将一个表达式的所有实例替换为另一个表达式的能力，我们可以使用以下方式进行操作：

Internally, this simply replaces all instances of the parameter of the second expression with the body of the first; the rest of the code is simply ensuring a single parameter throughout and wrapping the result back into a new lambda. This code depends on the ability to replace all instances of one expression with another, which we can do using:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

现在我们有了组合方法，我们需要做的就是调用它：

Now that we have our Combine method all we need to do is call it:

db.Items.Select(Item.LocationSelector.Combine((item, location) => new
    {
        ItemId = item.ItemId,
        LocationName = location.Name
    }));

还有瞧。

如果我们想要，我们可以打印出调用 Combine 生成的表达式，而不必将其传递给 Select 。这样做，它会打印出：

If we wanted, we could print out the expression generated by the call to Combine instead of passing it to Select. Doing that, it prints out:

param => new <>f__AnonymousType3`2(ItemId = param.ItemId, 
    LocationName = param.ItemLocations.Where(x => x.IsActive)
    .Select(x => x.Location).FirstOrDefault().Name)

（由我自己添加空格）

这正是您手动指定的查询，但是在这里，我们将重复使用现有的子查询，而不必每次都键入它。

That is exactly the query that you had specified out manually, however here we're re-using the existing sub-query without needing to type it out every single time.

这篇关于将LINQ查询封装在导航属性中以供重复使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！