En Ru
Advantages and risks of migration
to ADO.Net Entity Framework

Migration to ADO.Net Entity Framework

31.03.2009
We have widely used ADO.Net and LinqToSql technologies to work with database entities in outsourcing development of dispatch applications. Recently we tried to implement data layer in one of dispatch application on the base of Entity Framework and examine what benefits such migration brings.

      Content

Management summary


Nowadays Microsoft's data strategy is solidly focused on conceptual models and the ADO.Net Entity Framework is the new useful tool for .NET developers to build applications that leverage these models.
The Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.
             Useful related links:












The ADO.NET Entity Framework provides several benefits that make a migration to it undoubtedly attractive:
  • Better architecture. One of the most important benefits is the ability to use the Entity Data Model to separate data structures used by the application from the schema in the data source. This provides opportunity to make future changes easily to the storage model or to the data source itself (tables and views reorganization, import new stored procedures, etc) without making corresponding changes to the application.
  • Easy development and maintenance. Many handy visual tools instead XML configuration file editing, simpler LinqToEntities queries instead of native SQL, standard implementation of entities inheritance, less code to load and store with Entity Framework class library make development with the Entity Framework easier and more comprehensible.
  • Quality and reliability growth. Applications are free from hard-coded dependencies on a particular data engine or storage schema. Wide usage visual tools for automatic code generation and automatic data models validation (on the base of data model metadata and used data source) during both development process and runtime appreciably reduce probability of errors.
  • Performance optimization. The Entity Framework has highly optimized LINQ solution. The Entity Framework also provides various methods of data loading which in combination are able to increase application performance.
  • Safety of investments. As Microsoft announces they are going to base their future products on Entity Data Model and Entity Framework technologies that means that the Entity Framework is considered as main database access technology from Microsoft. Therefore for safety of investments we would highly recommend to base all new development and also new versions development on ADO.NET Entity Framework.
  • Database independence. The Entity Framework works against any database for which there is an ADO.Net provider (MS SQL Server, Oracle, DB2, MySql, etc).
One important note is that for a large or complex application, you are not required to migrate the whole application to the Entity Framework at one time. However, any part of the application that does not use the Entity Framework must still be changed when the data source changes.


 

Introduction


The ADO.Net Entity Framework is a new data access platform from Microsoft. It was released in the second quarter of 2008 as part of the Visual Studio Service Pack 1 and .Net 3.5 Service Pack 1. Although the existing data access features remain in ADO.Net, this new framework is part of Microsoft's data access strategy.


 

Quick start


Using Entity Framework starts with adding ADO.Net Entity Data Model template in the project:

Form: Add New Item

As we use Visual Studio wizard for Entity Data Model creation we can easily select data source, define connection string, our data model namespace and mark required database objects to import into our data model (tables, views and stored procedures).
The first interesting nuance is concerning the changed connection string format. Wizard generates the following connection string

<connectionStrings>
<add name="ContactViewEntities" connectionString="metadata=res://*/DataEntity.ContactViewEntityDataModel.csdl|res://*/DataEntity.ContactViewEntityDataModel.ssdl|res://*/DataEntity.ContactViewEntityDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=TEST-SERVER1\SQLEXPRESS;Initial Catalog=ZHKH;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

where, in addition to information required for connection to the data source, is placed extra information about allocation of data model metadata files and entity provider’s name. As a result, application that support Entity Data Model cannot use without extra transformation connection strings that defined in the classical format.

Visual Studio provides 3 new windows to support Entity Data Model:
  • Model designer – to make changes in the data model (we also can use any xml-editor for manual editing of Entity Data Model source files);
  • Model Browser – to surf the data model;
  • Mapping Details – to map data entities and their properties to database tables and views, and map stored procedures to data entities.

Form: Data model

In our application, we have created the following data model with help of the Visual Studio Wizard.

Form: Data model

  • It’s a good news that migration to ADO.Net Entity Framework allows us to inherit data entities within the model that was impossible in classical ADO.Net and LinqToSql. I think it can far simplify work with complicated hierarchic data models. For instance, in our data model we have entities Diapatcher and Administrator which are inherited from User entity.
There are two models of inheritance in Entity Framework:
  • Table-per-Hierarchy inheritance. One table in storage maintains data for all the types in an inheritance hierarchy. So you have one table in database, but different types in your entity model that inherits from a base class and all mapped to that table.
  • Table-per-Type inheritance. Separate table in storage to maintain data for each type in the inheritance hierarchy. That means you might have several tables with one-to-one relationships. Each table is mapped to single entity in the model. But there is a base entity that is mapped to the very base table.
In our data model, we use three different entities sharing single table (Table-per-Hierarchy). These entities are:
User: Type = 0
Dispatcher: Type = 1
Administrator: Type = 2

To map different entities to the same database table we use condition properties:

Form: Mapping Details - Administrator

For every data entity, which is added in the model Visual Studio creates class (that inherits System.Data.Objects.DataClasses.EntityObject) which define this data entity.
  • As a positive moment I would mention that all data entity classes marked as serialiseble. That means that we can easily transmit our data entities through .Net Remoting and WCF layers (without building any extra wrappers and customising serialisation procedure).

[System.Serializable()]
public partial class Category : System.Data.Objects.DataClasses.EntityObject
{
   .....

The top level of the data model (so called Object Services) is implemented as class (Entity container) which inherits System.Data.Objects.ObjectContext. This class provides high level operations for interaction with datasource and entities. For every data entity that is added in the data model the entity container contains corresponding generics collections that used to retrieve data from data source.

public partial class ContactEntities : System.Data.Objects.ObjectContext
{
   .....

   public System.Data.Objects.ObjectQuery<Category> Categories
   {
      get
      {
         if ((this._Categories == null))
         {
            this._Categories = base.CreateQuery<Category>("[Categories]");
         }
         return this._Categories;
      }
   }
   private global::System.Data.Objects.ObjectQuery<Category> _Categories;
  
   .....
   
Migration to the Entity Framework gives oportunity to use in the application entity objects with integrated business logic instead of awkward datatable/dataset objects of classic ADO.Net or extra class-wrapers for LinqToSql objects.  Developers can work with an application object model that can be mapped to various storage schemas, possibly implemented in different DBMS.

 
 

Using stored procedures with the data model


The Entity Framework provides us with a good opportunity of automatic generation of SQL commands for all entities in our data models. However, we decided not to use auto generated SQL queries for exchanging data between data source and data model but use a set of stored procedures. It does not mean that automatic queries work inefficiently, but in complex dispatch applications we often have to make some extra efforts to increase application performance and one of possible solutions to do this is using of stored procedures.

A stored procedures mapping provides in the Mapping details windows. Everything looked quite easy at first but during stored procedures mapping we encountered some challenges:
  • The first, if we map only a single stored procedure for the data entity we must create and map all supported types of stored procedures (for inserting, updating and deleting) to all data entity that have associations with that single one. It looks a rather complicated as we have to create a huge amount of stored procedures if we want to optimize only a single entity model.
  • The second, for data entities with associations we must use stored procedures, which take as parameters all entity data properties, which are included into association between entities even if we do not use them within the stored procedure.
  • The third, currently stored procedure mapping does not support OUT and INOUT parameters of stored procedures and data entity properties can be updated only by mapping to result columns of stored procedure.
All above mentioned showed in the following sample.

Form: Mapping Details - Contact

ALTER PROCEDURE dbo.RegisterNewContact
    (
    @UserId int,
    @RegionId int = null,
    @ControlLevelId int = null,
    @ContactStatusId int,
    @CategoryId int = null
    )
AS
SET NOCOUNT ON
INSERT INTO [ZHKH].[dbo].[Contact]
           ([RegistrationDate],[ContactStatusId],[UserId])
     VALUES
           (getdate(),@ContactStatusId,@UserId);
DECLARE @NewId int;
SELECT @NewId = IDENT_CURRENT ('Contact');
INSERT INTO [ZHKH].[dbo].[RefinedData] ([ContactId]) VALUES (@NewId)
select ContactId, RegistrationDate from [Contact] where  [ContactId] = @NewId
RETURN

Visual Studio provides mapping stored procedures for data modification operations, but how about data retrieving through stored procedures. For this reason, we should import required stored procedure in to our data model.

Form: Add Function Import

After that, we can call this stored procedure from the object services level:

DataEntity.Contact contact = null;
using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   contact = context.GetContact(contactId).First();
}

There is a potential problem here. If there are no items, First will throw an InvalidOperationException. FirstorDefault method protects us from the exception by returning the default, which is generally a null.
How we see we can easily customize process of receiving/saving data by using stored procedures. Only is a problem with it by for now, unfortunately the Entity Framework does not directly support queries that return multiple resultsets.

The Entity Framework provides an easily way for mapping between the conceptual model and the storage-specific schema can change without changing the application code – for this, you only have to change data model mapping using Visual Studio model designer.


 

Deferred and Eager data loading


The Entity Framework will only return data that we explicitly ask for. If our query asks only for contacts, the Entity Framework will not make an assumption that just because contacts have associations with another entities (e.g. RefinedData, ControlLevel, User, etc), it should return all this data anytime we query for contacts. Entity Framework provides two different ways of data loading.
The first, called Deferred loading (also known as lazy loading), uses method Load. When Load is called, ObjectContext class executes a query to retrieve from storage all associated items. In the following example, only after Load is called, all of the RefinedData.Enterprise and RefinedData.Enterprise.Region entities for that contact will be available.
However, if we know we will need all of the data related to contact entity, it may be more efficient to retrieve them as part of the particular query. The Include method is a way to achieve this and may be preferable for a variety of reasons. This way of data loading called Eager loading.

DataEntity.Contact contact = null;
using (var context = new DataEntity.ContactEntities(new EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
      // Eager loading
      contact =   context.Contacts.Include("User").Include("Category").Include("ContactStatus").Include("ControlLevel").Include("RefinedData").Include("Region").First(c => c.ContactId == contactId);
     
      // Deferred loading
    If(contact.RefinedDatas.IsLoad())
    {
         contact.RefinedData.EnterpriseReference.Load();
         if (contact.RefinedData.Enterprise != null)
         {
            contact.RefinedData.Enterprise.RegionReference.Load();
         }
    }
   }
}

The migration to ADO.Net Entity Framework provides more flexible way of data loading. We can balance application performance by combining the described methods of eager and deferred loading.


 

Modifying Entities and Saving Changes


ObjectContext class has a single method, SaveChanges, which saves to the database all of the changes made to the entities.

using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   var user = new User;
   user.Login = “Alex”;
   user.Password = “123456”;
   context.AddToUsers(user);
   context.SaveChanges(true);
}

ObjectContext automatically recognizes the operation type that should be called for every saved entity. As for instance, when the newly created user object is linked with the contact, ObjectContext recognizes that the user has no identity key and sets its EntityState to Added. When SaveChanges is called, an Insert command is called for the user entity.

DataEntity.Contact contact = null;
using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   contact = context.GetContact(contactId).First();
   var user = new User;
   user.Login = “Alex”;
   user.Password = “123456”;
   contact.User = user;
   context.SaveChanges(true);
}

The last type of modification is deleting entities.

using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   var contactKey = new EntityKey("ContactEntities.Contacts", "ContactId", contactId);
   var contact = context.GetObjectByKey(contactKey);
   context.DeleteObject(contact);
   context.SaveChanges();
}

In above sample we use another useful method of ObjectContext class GetObjectByKey which provides simple way of retrieving entity by its identity key.


 

Transactions


When performing a SaveChanges operation, the Entity Framework implicitly wraps all of the commands in a database transaction.

DataEntity.Contact contact = null;
using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   contact = context.GetContact(contactId).First();
   context.DeleteObject(contact);
   var user = new User;
   user.Login = “Alex”;
   user.Password = “123456”;
   context.AddToUsers(user);
   context.SaveChanges(true);
}

A transaction is created, and if the insert operation failed, the transaction is rolled back including delete operation.
We can control transaction scope as well. If we explicitly create our own transaction, SaveChanges will not implicitly create a transaction and we need to use a System.Transaction.TransactionScope object.

DataEntity.Contact contact = null;
using (var context = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
{
   contact = context.GetContact(contactId).First();
   context.DeleteObject(contact);
   using (TransactionScope tran = new TransactionScope())
   {
      try
      {
         context.SaveChanges(false);
         using (var altcontext = new DataEntity.ContactEntities(new      EntityConnection(ConfigurationManager.ConnectionStrings["ContactEntities"].ConnectionString)))
         {
            var user = new User;
            user.Login = “Alex”;
            user.Password = “123456”;
            altcontext.AddToUsers(user);
            altcontext.SaveChanges(true);
         }
         tran.Complete();
         context.AcceptAllChanges();
      }
      catch(Exception ex)
      {
         Trace.TraceError(ex.ToString());
      }
   }
}


If you are interested to get proposal for development based on ADO.Net Entity Framework or other .NET technology, please send us your request.

Ihar Hryhorchyk, Development Group Manager
Posted by Ihar Hryhorchyk,
 Development Group Manager
Bookmark or share:
Digg Stamble Upon Facebook Technorati Twitter Mr. Wong GoogleLinkedIn Delicious