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

i have mission table , want pass list<int> contains personid search method , return missions have of these personids(as doctorid, driverid,nurseid,operatorid). tried these 6 ways :

using (maindatacontext context = new maindatacontext())         {             return context.missions                 .where(t => !personids.except(new list<int>() { t.doctorid, t.nurseid, t.operatorid, t.driverid}).any()) } 

2:

!personids.any(item => !(new list<int?>() {t.doctorid, t.nurseid,t.operatorid,t.driverid}.contains(item))) 

3 :

!personids.any(item => t.doctorid != item && t.nurseid != item && t.operatorid != item && t.driverid != item) 

4 :

personids.intersect(new list<int>()  { t.doctorid, t.nurseid, t.operatorid, t.driverid}).count() == personids.count() 

5:

personids.all(u => new list<int?>() { t.doctorid, t.nurseid, t.operatorid, t.driverid }.contains(u)) 

6:

personids.all(p => p == t.doctorid || p == t.nurseid || p == t.operatorid || p == t.driverid) 

and of these raise same error :

local sequence cannot used in linq sql implementations of query operators except contains operator.

the following code works it's dirty , hope there 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 way check if list contains elements of list in linq sql?

first i'd make sure ids unique. if there more 4 wouldn't want results there's no point in doing query. you'd iterate on ids , where makes sure each id 1 of 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 if have more 4 unique ids you'd want no results , if personids empty you'd want missions.