Trouble pulling Field Value from List inside Foreach

Question

Goal: I have an Index View that shows all open records from Table (ICS_Transactions). Currently it shows ALL records. I am trying to do something quick to filter out any records that DO NOT have a match in secondary table (ICS_Orders). No need to show any records without orders associated.

I have this almost working (I think). I believe my problem is this area

var MyReqs = q.FirstOrDefault().RequsitionNumber;

The foreach works, except that it's only grabbing the fist records's Requisition Number each time. And I understand why (FirstorDefault). But, I don't know what to replace that with - in order to keep looping through each time. I tried simply removing the (.FirstOrDefault()) but that syntax is not correct.

Here is the full Index Controller Code

List<ICS_Transactions> newqList = new List<ICS_Transactions>();
var q = db.ICS_Transactions.Where(s => s.OpenClosed == "Open");
q = q.Where(s => s.TransType == "New Internal Order");

var MyReqs = q.FirstOrDefault().RequsitionNumber;

foreach (ICS_Transactions trans in q)
{
//do whtaever you need to see if this trans should be includxed

var z = db.ICS_Orders.Where(s => s.RequisitionNumber == MyReqs );

if (z == null)
{
// do nothing
          
else 
{ 
newqList.Add(trans); 
}
           
}

return View(newqList);

Answer 1

1. Change MyReqs to var MyReqs = trans.RequsitionNumber; and move it inside foreach
2. Change:

if (z == null)
{
    // do nothing
}       
else 
{ 
    newqList.Add(trans); 
}

to:

if(z.Any())
{ 
    newqList.Add(trans); 
}

Or just if you have relations set up you can try to do it in one query, looking something like this (not sure about relations you have):

var newqList = db.ICS_Transactions
    .Where(s => s.OpenClosed == "Open");
    .Where(s => s.TransType == "New Internal Order")
    .Where(s => s.ICS_Orders.Any())
    .ToList();

Or at least fetch all needed RequsitionNumber's from ICS_Orders in one query to db and filter list:

var newqList = db.ICS_Transactions.Where(s => s.OpenClosed == "Open");
    .Where(s => s.TransType == "New Internal Order")
    .ToList();

var reqsInOrders = db.ICS_Orders
    .Where(s => newqList.Select(n => n.RequisitionNumber).Contains(s.RequisitionNumber))
    .Select(s => s.RequisitionNumber)
    .ToList();

newqList  = newqList
    .Where(t => reqsInOrders.Contains(t.RequisitionNumber))
    .ToList();