c# - Linq Query where related entity contains value from array

Question

I have a Model called JobReport which looks like this (simplified)

public class JobReport
{
    public JobReport()   
    {
        WorkOrders = new List<WorkOrder>();
    }
    public int JobID { get; set; }
    public List<WorkOrder> WorkOrders{ get; set; }
}

public class WorkOrder
{
    public WorkOrder()   
    {
        Total = 0;
    }
    public string Trade { get; set; }
    public int WorkOrderID { get; set; }
    public decimal? Total { get; set; }
}

I'd like to run a Linq query which gets me all the Jobs that have WorkOrders that have a trade which is in a passed array.

var trades = new string[] { }

I've tried something like the following which doesn't work, as it tries to get me a list of workorders, when I actually need the underlying jobs.

The problem appears to be because I'm calling Select

var jobsDB = db.Jobs.Include(x=>x.WorkOrders).ToList();

var jobs = (from p in jobsDB
                    select new JobReport()
     {
        JobID = p.JobID,
        WorkOrders = p.WorkOrders.ToList()
     }


jobs = jobs
    .Select(x => x.WorkOrders
    .Where(y => trades.Contains(y.Trade)));

Answer 1

This will work:

jobs = jobs
    .Where(x => x.WorkOrders.Any(y => trades.Contains(y.Trade)));

The way I usually tackle these problems is that I look at what the outcome must be (a list of jobs) - that means we need to put the Where first, and we must look for a condition for a job to be included. It's a bit like constructing a SQL query - in fact you can use the SQL like query syntax for most LINQ tasks, if you prefer.