Improving an insert method

Question

I'm wrote an insert method that uses linq and loops through 2 lists, the first being able to go up to 14k objects and the send about 8k objects.

Whenever I run this method, I always get "Transaction Timeout Exception". Can you help me improve this?

public void InsertNewInventoryGoodsEvents(List<GoodsEvent> eventsList, List<InventoryGoods> goodsList)
{
    InventoryGoodsEvents ige = new InventoryGoodsEvents();
    TransactionScope scope= new TransactionScope();            
    int i = 0;
    const int batchSize = 50; // or even 50    
    foreach (InventoryGoods good in goodsList)
    {
        if (i == 50)
        {
            if (scope != null)
            {
                context.SubmitChanges();
            }
            i = 0;
        }    
        try
        {
             foreach (GoodsEvent g in eventsList)
             {
                 if (g.Gid == good.Gid)
                 {
                     ige = new InventoryGoodsEvents() { EventId = g.Id, InventoryGood = good.Id };    
                     context.InventoryGoodsEvents.InsertOnSubmit(ige);
                 }
             }
         }
         catch (Exception ex)
         {
             ex.ToString();    
         }    
         ++i;
     }
     if (scope != null)
     {    
         context.SubmitChanges();
         scope.Complete();                 
     }
}

Answer 1

Is it important that all of the inserts be done in the same transaction? If not, could you just use a new transaction for each batch of items you're inserting? That should prevent the transaction from being open for too long and timing out.

Answer 2

the Foreach in a foreach can can be re-written as this-

var itsReallyJustAJoin = (from g in GoodEvent
                          join i in InventoryEvent on g.Gid equals i.Gid
                          select new {g.Id, good.Id}).ToList();

Then you can insert these however you'd like. It looks like you're doing batches of 50 right now? You can continue that if you'd like... This will take all of the work outside of the transaction scope though, so you can probably lob the whole bit in there at once, if your results aren't too huge.

Answer 3

I have re-written the method after giving it a good thought and it turned out like this:

public void InsertNewInventoryGoodsEvents(List<GoodsEvent> eventsList, List<InventoryGoods> goodsList)
        {

            List<InventoryGoodsEvents> mylist = new List<InventoryGoodsEvents>();
            InventoryGoodsEvents ige = new InventoryGoodsEvents();

            foreach (GoodsEvent g in eventsList)
            {
                foreach (InventoryGoods ig in goodsList)
                {
                    if (ig.Gid == g.Gid)
                    {
                        ige = new InventoryGoodsEvents();
                        ige.EventId = g.Id;
                        ige.InventoryGood = ig.Id;
                        mylist.Add(ige);
                    }
                }
            }

            using (var scope = new TransactionScope())
            {
                foreach (InventoryGoodsEvents ip in mylist)
                {
                    context.InventoryGoodsEvents.InsertOnSubmit(ip);
                }

                context.SubmitChanges();
                scope.Complete();
            }
        }

I liked MikeM's idea of simplifying things but we'll see which method I'll keep.