C# LINQ to entity - 构建 where 子句以测试多对多关系中的集合

LINQ to entities - Building where clauses to test collections within a many to many relationship

提问by Phobis

So, I am using the Linq entity framework. I have 2 entities: Contentand Tag. They are in a many-to-many relationship with one another. Contentcan have many Tagsand Tagcan have many Contents. So I am trying to write a query to select all contents where any tags names are equal to blah

所以，我使用的是 Linq 实体框架。我有 2 个实体：Content和Tag. 它们之间是多对多的关系。Content可以有很多Tags，Tag也可以有很多Contents。所以我想写一个查询来选择所有标签名称等于的所有内容blah

The entities both have a collection of the other entity as a property(but no IDs). This is where I am struggling. I do have a custom expression for Contains(so, whoever may help me, you can assume that I can do a "contains" for a collection). I got this expression from: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2670710&SiteID=1

这两个实体都将另一个实体的集合作为属性（但没有 ID）。这是我挣扎的地方。我确实有一个自定义表达式Contains（因此，无论谁可以帮助我，您都可以假设我可以为集合执行“包含”）。我从这里得到这个表达式：http: //forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2670710&SiteID=1

Edit 1

编辑 1

I ended up finding my own answer.

我最终找到了自己的答案。

采纳答案by Phobis

After reading about the PredicateBuilder, reading all of the wonderful posts that people sent to me, posting on other sites, and then reading more on Combining Predicatesand Canonical Function Mapping.. oh and I picked up a bit from Calling functions in LINQ queries(some of these classes were taken from these pages).

在阅读了PredicateBuilder 之后，阅读了人们发送给我、发布在其他网站上的所有精彩帖子，然后阅读了更多关于Combining Predicates和Canonical Function Mapping 的内容……哦，我从LINQ 查询中的调用函数中学到了一些东西（其中一些课程取自这些页面）。

I FINALLY have a solution!!! Though there is a piece that is a bit hacked...

我终于有办法了！！！虽然有一块有点被黑了......

Let's get the hacked piece over with :(

让我们用 :(

I had to use reflector and copy the ExpressionVisitor class that is marked as internal. I then had to make some minor changes to it, to get it to work. I had to create two exceptions (because it was newing internal exceptions. I also had to change the ReadOnlyCollection() method's return from:

我不得不使用反射器并复制标记为内部的 ExpressionVisitor 类。然后我不得不对其进行一些小的更改，以使其正常工作。我必须创建两个异常（因为它是新的内部异常。我还必须更改 ReadOnlyCollection() 方法的返回值：

return sequence.ToReadOnlyCollection<Expression>();

To:

到：

return sequence.AsReadOnly();

I would post the class, but it is quite large and I don't want to clutter this post any more than it's already going to be. I hope that in the future that class can be removed from my library and that Microsoft will make it public. Moving on...

我会发布这门课，但它很大，我不想把这篇文章弄得比它本来就更杂乱。我希望将来可以从我的库中删除该类，并且 Microsoft 会将其公开。继续...

I added a ParameterRebinder class:

我添加了一个 ParameterRebinder 类：

public class ParameterRebinder : ExpressionVisitor {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) {
            return new ParameterRebinder(map).Visit(exp);
        }

        internal override Expression VisitParameter(ParameterExpression p) {
            ParameterExpression replacement;
            if (map.TryGetValue(p, out replacement)) {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }

Then I added a ExpressionExtensions class:

然后我添加了一个 ExpressionExtensions 类：

public static class ExpressionExtensions {
        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) {
            // build parameter map (from parameters of second to parameters of first)
            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression 
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {
            return first.Compose(second, Expression.And);
        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {
            return first.Compose(second, Expression.Or);
        }
    }

And the last class I added was PredicateBuilder:

我添加的最后一个类是 PredicateBuilder：

public static class PredicateBuilder {
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

}

This is my result... I was able to execute this code and get back the resulting "content" entities that have matching "tag" entities from the tags that I was searching for!

这是我的结果......我能够执行此代码并从我正在搜索的标签中取回具有匹配“标签”实体的结果“内容”实体！

    public static IList<Content> GetAllContentByTags(IList<Tag> tags) {
        IQueryable<Content> contentQuery = ...

        Expression<Func<Content, bool>> predicate = PredicateBuilder.False<Content>();

        foreach (Tag individualTag in tags) {
            Tag tagParameter = individualTag;
            predicate = predicate.Or(p => p.Tags.Any(tag => tag.Name.Equals(tagParameter.Name)));
        }

        IQueryable<Content> resultExpressions = contentQuery.Where(predicate);

        return resultExpressions.ToList();
    }

Please let me know if anyone needs help with this same thing, if you would like me to send you files for this, or just need more info.

请让我知道是否有人需要这方面的帮助，如果您希望我为此向您发送文件，或者只是需要更多信息。

回答by chakrit

tags.Select(testTag => testTag.Name)

Where does the tags variable gets initialized from? What is it?

标签变量从哪里初始化？它是什么？

回答by Alex Lyman

This is what the question itself asks for:

这就是问题本身所要求的：

contentQuery.Where(
    content => content.Tags.Any(tag => tag.Name == "blah")
);

I'm not sure what the thought process was to get to the questioner's code, really, and I'm not entirely sure exactly what its really doing. The one thing I'm really sure of is that .AsQueryable() call is completely unnecessary -- either .Tags is already an IQueryable, or the .AsQueryable() is just going to fake it for you -- adding extra calls in where there doesn't need to be any.

我不确定要获得提问者的代码的思考过程是什么，真的，我也不完全确定它到底在做什么。我真正确定的一件事是 .AsQueryable() 调用是完全没有必要的——要么 .Tags 已经是 IQueryable，要么 .AsQueryable() 只是为你伪造它——在 where 添加额外的调用不需要任何。

回答by chakrit

Summing it up...

总结一下...

contentQuery.Where(
    content => content.Tags.Any(tag => tags.Any(t => t.Name == tag.Name))
);

So is that what you're expecting?

那是你期待的吗？

I'm a little confused.

我有点困惑。

回答by Alex Lyman

NOTE: please edit the question itself, rather than replying with an answer -- this is not a discussion thread, and they can re-order themselves at any time

注意：请编辑问题本身，而不是回复一个答案——这不是一个讨论线程，他们可以随时重新排序

If you're searching for all Contents that are marked with any one of a set of tags:

如果您要搜索标有一组标签中的任何一个的所有内容：

IEnumerable<Tag> otherTags;
...
var query = from content in contentQuery
            where content.Tags.Intersection(otherTags).Any()
            select content;

It looks like you might be using LINQ To SQL, in which case it might be better if you write a stored procedure to do this one: using LINQ to do this will probably not run on SQL Server -- it's very likely it will try to pull down everything from contentQueryand fetch all the .Tagscollections. I'd have to actually set up a server to check that, though.

看起来您可能正在使用 LINQ To SQL，在这种情况下，如果您编写一个存储过程来执行此操作可能会更好：使用 LINQ 执行此操作可能不会在 SQL Server 上运行——它很可能会尝试拉下所有内容contentQuery并获取所有.Tags集合。不过，我必须实际设置一个服务器来检查。

回答by Alex Lyman

The error is related to the 'tags' variable. LINQ to Entities does not support a parameter that is a collection of values. Simply calling tags.AsQueryable() -- as suggested in an ealier answer -- will not work either because the default in-memory LINQ query provider is not compatible with LINQ to Entities (or other relational providers).

该错误与“标签”变量有关。LINQ to Entities 不支持作为值集合的参数。简单地调用 tags.AsQueryable() - 正如更早的答案中所建议的那样 - 也不会起作用，因为默认的内存中 LINQ 查询提供程序与 LINQ to Entities（或其他关系提供程序）不兼容。

As a workaround, you can manually build up the filter using the expression API (see this forum post) and apply it as follows:

作为一种解决方法，您可以使用表达式 API 手动构建过滤器（请参阅此论坛帖子）并按如下方式应用它：

var filter = BuildContainsExpression<Element, string>(e => e.Name, tags.Select(t => t.Name));
var query = source.Where(e => e.NestedValues.Any(filter));