For brevity's sake I've boiled down my scenario to a bare minimum: A SQL table [Test] with three columns (int Manual, int Automatic, IDENTITY int ID) needs any external updates on its [Manual] column to be applied to its [Automatic] column (with an additional sign change) through a Trigger.
A fairly direct translation of my non-Linq CLR Trigger to Linq yields the following code (the [Trigger_TestLinQs] SQL table has been modified to point to the Trigger's "INSERTED" table, [TestLinQs] points to the actual SQL table):
Code Snippet
[SqlTrigger(Name = "LinQTrigger", Target = "dbo.TestLinQ", Event = "FOR INSERT, UPDATE")]public static void LinQTrigger()
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
if (triggerContext.TriggerAction == TriggerAction.Insert || triggerContext.TriggerAction == TriggerAction.Update) {
//using (TransactionScope scope = new TransactionScope(Transaction.Current)) { /* err 6549 */
using (SqlConnection connection = new SqlConnection("context connection=true")) {
connection.Open();
using (MyDataContext context = new MyDataContext(connection)) {
List<int[]> updates = new List<int[]>();
foreach (Trigger_TestLinQ test in (from t in context.Trigger_TestLinQs select t)) { /* err 6522 */
if (test.Manual != null && triggerContext.IsUpdatedColumn(0))
updates.Add(new int[] { test.ID, -test.Manual.Value });
}
foreach (int[] update in updates) {
dbo.TestLinQ test = (from t in context.TestLinQs where t.ID == update[0] select t).Single<dbo.TestLinQ>();
test.Automatic = update[1];
}
context.SubmitChanges();
}
}
// scope.Complete();
//}
}
}
Running this on SQL Server 2005 / .NET 3.5 beta2 produces an Error 6522 "Cannot enlist in the transaction because a local transaction is in progress on the connection." in the line executing the Linq query because the Linq DataContext apparently insists on opening a transaction.
The recommended pattern for DataContext query operations - running them inside a TransactionScope as indicated by the commented-out sections - unfortunately leads to a different Error 6549 in the TransactionScope constructor complaining about a missing MSDTC. And there shouldn't be a need for a distributed transaction here, should there?
Using different TransactionScope constructors (TransactionScopeOptions.Required, TransactionOptions(){IsolationLevel=ReadCommitted}) did not make any difference, either.
What am I doing wrong here? Is there any way for the Linq DataContext to attach to the CLR Trigger's ambient transaction?
Interesting scenario! I won't ask why you'd want to use LINQ in this way :-)
Anyway, first of all I'm a little bit surprised that LINQ works at all within SQLCLR. I thought it would not work with a Context Connection at all.
It looks like, at the moment, that LINQ does not work very well with transactions when running inside SQLCLR (in fact it looks it doesn't work at all). So at the moment, I don't think there is anything you can do.
Niels
|||Hi nielsb,
thanks for destroying my last hope :-).
You're probably right in that presently LinQ DataContexts don't fully - or maybe even not at all - interoperate with a Context Connection.
I'm wondering why that is the case, however, since it's basically just a case of O/R mapping - which can apparently be coaxed to work with dynamic SQL Tables - and of attaching to a local System.Transactions.Transaction.
Interestingly enough you can make the LinQ DataContext work for the SELECT statement embedded in the trigger by explicitely opening a new Transaction on the SqlConnection. You can't effectively perform any updates, though, because the DataContext's change tracking mechanism doesn't kick in.
As to why I'd want to use LinQ in this context - it's the logical next step once you've been lured away from the glory of T-SQL into the murky depths of SQL Server CLR integration. :-]
