SQL Server timeout exception when extending Linq partial methods

Question

In .NET 4.0 and Linq to SQL, I am trying to use a partial class to "trigger" changes from within an update method (an existing DBML method). For simplicity, imagine a table Things with columns Id and Value

The auto gen DBML contains a method OnValueChanged, I'll extend that and as an exercise try to change one value in one other row :

   public partial class Things
    {
        partial void OnValueChanged()
        {
            MyAppDataContext dc = new MyAppDataContext();
            var q = from o in dc.GetTable<Things>() where o.Id == 13 select o;
            foreach (Things o in q)
            {
                o.Value = "1";  // try to change some other row
            }
            try
            {
                dc.SubmitChanges();
            }
            catch (Exception)
            {
                // SQL timeout occurs 
            }
        }
    }

A SQL timeout error occurs. I suspect that the datacontext is getting confused trying to SubmitChanges() before the current OnValueChanged() method has disposed of it's datacontext, but I am not sure.

Mostly I cannot find an example of a good pattern for triggering updates against a DB within an existing DBML generated method.

Can anyone provide any pointers on why this doesn't work and how I can accomplish something that works OK? (I realize I can trigger in the SQL database, but do not want to take that route.)

Thanks!

Answer 1

First, you aren't disposing of the DataContext at all in your function. Wrap it in a using statement.

The actual issue is coming from the fact that you're recursively calling yourself by setting the Value property on the retrieved values. You're just running into the timeout before you can hit a StackOverflowException.

It's unclear what you're trying to do here; if you're trying to allow different behavior between when you set the Value property here versus anywhere else, then it's simple enough to use a flag. In your partial class, declare an internal instance boolean auto property called UpdatingValue, and set it to true on each item inside your foreach block before you update the value, then set it to false after you update the value. Then, as the first line in OnValueChanged, check to ensure that UpdatingValue is false.

Like this:

public partial class Things
{
     internal bool UpdatingValue { get; set; }

     partial void OnValueChanged()
     {
         if (UpdatingValue) return;

         using(MyAppDataContext dc = new MyAppDataContext())
         {
             var q = from o in dc.GetTable<Things>() where o.Id == 13 select o;
             foreach (Things o in q)
             {
                 o.UpdatingValue = true;
                 o.Value = "1";  // try to change some other row
                 o.UpdatingValue = false;
             }

             dc.SubmitChanges();
         }
     }
 }

Answer 2

I would suspect that you may have introduced infinite recursion by changing the values of Things in the OnValueChanged event handler of Things.

To me, a cleaner solution to your problem is not to generate your class in a DBML file, but instead use LinqToSql attributes on a class you create. By doing so you can do your "trigger" modifications in the setters of your properties/columns.