This article continues series of publications about non-trivial Entity Framework situations encountered by our users.
We consider the following questions in this article:
- Why Oracle raises an exception about too many local sessions
participating in global transaction when using TransactionScope? - How can I optimize SQL queries using Entity Framework?
- How can I perform cascade deleting of objects in Entity Framework?
- How can I get the default value of a database field in my Entity Framework project?
1. Why Oracle raises an exception about too many local sessions participating in global transaction when using TransactionScope?
Issue:
I have run the following test code:
public void TestMaxNumberOfObjectContetxtInTransactionScope()
{
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.Required,
IsolationLevel.ReadCommitted))
{
for (int i = 0; i < 50; i++)
{
using (TestObjectContext context = new TestObjectContext())
{
TestData newTestData = TestData.CreateTestData(id);
newTestData.Description = description;
context.AddToTestDataEntity(newTestData);
context.SaveChanges();
}
}
ts.Complete();
}
}
and it failed after the 32 iterations. The error I get is “ORA-02045: too many local sessions participating in
global transaction”. One more strange thing is the fact that everything works like a charm in Direct mode.
Could you please explain the behaviour?
Solution:
Maximum number of branches in the distributed Oracle transaciton is 32. We use distributed Oracle transactions in our code that implements TransactionScope support. So, as soon as the number of transactions inside the scope exceeds this number this error appears. The reason of the fact that Direct mode works is the fact that there is no distributed transactions support in it (local transactions are used). So, the workaround is to use TransactionScope with smaller number of transaction branches. In most scenarios it is enough to use a local transaction like in the following example:
context.Connection.Open();
DEPT department = context.DEPT.Where(d => d.DEPTNO == 10).First();
department.LOC = "TEST";
using (System.Data.Common.DbTransaction transaction =
context.Connection.BeginTransaction()) {
context.SaveChanges();
department.DNAME = "TEST";
context.SaveChanges();
if (flag)
transaction.Commit();
else
transaction.Rollback();
}
Note: Starting from the last version of dotConnect for Oracle we solve this problem with the help of promotable single phase transaction support. Only one active session with internal local transaction is used during several connections opening. You can activate this option in connection string setting up the parameter “Transaction Scope Local=true”.
2. How can I optimize SQL queries using Entity Framework?
Issue:
Solution:
You have two alternatives – the first one is to create views in your database implementing the desired behaviour, and the second one is to manually create Defining Query in the Storage model and entities in your conceptual model corresponding to these Defining Queries.
In both cases you will be able to use the MySQL hints.
3. How can I perform cascade deleting of objects in Entity Framework?
Issue:
Solution:
<Association Name="CProductCategory">
<End Type="Self.CProduct" Multiplicity="*" />
<End Type="Self.CCategory" Multiplicity="0..1*">
<OnDelete Action="Cascade" />
</End>
</End>
</Association>
Example of SSDL is shown below:
<Association Name="CProductCategory">
<End Role="PRODUCT" Type=Self.Store.CProduct" Multiplicity="0..1" >
<OnDelete Action="Cascade" />
</End>
</Association>
As you can see to add cascade deleting just define the following construction in CSDL or SSDL part of your .edmx file:
<OnDelete Action="Cascade"/>
4. How can I get the default value of a database field in my Entity Framework project?
Issue:
I have a varchar2 “MyProperty” column in my Oracle database containing the default value. How can I get this value, e.g, to populate my Textbox in the user input form?
Solution:
You need to query MetadataWorkspace, for example, in the following way:
MetadataWorkspace workspace = context.MetadataWorkspace; object value = new value(); var item = workspace.GetItems<EntityType>(DataSpace.CSpace).Where (i => i.Name == "MyEntity").Single(); value = item.Properties.Where (p => p.Name == "MyProperty").Single().DefaultValue;
We hope this material will be useful for you. Please provide your feedbacks and suggestions in comments to article.
If I add the setting “TransactionScopeLocal=true” to my connection string as suggested in the note at the end of question #1 I get an exception telling me that the connection string parameter is not supported. Is this parameter really implemented in the current version?
Thank you for the comment. We have made a correction, the parameter name should be “Transaction Scope Local”. This functionality is available since the latest 6.0.46 Beta build of dotConnect for Oracle.