LINQ Join query with Multiple Conditions in On Clause

As per your select, you might get rows where CO.ApprovalStatusID returns NULL, since you're using LEFT JOIN and you're not specifying any clauses for it in WHERE (which imho would be strange since you're using LEFT).

Your sample query states that your goal is not to get only CostItems where their ChangeOrder has ApprovalStatusID equals 2 or 4. Your query will get all active CostItems for project 3, and list their CostItemId, ParentId, CBSPosition and if they have a ChangeOrder with ApprovalStatusID equals 2 or 4, it will get that status (whether is 2 or 4).

It's kinda simple to generate that IN statement. You could create an array of int containing the status you want (2 and 4) and use it like this:

var validStatus = new int[] { 2, 4 }; ctx.ChangeOrders.Where(co => validStatus.Any(vs => vs == co.ApprovalStatusID)); 

Now, I can see two ways of resolving your full query, one using LINQ GroupJoin to perform the LEFT JOIN, another by using entity relationships. I used the last one assuming these classes as example:

public class CostItem { public int CostItemId { get; set; } public int ParentId { get; set; } public int CBSPosition { get; set; } public int ProjectID { get; set; } public bool isActive { get; set; } public virtual ChangeOrderDetail Detail { get; set; } // Idk if this is a collection or one single optional child.... } public class ChangeOrderDetail { public int CostItemId { get; set; } public int ChangeOrderId { get; set; } public virtual ChangeOrder ChangeOrder { get; set; } } public class ChangeOrder { public int ChangeOrderId { get; set; } public int ApprovalStatusID { get; set; } } public class CostItemWithChangeOrderApprovalStatus { public int CostItemId { get; set; } public int ParentId { get; set; } public int CBSPosition { get; set; } public int? ApprovalStatusID { get; set; } } 

Since you asked for LINQ and didn't mention any specific provider (which could be EF6, ADO.NET...), I'm providing which a LINQ statement (which would work for EF6, for example) that could be like this:

var validStatus = new int[] { 2, 4 }; this.CostItems .Where(ci => ci.ProjectID == 3 && ci.isActive) .Select(ci => new CostItemWithChangeOrderApprovalStatus { CostItemId = ci.CostItemId, ParentId = ci.ParentId, CBSPosition = ci.CBSPosition, ApprovalStatusID = validStatus.Contains(ci.Detail.ChangeOrder.ApprovalStatusID) ? (int?)ci.Detail.ChangeOrder.ApprovalStatusID : null }); 

Tell me if your classes are not like this, or if you need more details, or another way of executing this query.