masoumeh karvar masoumeh karvar - 3 months ago 60
C# Question

How to check if one list contains all elements of another list in linq to sql statement

I have a mission table and want to pass a

List<int>
contains some personId to a search method and return missions that have all of these personIds(as DoctorId, driverId,NurseId,OperatorId). I tried this :

using (MainDataContext context = new MainDataContext())
{
return context.Missions
.Where(t => !personIds.Except(new List<int>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID}).Any())
}


and this:

!personIds.Any(item => !(new List<int?>() {t.DoctorID, t.NurseID,t.OperatorID,t.DriverID}.Contains(item)))


and this :

!personIds.Any(item => t.DoctorID != item && t.NurseID != item && t.OperatorID != item && t.DriverID != item)


and this :

personIds.Intersect(new List<int>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID}).Count() == personIds.Count()


and this:

personIds.All(u => new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(u))


and all of these raise the same error :


Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.


the following code works but it's dirty and I hope there is a better way :

personIds.Count() == 0
|| (personIds.Count() == 1 && new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[0]))

|| (personIds.Count() == 2 && new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[0])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[1]))

|| (personIds.Count() == 3 && new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[0])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[1])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[2]))

|| (personIds.Count() == 4 && new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[0])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[1])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[2])
&& new List<int?>() { t.DoctorID, t.NurseID, t.OperatorID, t.DriverID }.Contains(personIds[3]))


is there any way to check if a list contains all elements of another list in linq to sql?

Answer

First I'd make sure all the ids are unique. Then if there are more than 4 then you wouldn't want any results so there's no point in doing the query. Finally you'd just iterate over the ids and do a Where that makes sure that each id is one of the mission ids.

using (MainDataContext context = new MainDataContext())
{
    var uniqueIds = personIds.Distinct().ToList();
    if(uniqueIds.Count > 4)
        return Enumerable.Empty<Mission>().AsQueryable();

    var result = context.Missions;
    foreach(int id in uniqueIds)
    {
        result = result.Where(t => id == t.DoctorID || 
                                   id == t.NurseID || 
                                   id == t.OperatorID ||
                                   id == t.DriverID);
    }
    return result;
}

My assumptions are that if you have more than 4 unique ids you'd want no results and if personIds is empty then you'd want all the missions.