Entity Framework - where clausule using Any () and All ()

Question

There is a structure:

The client has several cases and has several 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 get customers who have all the magazines from each case. The Code property is set to "WRONG" or clients that have no logs at all. In addition, I have a few simple conditions that you can see below in a simplified form for the publication code (naturally, EF uses IQueryable instead of ICollection).

First, I tried to create an extension method called AllOrEmpty that works fine, but not in Linq for entities (it does not 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 where clausule, , IQueryable : lambda

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

, - . ?

Answer 1

LINQ, let .

, :

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;

- .

source.All(predicate) || !source.Any() (, AllOrEmpty) , source.All(predicate) (, ), !source.Any(predicate).

, LINQ to Entities, SQL- ( ) LINQ to Objects, Enumerable.Any :

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

linq , :

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

, , , , "WRONG"

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

If you want all clients that do not have a log journal item for any event.

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

If you want both conditions to be together.

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