Linq to SQL replace .Any() with .Contains()

Question

Got a dictionary with the persons Id as a key. And each value is a List with a class that contains a datetime.

I want to get all contracts from the database where each date in the list is in between the contracts from and until -date. There could be multiple for each person.

I can't use .Any() function. If I do I'll get this error: " Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."

This is an example with the .Any method that doesn't work with linq to sql. And I need an other way to manage this.

public class SimpleObject
    {
        public bool Test { get; set; }
        public DateTime DateTime { get; set; }
    }

    private void Test(Dictionary<int, List<SimpleObject>> dataBaseObjDictionary)
    {
        using (var db = TpContext.Create())
        {
            var selectedObj = db.Contracts.Where(x => dataBaseObjDictionary.ContainsKey(x.PersonRef) && dataBaseObjDictionary.Values.Any(y => y.Any(a => x.FromDate <= a.DateTime) && y.Any(a=>a.DateTime >= x.UntilDate)));
        }
    }

Answer 1

I think this should do it. It looks like you were relying on y to be the same with the two y.anys. In addition, you were checking if a was greater than until date and greater than from date so I fixed those.

var selectedObj = db.Contracts.Where(x => dataBaseObjDictionary.ContainsKey(x.PersonRef) 
                && dataBaseObjDictionary.Values.Any(
                y => y.Any(a => x.FromDate <= a.DateTime 
                        && a.DateTime <=  x.UntilDate)
                       )
                );