Entity Framework - where clausule with Any() and A

Question

There is a structure:

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; }
    }

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).

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

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?

Answer 1

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.

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).

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

Answer 2

This linq query should do what you're trying to do:

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

Answer 3

If you want clients who has cases where all the history log items has the code set to "WRONG"

var clientsWithWrongLogCode = clist.Where(s => s.Cases
                                       .Any(c => c.Histories.All(h => h.Code == "WRONG")));

If you want to get all the clients who does not have any History log item for any cases.

var clientsWithNoLogs = clist.Where(s => s.Cases.Any(c => !c.Histories.Any()));

If you want both conditions together.

var combined = clist.Where(s => s.Cases.Any(c => c.Histories.All(h => h.Code == "WRONG")
                                                                  || !c.Histories.Any()) );