Linq Contains issue: cannot formulate the equivalent of 'WHERE IN' query

In the table ReservationWorkerPeriods there are records of all workers that are planned to work on a given period on any possible machine.

The additional table WorkerOnMachineOnConstructionSite contains columns workerId, MachineId and ConstructionSiteId.

From the table ReservationWorkerPeriods I would like to retrieve just workers who work on selected machine.

In order to retrieve just relevant records from WorkerOnMachineOnConstructionSite table I have written the following code:

var relevantWorkerOnMachineOnConstructionSite = (from cswm in currentConstructionSiteSchedule.ContrustionSiteWorkerOnMachine
                                                        where cswm.MachineId == machineId
                                                        select cswm).ToList();
        workerOnMachineOnConstructionSite = relevantWorkerOnMachineOnConstructionSite as List<ContrustionSiteWorkerOnMachine>;

These records are also used in the application so I don't want to bypass the above code even if is possible to directly retrieve just workerPeriods for workers who work on selected machine. Anyway I haven't figured out how it is possible to retrieve the relevant workerPeriods once we know which userIDs are relevant.

I have tried the following code:

var userIDs = from w in w开发者_如何学编程orkerOnMachineOnConstructionSite select new {w.WorkerId};

        List<ReservationWorkerPeriods> workerPeriods = currentConstructionSiteSchedule.ReservationWorkerPeriods.ToList();
        allocatedWorkers = workerPeriods.Where(wp => userIDs.Contains(wp.WorkerId));

but it seems to be incorrect and don't know how to fix it. Does anyone know what is the problem and how it is possible to retrieve just records which contain userIDs from the list?

Currently, you are constructing an anonymous object on the fly, with one property. You'll want to grab the id directly with (note the missing curly braces):

var userIDs = from w in workerOnMachineOnConstructionSite select w.WorkerId;

Also, in such cases, don't call ToList on it - the variable userIDs just contains the query, not the result. If you use that variable in a further query, the provider can translate it to a single sql query.