Sunday, January 30, 2011

Introducing ADO.NET Entity Framework

This is the third and final part of my ORM series in which I am going to introduce the ADO.NET Entity Framework, an in-built Object Relational Mapping model of the .NET Framework.

Similar to the previous post, this one also covers the same four principles -

  • Configuring a .NET project for the ADO.NET Entity Framework
  • Inserting data from Objects directly
  • Retrieving data using Object Lists an LINQ
  • Changing the Database Management System

Though the screen-shots and the example codes emphasize on C#, the principles are same for all the .NET languages.

Requirements to run the example in this article - Visual C# Express, SQL Server Express and MySQL. Download Visual C# Express here, SQL Server Express here and MySQL here.

Configuring a .NET project for the ADO.NET Entity Framework

To configure a .NET project to work with the ADO.NET Entity Framework, an Entity Data Model is added to the project. The Entity Data Model is primarily a schematic representation of the database tables stored as a XML file. Each of these tables is converted into a class and foreign key relationships between these tables are maintained as Lists inside the objects.

Visual Studio provides a Wizard to create Entity Data Models. Right click on the project and select New Item from the Add menu. Choose the ADO.NET Entity Data Model template from the chooser. There are two approaches available to build the Entity Data Model - Database First Approach and the Model First Approach.

In the Database First Approach, the Entity Data Model is generated from existing table structures. The wizard allows the developer to setup a connection by providing the Database Server name and the Database name. The wizard also allows developers to choose tables, views and stored procedure that are to be a part of the Entity Data Model.

In the Model First Approach, the Entity Data Model is created from scratch and the database tables are generated based on this model.

Once the wizard completes, a designer opens up which shows a schematic representation of the generated .edmx file. This file contains the table mappings as a XML along with a code behind file with a .Designder.cs extension for the auto-generated classes corresponding to the tables. However a drawback with the Entity Data Model is that it combines all the classes into a single file which makes manual maintainence a little difficult.

I used the Database First approach to create the sample application to insert and retrieve data. You can get the MySQL and SQL Server scripts along with the Visual Studio solution here.

Inserting data from Objects directly

The Entity Model generates a class which inherits from ObjectContext. This class acts as a data manager to connect to the Database Management System to retrieve, insert, update and delete records.

ADO.NET Entity Framework maintains the records of the table as a List of objects. To insert new records into the table, create new objects, add them to the appropriate lists and then save the changes using a ObjectContext instance. The following piece of code shows how objects can be persisted -

using (Context context = new Context())
// Create the object

ADO.NET Entity Framework does a wonderful job while storing objects with foreign key dependencies. These dependencies are maintained using lists as objects and while storing these records, the appropriate identity keys are inserted into the child tables. However this feature is limited to a few DBMS like SQL Server.

Retrieving data using LINQ and Object Lists

The ADO.NET Entity Framework retrieves data from the back-end tables in the form of object lists. So accessing records is as simple as iterating through these lists.

using (Context context = new Context())
foreach (Object object in context.Objects)
// use the object appropriately

Since data retrieval is in the form of lists, developers can piggy-back on an other .NET framework feature - LINQ (Language Integrated Query). LINQ makes conditional querying of data a lot easier.

using (Context context = new Context())
var objects = from object in context.Objects where "condition" select object;
// use the objects

Changing the Database Management System

Before changing the DBMS of the Entity Model, it is important to understand how the ADO.NET Entity Framework stores the connection strings and the mapping between classes and the back-end tables. The connection string is stored in the App.Config file of the project and the table mappings are stored as a XML in the form of the .edmx file as mentioned earlier.

Unfortunately the ADO.NET Entity Framework varies it's implementation with the DBMS. Because of this modifying the XML manually isn't easy. For Example, ADO.NET Entity Framework does not support foreign key constraints in the form of lists for DBMS like MySQL.

The sample application contains another Entity Model which connects to a MySQL Server containing similar tables. To use MySQL with the ADO.NET Entity Framework, an connector is needed. The MySQL Connector/NET is available here.


Future said...

Thank you so much for the tutorial. It helps alot.

Maethee Riwsawai said...

Cannot change the Database Management System to MySQL. Visual Studio Express does not support MySQL .Net Connector extension. Are you sure you do this on VS Express edition?

Sudhir DBAKings said...

Nice post very helpful

girls games said...

Great post. i like it. feeling great when reading your post . friv

descargar whatsapp said...

This is a great article. It gave me a lot of useful information. thank you very much.
Versión en facebook en español descargar a los países hablan Español: facebook entrar direto agora , facebook en español para and facebook entrar direto , baixar whatsapp

facebook said...

This article is really fantastic and thanks for sharing the valuable post.
Jugar juegos de frozen en línea gratis, los nuevos de princesa de Disney juegos frozen - la princesa encantadora y linda. Divertirse frozen!

Facebook Descargar said...

Great post,Thanks for providing us this great knowledge,Keep it up.
A good blog..
download descargar facebook gratis para Android celular and download free descargar facebook apk en español, descargar facebook plus , descarga facebook

iniciar sesion facebook entrar said...

Great post,Thanks for providing us this great knowledge,Keep it up.
A good blog.
facebook entrar iniciar sesion gratis - Sitio Oficial iniciado sesión en Facebook lengua española. facebook entrar rápido, facebook iniciar sesion en tu cuenta de entrar facebook

Anh Mai said...

Thanks for sharing. I hope it will be helpful for too many people that are searching for this topic.
Versión en facebook en español descargar a los países hablan Español: facebook entrar direto agora , facebook en español para and facebook entrar direto

Mít Trang said...

Its really good information, i like your blog post. Thanks for sharing
download baixar facebook movel, Facebook movel, baixar facebook movel. Facebook chat, baixar whatsapp gratis, fazer o download baixar whatsapp para Android, iPhone. Últimas Facebook

games unblocked said...

This article is really fantastic and thanks for sharing the valuable post.
The place to play all unblockedgames online. Here you can find every blocked games such as: unblocked games , unblocked games happy , unblocked games 77 , gmod

kovalan Jayamurugan said...

Thanks for sharing such informative guide on .Net technology. This post gives me detailed information about the .net technology. I am working as trainer in leading IT training academy offering Dot Net Training in Chennai and i use your guide to educate my students.

games unblocked said...

This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this.
kids games online , friv 2 , jogos do friv , , juegos de matar zombbies
, juegos de un show mas

thị hậu nguyễn said...

I appreciate your blog post, beautifully expressed and well written.
happy wheels
super mario bros

Niranjan R said...

Nice post. We appreciate that. Given that ASP.NET is interested in many years, there are numerous developers that are expert at utilizing it to develop the best of apps. Establishing an application in Dot net training in Chennai with the assistance of this systems fairly cost-effective. The easy energy for this will allow the creators to perform the granted occupation inside the stipulated time period. And this also permits supply of quality products and services in dot net platform.Take a look to my blog Best Dot Net Academy in Chennai

Roshini RS said...

In recent days Angular plays vital role to loading your site content in a fastest way, so it’s a required skill for everyone, thanks for sharing this useful information to our vision keep blogging.
Angularjs course in chennai|Node JS training|Angularjs training in chennai

varshini devi said...

This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing...
Python Training in Chennai

Friv4 Games Online said...

Really i appreciate the effort you made to share the knowledge.The topic here i found was really effective to the topic which i was researching for a long time
i like play happy wheels demo online and play happy wheels games full and Download retrica app includes more than eighty different filters with many different styles and include retrica , and play zombie tsunami game is the ideal game for anyone who loves the running game genre

Naviya Nair said...

I have read your blog its very attractive and impressive. I like it your blog.

.Net Training in Chennai | .Net Online Training | Dot Net Training in Chennai

Dot Net Online Training | LINQ Online Training

Giải Trí Tổng Hợp said...

I was working and suddenly I visits your site frequently and recommended it to me to read also. Age Of War 2
Big Farm | Slitherio
Tank Trouble | Happy Wheels
Goodgeme Empire |

Unknown said...

Hi! I’ve been reading your blog for a while now and finally got the
Cookie Clicker
Cookie Clicker play
Cookie Clicker game
Cookie Clicker online

Love Kpop said...

Not all are true. Everyone has their own way of thinking but I think they have to reconsider. I like to argue for the most accurate results.

Search Optimization said...

Your blog is very unique and interesting. It makes reader to come back and visit again.

Selenium Training in Chennai

harry lopiz said...

I like your all post. You have done really good work. Thank you for the information you provide, it helped me a lot. I hope to have many more entries or so from you.