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?
Thank!
