I’m trying to avoid to call SaveChanges inside a loop and call it only once when the loop is completed.

Seems that in this case I have to SaveChanges at every iteration, here a small example.

I’ve this two entities:

public class OrderRow
{
    public int Id { get; set; }
    public string PartNumber {get; set; }
    public List<ShipmentRow> Rows { get; set; } 
}


public class ShipmentRow
{
    public int Id { get; set; }
    public string PartNumber {get; set; }
    public int? OrderRowId { get; set; }
    public OrderRow? OrderRow { get; set; }
}  

Now I need to match one or more rows of the shipment table with one order row, let’s say this is the data inside the two tables:

OrderRows Table

ShipmentRow Table

In a loop I’m matching one row with the other (very simplified example):

foreach(var item in dbContext.OrderRow.ToList())
{
    List<ShipmentRow> sRows = dbContext.ShipmentRow
                                       .Where(x=> x.PartNumber == item.PartNumber 
                                                  && x.OrderRowId == null)
                                       .ToList();

    foreach(var s in sRows)
    {
        item.Rows.Add(s)
    }
    
    dbContext.SaveChanges();
}

Now I want to move SaveChanges() outside the loop and save everything at once, but the problem is that if I remove SaveChanges from the loop on the second iteration sRows contains the already matched data.

On the first iteration the shipment row with id 333 is matched with order row id 111, on the second iteration the shipment row 333 gets matched with order 222 even though it should never be retrieved from the DB since it was already associated but the foreign key is not added until SaveChanges is called.
I’ve tried adding it manually but the sRow db call still see the OrderRowId as null.

I need to move that SaveChanges outisde because is slowing things down too much.
I thought the EF was smart enough to detect that change…but is probably me doing something wrong.

A possible solution: check if the shipment row retrieved from the DB is already associated with the orders:

List<OrderRow> orders = dbContext.OrderRow.ToList();

foreach(var item in orders)
{
    List<ShipmentRow> sRows = dbContext.ShipmentRow
                                       .Where(x=> x.PartNumber == item.PartNumber 
                                                  && x.OrderRowId == null)
                                       .ToList(); 
            
    foreach(var s in sRows)
    {
        if(orders.SelectMany(x => x.Rows).Any(x => x.Id == s.Id))
            continue;
        
        item.Rows.Add(s)
    }
}  
dbContext.SaveChanges();

Here is ChatGPT solution: retrieve everything and work in RAM, could cause a large memory footprint when the DB size increase

var orderRows = dbContext.OrderRow.ToList();
var shipmentRows = dbContext.ShipmentRow.Where(x => x.OrderRowId == null).ToList();

foreach (var orderRow in orderRows)
{
    var matchingShipmentRows = shipmentRows
        .Where(x => x.PartNumber == orderRow.PartNumber && x.OrderRowId == null)
        .ToList();

    foreach (var shipmentRow in matchingShipmentRows)
    {
        orderRow.Rows.Add(shipmentRow);
        shipmentRow.OrderRowId = orderRow.Id;
    }
}

dbContext.SaveChanges();