LINQ-to-SQL transaction problem in calculated property

  • Thread starter Thread starter Jakob Lithner
  • Start date Start date
J

Jakob Lithner

I have a DBML object that maps database to objects.

In the partial Text class I added a couple of calculated properties.
Several of them are dependant on how many child object are related to the
current object. To avoid duplicate database calls I got the idea to set local
variable _childrenCount in the Text OnLoaded event.

partial void OnLoaded()
{
// ChildrenCount is used in many calculated properties and is therefore
set already on Load.
using (MyDataContext dc = MyDataContext.CreateInstance())
{
_childrenCount = dc.Texts.Where(t => (t.TextIDParent == this.TextID)
&& (t.TextID != this.TextID)).Count();
}
}

I found no way to refer to and reuse the current datacontext object to do
the call, therefore I created an instance method on the datacontext object to
provide a new object. But it really looks like a workaround.

The above solution actually worked fine until I added TransactionScope to my
code. When a retrieved Text object is involved in a transaction it will fail
with the following message: "MSDTC on server XXX is unavailable". When the
above event handler is removed everything works fine.

I guess the transaction will detect a new datacontext object is called and
expect it to take part in the transaction. Actually it should not bother
beacuse it is only a passive read.

1) Is it possible to reuse the outer datacontext call for the inner
calculation?

2) Can I tell the inner datacontext not to take part in the outer transaction?

3) Is there a better way to set the value of the inner private variable
_childrenCount?

4) Should I configure DTC and/or exchange TransationScope with Transaction?
 
Jakob said:
I have a DBML object that maps database to objects.

In the partial Text class I added a couple of calculated properties.
Several of them are dependant on how many child object are related to
the current object. To avoid duplicate database calls I got the idea
to set local variable _childrenCount in the Text OnLoaded event.

partial void OnLoaded()
{
// ChildrenCount is used in many calculated properties and is
therefore set already on Load.
using (MyDataContext dc = MyDataContext.CreateInstance())
{
_childrenCount = dc.Texts.Where(t => (t.TextIDParent ==
this.TextID) && (t.TextID != this.TextID)).Count();
}
}

I found no way to refer to and reuse the current datacontext object
to do the call, therefore I created an instance method on the
datacontext object to provide a new object. But it really looks like
a workaround.

The above solution actually worked fine until I added
TransactionScope to my code. When a retrieved Text object is involved
in a transaction it will fail with the following message: "MSDTC on
server XXX is unavailable". When the above event handler is removed
everything works fine.

I guess the transaction will detect a new datacontext object is
called and expect it to take part in the transaction. Actually it
should not bother beacuse it is only a passive read.
Click to expand...

This is because you opened a second connection which means the
transaction is elevated to a real com+ transaction instead of being a
lightweight transaction.

As you're fetching during a transaction, it really is required you
either participate in the same transaction or re-use the same
connection as otherwise you can run into deadlocks.

I dont have answers to your linq to sql specific questions though, I'm
sorry. One possible answer could be to use eager loading however that's
so slow on linq to sql that it's not really an option.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Hello jaklithn,
1) Is it possible to reuse the outer datacontext call for the inner
Click to expand...
calculation?
You can expose a method in partial text class, then pass datacontext object
into the method.
But this isn't a good idea, because we have to call that method by ourself.
2) Can I tell the inner datacontext not to take part in the outer
Click to expand...
transaction?
When you instantiate TransactionScope, the transaction manager determines
which transaction to participate in. The decision is based on whether an
ambient transaction is present and the value of the TransactionScopeOption
parameter in the constructor. If the scope is instantiated with Suppress,
it never takes part in a transaction, regardless of whether an ambient
transaction is present. You can wrap your onload method into an suppress
transaction scope.

More detailed information:
http://msdn2.microsoft.com/en-us/library/ms172152.aspx
[Implementing an Implicit Transaction using Transaction Scope]
3) Is there a better way to set the value of the inner private variable
Click to expand...
_childrenCount?
In my opinion, I would like to create a Utility Class which exposes a count
method. In that method, it will return the clidrencount if we pass parent
id into it. We can define a datacontext instance in that class, and ensure
it could only be created once (such as singleton). If the count number of
children rows doesn't change often, we can also insert the number into
hashtable after query. Thereby, in the next time, we just need to get
number from hashtable instead of querying database again.
4) Should I configure DTC and/or exchange TransationScope with Transaction?
Click to expand...
I think you needn't. You can specify TransactionScopeOption to suppress if
you needn't inner datacontext to be involved into outer transaction.
Moreover, Transaction could also be a good solution. But I don't think it's
necessary for you to configure DTC.

Hope this helps, please feel free to let us know if you have any more
concern. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
1) I agree. Will not work for automatic event.

2) Beautiful !!!! A suppressed transaction solves my problem.

3) I will consider this suggestion, but for now I don't think it is necessary.

4) I agree. Transaction/DTC not needed.
 
Hello Jaklithn,
Thanks for your reply.

It seems a suppressed transaction is what you're after.
If you face any further issue or you have any more concern, please feel
free to let us know. We are glad to assist you.

Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top