实体框架 - 与Any（）和All（）使用的关系 [英] Entity Framework - where clausule with Any() and All() use

问题描述

有一个结构：

客户端有多种情况，案例有多个LOGS。

Client have multiple cases and case have multiple LOGS.

    public class Client
    {
        public int Id { get; set; }
        public IEnumerable<Case> Cases { get; set; }
    }

    public class Case
    {
        public int CaseId { get; set; }
        public IEnumerable<Log> Histories { get; set; }
    }

    public class Log
    {
        public int Id { get; set; }
        public string Code { get; set; }
    }

我想从每个案例检索具有所有日志的客户端代码属性设置为错误或根本没有日志的客户端。此外，我有几个简单的条件，您可以在下面简要看到出版代码的目的（自然EF使用IQueryable而不是ICollection）。

I want to retrieve clients that have all logs' from each case Code property set to "WRONG" OR clients that have no logs at all. Besides I have couple simple conditions that you can see below in simplified for the purposes of publication code (naturally EF uses IQueryable instead of ICollection).

首先我尝试创建扩展方法称为AllOrEmpty，它工作正常但不在Linq中实体（它不接受扩展方法）。

Firstly I tried to create extension method called AllOrEmpty which works fine but not in Linq to entities (it doesn't accept extension methods).

   public static class Extensions
   {
       public static bool AllOrEmpty<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate)
       {
           return source.All(predicate) || !source.Any();
       }
   }

        var sampleIds = new List<int>() { 1, 2, 3 };

        Entities db = new Entities();
        db.Clients
           .Where(client => client.Cases
               .Where(cas => sampleIds.Contains(cas.CaseId))
               .SelectMany(cas => cas.Histories
                   .Where(log => log.Id < 10)
                   )
               .AllOrEmpty(log => log.Code == "WRONG") << ideal solution
               )
               .Select(client => client.Id);

其次我试图用return语句创建lambda表达式，其中clausule和它的工作正常，但不适用于IQueryable并返回错误：具有语句正文的lambda表达式无法转换为表达式树

Secondly I was trying to create lambda expression with return statement in where clausule and it works fine but not for IQueryable and returns error: A lambda expression with a statement body cannot be converted to an expression tree

         db.Clients
            .Where(client => 
            {
                var logs = client.Cases
                    .Where(cas => sampleIds.Contains(cas.CaseId))
                    .SelectMany(cas => cas.Histories
                        .Where(log => log.Id < 10)
                        );

                return !logs.Any() || logs.All(log => log.Code == "WRONG");
             })
             .Select(client => client.Id);

我不知道如何创建这样的查询，并保持简单，并避免一些脏代码。任何想法？

I have no idea how to create such query and keep it simple and avoid some dirty code. Any idea?

推荐答案

您可以利用LINQ查询语法，其中 let 子句和透明标识符大大简化了这种查询。

You can leverage the LINQ query syntax, which with let clause and transparent identifiers greatly simplifies such queries.

例如，您的查询可能如下所示：

For instance, your query could be like this:

var query =
    from client in db.Clients
    let logs = from cas in client.Cases
               where sampleIds.Contains(cas.CaseId)
               from log in cas.Histories
               where log.Id < 10
               select log
    where !logs.Any() || logs.All(log => log.Code == "WRONG")
    select client.Id;

但是我想提一下你的扩展方法。

But I want to mention something regarding your extension method.

条件 source.All（predicate）|| ！source.Any（）（因此您的 AllOrEmpty 方法）没有任何意义，因为它相当于源.All（谓词）（是的，这不是一个错误）或！source.Any（predicate）。

The condition source.All(predicate) || !source.Any() (hence your AllOrEmpty method) does not make any sense because it is equivalent to either source.All(predicate) (yes, this is not a mistake) or !source.Any(predicate).

您可以通过查看生成的SQL查询（一个）和LINQ to Objects来轻松地验证LINQ to Entities，方法是查看 Enumerable.Any 参考资料或以下简单测试：

You can easily verify that for LINQ to Entities by looking at the generated SQL query (one and the same) and for LINQ to Objects by looking at the Enumerable.Any reference source or the following simple test:

class Foo
{
    public int Bar { get; set; }
}

var source = new List<Foo>();
bool test1 = !source.Any() || source.All(e => e.Bar == 0);
bool test2 = source.All(e => e.Bar == 0);
bool test3 = !source.Any(e => e.Bar == 0);
Debug.Assert(test1 == test2 && test2 == test3);

这篇关于实体框架 - 与Any（）和All（）使用的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！