LINQ to SQL and TransactionScope == DTC
-
The client I'm working for is using LINQ to SQL behind a BLL of sorts. So each CRUD operation gets its own context. Here's an example of the Save method:
public static Step Save(Step step) { using ( KRATDataContext context = new KRATDataContext( Helpers.EnvironmentState.CurrentConnectionString ) ) { Step oldStep = step.StepID > 0 ? context.Steps.Single( p => p.StepID == step.StepID ) : null; if ( oldStep == null ) { context.Steps.InsertOnSubmit( step ); } else { oldStep.Description = step.Description; oldStep.PotentialHazard = step.PotentialHazard; oldStep.ActionsToReduceRisk = step.ActionsToReduceRisk; oldStep.InitialRiskCellID = step.InitialRiskCellID; oldStep.PostRiskCellID = step.PostRiskCellID; oldStep.DateCreated = step.DateCreated; oldStep.RiskAnalysisID = step.RiskAnalysisID; oldStep.DisplayIndex = step.DisplayIndex; } context.SubmitChanges(); return step; } }I have implemented functionality which allows a user to change the display order of a "Step" record by clicking an up/down arrow which essentially swaps the value of DisplayIndex for two separate records. Like this:protected void Swap(int stepId1, stepId2) { // get the actual records Step step1 = Step.GetOne(stepId1); Step step2 = Step.GetOne(stepId2); // do the real swap (left out logic on purpose to focus on issue) Swap(step1, step2); using( System.Transactions.TransactionScope txCtx = new System.Transactions.TransactionScope()){ Step.Save(step1); Step.Save(step2); txCtx.Complete(); } }The important fact being demonstrated here is that each save operation has its own DataContext. So when the 2nd call to Save() is made I get an exception because I have explicitly turned off DTC (ie. I don't allow remote DTC operations). But these two records are in the same table. So here's what I'm looking for: 1) Can someone else confirm this for me, but duplicating the error I get. 2) Is there a way around this, other than using the same DataContext object? 3) I'll need to double check, but this doesn't happen to me when I use the EnterpriseLibrary connection objects with TransactionScope. So is this a bug? Or at least something that should be changed so it works the way EnterpriseLibrary does? #2 is important because while fixing this issue for the specific case above (both objects are based on the same database ta -
The client I'm working for is using LINQ to SQL behind a BLL of sorts. So each CRUD operation gets its own context. Here's an example of the Save method:
public static Step Save(Step step) { using ( KRATDataContext context = new KRATDataContext( Helpers.EnvironmentState.CurrentConnectionString ) ) { Step oldStep = step.StepID > 0 ? context.Steps.Single( p => p.StepID == step.StepID ) : null; if ( oldStep == null ) { context.Steps.InsertOnSubmit( step ); } else { oldStep.Description = step.Description; oldStep.PotentialHazard = step.PotentialHazard; oldStep.ActionsToReduceRisk = step.ActionsToReduceRisk; oldStep.InitialRiskCellID = step.InitialRiskCellID; oldStep.PostRiskCellID = step.PostRiskCellID; oldStep.DateCreated = step.DateCreated; oldStep.RiskAnalysisID = step.RiskAnalysisID; oldStep.DisplayIndex = step.DisplayIndex; } context.SubmitChanges(); return step; } }I have implemented functionality which allows a user to change the display order of a "Step" record by clicking an up/down arrow which essentially swaps the value of DisplayIndex for two separate records. Like this:protected void Swap(int stepId1, stepId2) { // get the actual records Step step1 = Step.GetOne(stepId1); Step step2 = Step.GetOne(stepId2); // do the real swap (left out logic on purpose to focus on issue) Swap(step1, step2); using( System.Transactions.TransactionScope txCtx = new System.Transactions.TransactionScope()){ Step.Save(step1); Step.Save(step2); txCtx.Complete(); } }The important fact being demonstrated here is that each save operation has its own DataContext. So when the 2nd call to Save() is made I get an exception because I have explicitly turned off DTC (ie. I don't allow remote DTC operations). But these two records are in the same table. So here's what I'm looking for: 1) Can someone else confirm this for me, but duplicating the error I get. 2) Is there a way around this, other than using the same DataContext object? 3) I'll need to double check, but this doesn't happen to me when I use the EnterpriseLibrary connection objects with TransactionScope. So is this a bug? Or at least something that should be changed so it works the way EnterpriseLibrary does? #2 is important because while fixing this issue for the specific case above (both objects are based on the same database taOk, well I came up with a solution for this (for anyone interested). The DataContext is not aware of any other contexts (through thread local storage) in any way. So I basically decided to use thread local storage to store my DataContext for the current thread (HttpRequest). Here's how it works. The DataContext classes generated for a dbml file is a partial class, so I added a class file to the same project which contains a static factory method, GetDataContext(). GetDataContext checks HttpContext.Current.Items to see if a DataContext object already exists. If not, it creates and new one, stores it in HttpContext.Current.Items and the returns the new instance. If the object already exists, it just returns the existing object to the caller.
public static DataContext GetDataContext() { if(HttpContext.Current == null) return new DataContext(connectionString); if(HttpContext.Current.Items.Contains("DataContext")) { return (DataContext) HttpContext.Current.Items["DataContext"]; } return new DataContext(connectionString); }Now, I am able to use TransactionScope to wrap my business logic without having to use DataContext directly. Plus there are other benefits, my Save method no longer requires the additional database call to retrieve the record and copy the changes before calling SubmitChanges(). So compared with my previous post, my Save method now looks like this:public static Step Save(Step step) { KRATDataContext context = KRATDataContext.GetDataContext(); if ( step.StepID <= 0 ) { context.Steps.InsertOnSubmit( step ); } context.SubmitChanges(); return step; }Which requires one less database call, always a plus :cool:. This all solves my Transaction problem because it uses the same connection object (because we're using the same DataContext which owns the Connection object) and so there is no longer any distributed transactions (DTC) required.Code responsibly: OWASP.org Mark's blog: developMENTALmadness.blogspot.com