c# - Is there a way to construct database query, where I can apply condition based on values in a list?

Question

var contacts = await dbContext.Registration.Where(x => visited.Any(y => y.Id == x.Id)
                && visited.Any(y => (x.OutDate >= y.InDate && x.OutDate <= y.OutDate)
                || (x.InDate >= y.InDate && x.OutDate <= y.OutDate)
                || (x.InDate >= y.InDate && x.InDate <= y.OutDate)
                || (x.InDate <= y.InDate && x.OutDate >= y.OutDate)))
                .ToListAsync()

This query gives error that, "The LINQ Query could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()."

Here visited is a list of Object that has property Id, InDate and OutDate. Visited has been already fetched from DB and is present in ,memory. I want to filter my database results based on the values present in these objects.

One way could be Iterating through each object and getting all tuples according to a single object and repeating the same for other objects in the list. This would involve multiple Network Calls and would be very inefficient.

Is there any way in which I get all the results in 1 Network call ?

Answer 1

Assuming that x.OutDate > x.InDate and y.OutDate > y.InDate you can reduce to following :

             var contacts = dbContext.Registration.SelectMany(x => visited.Where(y => (y.Id == x.Id)
                && ((x.InDate >= y.InDate && x.OutDate <= y.OutDate)
                || (x.InDate <= y.InDate && x.OutDate >= y.OutDate))));

---

Where comparing two ranges of dates there are 7 combinations