The underlying provider failed on Open Error in Entity Framework Transactions

Error:
The underlying provider failed on Open
MSDTC on server 'XXXXX\\SQLEXPRESS' is unavailable."   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)\r\n   at System.Data.EntityClient.EntityConnection.Open()\r\n   at System.Data.Objects.ObjectContext.EnsureConnection()\r\n   at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)\r\n   at System.Data.Objects.ObjectContext.SaveChanges()\r\n  

Cause:
The error The underlying provider failed on Open is thrown while performing a transactional update using Entity Framework, if the MSDTC - Distributed Transaction Coordinator service is not started.

Resolution:

Transactions in Entity Framework with DbTransaction.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and DbTransaction. Unlike the TransactionScope which needs the MSDTC - Distributed Transaction Coordinator service to be running, DbTransaction will work even if the service is not running.

In this post we will do 2 operations insert a row in the Bugs table and insert a row in the Comments table in a single transaction.

Transactions in Entity Framework with TranscationScope.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and TransactionScope. To make use of the TransactionScope we need to make sure that the MSDTC - Distributed Transaction Coordinator service is running. If the service is not running then the code will throw the following error.
The underlying provider failed on Open

In this post we will do 2 operations update the Bugs table and insert a row in the Comments table in a single transaction.

TranscationScope Vs DbTransaction

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

Transactions in Entity Framework

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

LINQ to SQL

LINQ to SQL

LINQ to SQL Object Relational Designer (O/R Designer) LINQ to SQL Sample LINQ To SQL Vs ADO.Net Entity Framework LINQ to SQL Select Query with Specific columns LINQ to SQL Select Query with Alias Name LINQ to SQL Select query with Filter (WHERE Clause) LINQ to SQL - Sub Queries LINQ to SQL – Join 2 Tables LINQ to SQL – Select DISTINCT Values LINQ to SQL – Select with SORT (ORDER BY) LINQ to SQL – Select with AGGREGATE (GROUP BY) LINQ to SQL – INNER JOIN LINQ to SQL – LEFT OUTER JOIN LINQ to SQL – RIGHT OUTER JOIN LINQ to SQL – INSERT LINQ to SQL – UPDATE LINQ to SQL – DELETE LINQ to SQL – Using ExecuteCommand() to Execute SQL Statements LINQ to SQL - call stored procedure without parameters LINQ to SQL - call stored procedure with parameters LINQ to SQL - call stored procedure with OUTPUT parameters LINQ to SQL Vs ADO.Net Performance Test - Loading a DropDownList LINQ to SQL Vs ADO.Net Performance Test - Loading a GridView LINQ to SQL Vs ADO.Net Performance Test - INSERT LINQ to SQL Vs ADO.Net Performance Test - UPDATE LINQ to SQL Vs ADO.Net Performance Test - DELETE LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures with OUT Parameter

LINQ to SQL - Select Top N Rows

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

The below code will fetch all the rows from the Employee Table

EmployeeClassesDataContext dbContext = newEmployeeClassesDataContext();

var emp = from employees in dbContext.Employees 

          select employees;

grdEmployees.DataSource = emp;

grdEmployees.DataBind();

Now we will select only the Top 10 rows returned using the following LINQ query

EmployeeClassesDataContext dbContext = newEmployeeClassesDataContext();

var emp = (from e in dbContext.Employees

          select new { e.ID, e.Name, e.Phone }).Take(10);

grdEmployees.DataSource = emp;

grdEmployees.DataBind();

That’s it, Adding Take(n), filters the results and returns only the top n rows from the query.

Anonymous Types in LINQ

In this post Anonymous Types in LINQ, we shall see how Anonymous Types are used to capture output of LINQ expressions.

To know more about Anonymous Types refer the Post Anonymous Types in .Net 3.5

One of the main uses of Anonymous Types is to capture the output of LINQ expressions, the type and structure returned by a LINQ expressions will vary based on the Expression, it will be difficult to pre-define Types to hold the results returned by LINQ expressions,

Anonymous Types comes in handy in this situation, it accepts any type of result returned by the LINQ expressions without having to pre-define them.

In the below example we use LINQ to SQL to get the details of Employees, the results of the LINQ expression is captured in an Anonymous Type variable emp.

To know more about LINQ refer the Post What is LINQ?
To know more about LINQ to SQL refer the Post LINQ to SQL

Example:

EmployeeClassesDataContext dbContext = newEmployeeClassesDataContext();

var emp = (from e in dbContext.Employees

            select new { e.ID, e.Name, e.Phone });

grdEmployees.DataSource = emp;

grdEmployees.DataBind();

Notice that we are binding the Anonymous Type emp directly to the GridView grdEmployees, before executing the LINQ Query the type of emp is unknown, once the Query is executed, the Type emp stores the details of the Employees returned by the LINQ query and the same is bound to the GridView.

That’s it we have seen the usage of Anonymous Types in capturing results returned by LINQ Expressions

Related Posts

Anonymous Types in .Net 3.5

What is LINQ?

LINQ to SQL

LINQ to SQL Sample

LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures with OUT Parameter

In this post LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures with OUT Parameter we, shall compare the performance of LINQ to SQL and ADO.Net in executing a stored procedure call. This stored procedure takes the Department ID (int) as the input parameter and returns the Department Name (varchar(50)) as an OUT parameter

Asp.Net provides a number of Data Access Technologies, like ADO.Net, LINQ-to-SQL, Entity Framework etc, each one of these technologies has its own advantages and disadvantages, and while designing an application; we need to identify the appropriate data access technology to achieve maximum efficiency.

The configuration of the system used to perform the evaluation is as follows.

OS	Windows XP Professional 2002 SP3
Processor	Pentium® D 2.66 GHz
RAM	3 GB

The performance test was carried out for 25 iterations with both ADO.net and LINQ-to-SQL; the results of the test are as follows.

ADO.Net Average time:              1199.16 Microseconds
LINQ-to-SQL Average time:        1962.68 Microseconds

The test results show that ADO.Net has performed better than LINQ to SQL, the executions time is in the ratio of 2:3 between ADO.Net and LINQ-to-SQL.

The code used to perform the test is as follows

ADO.Net

SqlConnection objConn;

SqlCommand objCmd;

Stopwatch timer;

string strQuery = string.Empty;

timer = new Stopwatch();

timer.Start();

string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

objConn = new SqlConnection(strConn);

strQuery = "GetDepartmentName";

objCmd = new SqlCommand(strQuery, objConn);

objCmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramID = new SqlParameter("DepartmentID",SqlDbType.Int);

paramID.Direction = ParameterDirection.Input;

paramID.Value = 1;

SqlParameter paramName = new SqlParameter("DepartmentName", SqlDbType.VarChar, 50);

paramName.Direction = ParameterDirection.Output;

objCmd.Parameters.Add(paramID);

objCmd.Parameters.Add(paramName);

objConn.Open();

objCmd.ExecuteNonQuery();

objConn.Close();

timer.Stop();

lblExecutionTime.Text = "Execution Time (Micro Seconds):" + (1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString();

LINQ to SQL

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

string strDepartmentName = string.Empty;

Stopwatch timer;

timer = new Stopwatch();

timer.Start();

dbContext.GetDepartmentName(1, ref strDepartmentName);

timer.Stop();

lblExecutionTime.Text = "Execution Time (Micro Seconds):" + (1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString();

That’s it we have evaluated the performance of ADO.net and LINQ-to-SQL in executing a stored Procedure with an OUT Parameter. 

RELATED POST

LINQ to SQL Vs ADO.Net Performance Test - Loading a DropDownList 

LINQ to SQL Vs ADO.Net Performance Test - Loading a GridView

LINQ to SQL Vs ADO.Net Performance Test - INSERT

LINQ to SQL Vs ADO.Net Performance Test - UPDATE

LINQ to SQL Vs ADO.Net Performance Test - DELETE

LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures

LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures

In this post LINQ to SQL Vs ADO.Net Performance Test – Calling Stored Procedures we, shall compare the performance of LINQ to SQL and ADO.Net in executing a stored procedure call. This stored procedure returns 1000 rows; the result set returned by the procedure is bound to a GridView control.

Asp.Net provides a number of Data Access Technologies, like ADO.Net, LINQ-to-SQL, Entity Framework etc, each one of these technologies has its own advantages and disadvantages, and while designing an application; we need to identify the appropriate data access technology to achieve maximum efficiency.

The configuration of the system used to perform the evaluation is as follows.

OS	Windows XP Professional 2002 SP3
Processor	Pentium® D 2.66 GHz
RAM	3 GB

The performance test was carried out for 25 iterations with both ADO.net and LINQ-to-SQL; the results of the test are as follows.

ADO.Net Average time:              17.72 Milliseconds

LINQ-to-SQL Average time:        15.8 Milliseconds

The test results show that both LINQ to SQL and ADO.Net perform more or less similar with LINQ-to-SQL having a slight edge over ADO.Net.

The code used to perform the test is as follows

ADO.Net

SqlConnection objConn;

SqlCommand objCmd;

SqlDataAdapter objDA;

DataSet dsEmp;

Stopwatch timer;

string strQuery = string.Empty;

timer = new Stopwatch();

timer.Start();

string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

objConn = new SqlConnection(strConn);

strQuery = "GetEmployees";

objCmd = new SqlCommand(strQuery, objConn);

objCmd.CommandType = CommandType.StoredProcedure;

objDA = new SqlDataAdapter(objCmd);

dsEmp = new DataSet();

objDA.Fill(dsEmp, "dtEmp");

grdEmployees.DataSource = dsEmp.Tables["dtEmp"].DefaultView;

grdEmployees.DataBind();

timer.Stop();

lblExecutionTime.Text = "Execution Time (Milliseconds):" + timer.ElapsedMilliseconds.ToString();

LINQ to SQL

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

Stopwatch timer;

timer = new Stopwatch();

timer.Start();

var empList = dbContext.GetEmployees();

grdEmployees.DataSource = empList;

grdEmployees.DataBind();

timer.Stop();

lblExecutionTime.Text = "Execution Time (Milliseconds):" + timer.ElapsedMilliseconds.ToString();

That’s it we have evaluated the performance of ADO.net and LINQ-to-SQL in executing a stored Procedure which returns 1000 rows. 

RELATED POST

LINQ to SQL Vs ADO.Net Performance Test - Loading a DropDownList 

LINQ to SQL Vs ADO.Net Performance Test - Loading a GridView

LINQ to SQL Vs ADO.Net Performance Test - INSERT

LINQ to SQL Vs ADO.Net Performance Test - UPDATE

LINQ to SQL Vs ADO.Net Performance Test - DELETE