Tag Archives: EF

Distributed Transactions in Azure SQL Databases–Azure App Service and EF

Posted on by

Are you handing more than one SQL database in Azure for your application ? Most of the times the answer would be YES. In dedicated database multi-tenant systems at least you have your customer information in the master database and dedicated application database for each customers. Some CRUD operations need to touch both the master and customer specific databases.

We need MSDTC (Microsoft Distributed Transaction Controller) for distributed transactions in on premise systems, but in Azure the SQL Databases has the elastic distributed transaction feature enabled and using .NET 4.6.1 we can use them via TransactionScope class from Systems.Transactions.

This link explains how this works, but I wanted to test this with EF and Azure App service as the Azure App service has the target platform option .NET 4.6 and not 4.6.1.

I created two logical Azure SQL servers in two different regions, and enabled the transaction communication link between them using PowerShell.

2016-08-27_18-13-09

Then I created a small Web API project using .NET 4.6.2 (which is higher than the required version) and tested the app from the local machine and things worked well. I deployed the same stuff and things worked fine in Azure as well.

Even the though the target platform is .NET 4.6 in the Azure App Service, when we deploy the .NET 4.6.1 and .NET 4.6.2 projects, the required assemblies in the respected platform version are referenced.

But my swagger endpoint behaved strange and didn’t output the results, no idea why and need to launch another investigation for that.

You can reference the test project from my Github

Conclusion – We can use the Distributed transactions in Azure SQL Database using EF and deploy your projects written in .NET 4.6.1/ 4.6.2 in the Azure App Service platform targeting .NET 4.6

Advertisement

SQL Azure Database best practices and TDD

Posted on by

This post summarizes the set of good practices you should follow when developing application on SQL Azure and how to arrange TDD or unit tests based on those practices.

Before continue reading this post, I assume that you are familiar with SQL Azure Databases, Entity Framework and TDD.

One of the highly regarded and strongly recommended cloud development strategies is resilient development, meaning that the application should expect the failures and handle them. This is more than catching the exceptions and informing to users, this includes retry logics, trying alternatives or any other way to mitigate / reduce the failure and user frustration.

Resilient Development in SQL Azure

When accessing a SQL Azure Database we should implement resilient mechanisms. EF provides an execution strategy interface to put custom logic on retrying errors. You can read more about the execution strategies here

There’s a dedicated implementation of the execution strategy named SqlAzureExecutionStrategy especially for the SQL Azure Databases.

Before continuing let us discuss why we need to implement this execution strategy and why it is highly recommended when using SQL Azure databases. You can list many transient failures in accessing databases, but I list the following two are the main culprits.

  • SQL Azure is a database as a service. Applications talk to SQL Azure through the Internet and there is a high probability of communication breakage.
  • SQL Azure has a concept of DTU (Data Throughput Units) which is a number assigned to a database, which directly maps to the usage of CPU, memory and IO. You can say that a database with higher DTU has more throughput. Since DTU is a limiting factor, when the databases reach the maximum allocated DTU usage, they start to throttle the requests, which might produce timeout exceptions.

Implementing the execution strategy comes with the limitations, mainly we cannot handle custom transactions if we implement execution strategy. So needless to say that we should implement it in a way that we can control it.

This link describes the limitations and provide the pattern to implement controllable execution strategy.

Limitation on TDD / Unit Tests

Before proceeding just by the above sub heading do not get mislead to the conclusion that TDD is same as Unit Tests, they are different approaches.

As you have read in the above link now we know the importance of implementing execution strategy and the limitations. In our unit tests we implement transactions because we can rollback them. This is very essential when running tests on production databases, so we can rollback changes done by the unit tests and leave the database unchanged after the text execution regardless of whether the tests have passed or not.

This is a great article which provides details on how to implement such a test base class.

Few good to know features of Entity Framework

Posted on by

I have been doing some reading about advance EF features for last couple of weeks and got to know some interesting things, this post is a compilation of the information and provides links to the relevant sites.

Starting with EF 5.0 ObjectContext was replaced with DbContext. So what’s the difference between ObjectContext and DbContext ?

This link gives the simplest answer for the above question. And using the following code you get the ObjectContext from your DbContext. Say that your have model class named MyEntities the best practice is to create a partial class (partial classes are the safest way since the auto generated classes could be overridden at any time there’s a code regeneration) and create a property that returns the ObjectContext. The below code describes the idea.

   1: public partial class MyEntities : DbContext

   2: {

   3:     public System.Data.Entity.Core.Objects.ObjectContext ObjectContextProperty

   4:     {

   5:         get

   6:         {

   7:             return ((IObjectContextAdapter)this).ObjectContext;

   8:         }

   9:     }

  10: }

 

Compiled Queries

Compiled Queries are helpful when we want to squeeze the final drop of performance from our application. We cache the TSQL statement which is produced from LINQ to Entity queries we write. Since we write the queries in LINQ these queries first get translated into TSQL statements and they’re executed against the SQL Server. As a result of caching the TSQL statements of the LINQ queries we cut down the translation time. Microsoft has mentioned this provides 7% performance improvement. Details here

This article provides information on creating compiled queries

MSDN Compile Query Class

The reason why I haven’t mention any samples here is that we do not need to worry much about the query compilation in EF 5.0 and above because it automatically happens.

This MSDN blog post explains the automatic query compilation in EF 5.0 and above. But again if you want to have more advanced and granular control over the compilation you should consider implementing your custom compiled queries. It’s better to have the compiled queries in your data repository classes.

Performance

Read this Data Access Blog article about the EF 5.0 performance. The below image take from the blog gives a summary.

image 

As you see that updating to EF 5.0 on .NET 4.5 gives a greater performance boost to LINQ to Entities, automatic query compilation is one of the main features for this performance gain.

EF Versions – http://msdn.microsoft.com/en-us/data/jj574253

This channel 9 video is a good one about EF 5 and EF 6

 http://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Entity-Framework-5-and-6