Issues with LINQ (to Entity) [adding records]

Question

I am using LINQ to Entity in a project, where I pull a bunch of data (from the database) and organize it into a bunch of objects and save those to the database. I have not had problems writing to the db before using LINQ to Entity, but I have run into a snag with this particular one. Here's the error I get (this is the "InnerException", the exception itself is useless!):

New transaction is not allowed because there are other threads running in the session.

I have seen that before, when I was trying to save my changes inside a loop. In this case, the loop finishes, and it tries to make that call, only to give me the exception. Here's the current code:

try
{
    //finalResult is a list of the keys to match on for the records being pulled
    foreach (int i in finalResult)
    {
         var queryEff = (from eff in dbMRI.MemberEligibility
                         where eff.Member_Key == i && eff.EffDate >= DateTime.Now
                         select eff.EffDate).Min();

         if (queryEff != null)
         {
             //Add a record to the Process table
             Process prRecord = new Process();
             prRecord.GroupData = qa;
             prRecord.Member_Key = i;
             prRecord.ProcessDate = DateTime.Now;
             prRecord.RecordType = "F";
             prRecord.UsernameMarkedBy = "Autocard";
             prRecord.GroupsId = qa.GroupsID;
             prRecord.Quantity = 2;
             prRecord.EffectiveDate = queryEff;

             dbMRI.AddObject("Process", prRecord);
         }
    }

    dbMRI.SaveChanges();    //<-- Crashes here

    foreach (int i in finalResult)
    {
        var queryProc = from pro in dbMRI.Process
                        where pro.Member_Key == i && pro.UsernameMarkedBy == "Autocard"
                        select pro;

        foreach (var qp in queryProc)
        {
             Audit aud = new Audit();
             aud.Member_Key = i;
             aud.ProcessId = qp.ProcessId;
             aud.MarkDate = DateTime.Now;
             aud.MarkedByUsername = "Autocard";
             aud.GroupData = qa;

             dbMRI.AddObject("Audit", aud);
        }
   }

   dbMRI.SaveChanges();          //<-- AND here (if the first one is commented out)
}
catch (Exception e)
{
    //Do Something here
}

Basically, I need it to insert a record, get the identity for that inserted record and insert a record into another table with the identity from the first record. Given some other constraints, it is not possible to create a FK relationship between the two (I've tried, but some other parts of the app won't allow it, AND my DBA team for whatever reason hates FK's, but that's for a different topic :))

Any ideas what might be causing this?

Thanks!

Edit: thanks for updating the tags!

Answer 1

Your problem is you have an open Reader somewhere. The solution is to make sure you have selected your data and are not enumerating on a query when calling SaveChanges().

My guess is finalResult is Queryable (so add a ToList(), ToArray(), etc), or the block you sent us is inside a larger block that is enumerating on a linq query.