Friday, March 20, 2009

The ADO .NET Entity Framework

During the development cycle of a database application, the developer may face issues having to do with the fact that the logical (relational) and related infrastructure may not match the application’s needs. This happens as relational schemas are often normalized in such a way, for being able to be shared across multiple applications.

Still, this does not allow the relational schemas to be somehow “aware” of the business concept of the database application. As a result, developers often find themselves in the position of trying to remember whether a database table is called Employee or Employees, how the employees of the various departments are separated within the same table and so on. This requires going over the database’s schema diagram repeatedly which is a task consuming valuable time.

The ADO .NET Entity Framework provides a solution to this problem by allowing the creation of conceptual models mapped to the corresponding relational models. In order to understand the concept of the ADO .NET Entity Framework, first you have to understand what the Entity Data Model is. This is actually the core concept of the Entity Framework.

The Entity Data Model (EDM) is a conceptual model of problem space. It introduces an object model which is independent of any storage considerations. Within this model the Entity is introduced. An entity is an instance of an Entity Type (i.e. an SQL Server Database Table). Entities can contain relational database types (e.g. int, varchar) as well as multi-property types (e.g. address). Entities are grouped in Entity-Sets. EDM also introduces Relationships. Relationships associate entities and are grouped in Relationship-Sets.

The ADO .NET Entity Framework technology features an architecture where there are the ADO .NET 2.0 Data Providers which provide access to the various DBMSs and on top of these there is the EntityClient Data Provider which is very similar to an ordinary .NET provider. This provider's "database" is the Entity Data Model. This allows Object Services which is on top of the abovementioned components, to support various levels of access and services to the conceptual schema like: Change Tracking, Object Identity, Overriding INSERT/UPDATE/DELETE with stored procedures, Column-level concurrency for updates, System.Transactions or explicit transactions. Object Services also support LINQ to Entities which is a very powerful way for querying the conceptual model.

A simple procedure of using the Entity Framework is the following:

1. Connect to a DBMS via an ADO .NET Provider.
2. Create the conceptual model (EDM) by mapping database entities to conceptual entities.

Then, you can access/query the conceptual model either by using Entity SQL (a special version of SQL language containing certain extensions), or by using Object Services, or by using LINQ to Entities. The latter is a very powerful way of accessing the conceptual model because it provides all the benefits that LINQ offers like: compile-time syntax checking, intellisense and much more!

Now, if the relational model (database) and/or the mappings change, this will not be a problem for the developer as he/she will still be able to query the same conceptual entities without requiring to change anything in the code!

Please keep in mind that for being able to use the ADO .NET Entity Framework you will need the Microsoft .NET Framework 3.5 and Visual Studio 2008.

SQL Server 2008 provides significant support and many features for use with the ADO .NET Entity Framework. More information can be found on this link.

There is also excellent online documentation regarding the ADO .NET Entity Framework and LINQ to Entities. More information is available on this MSDN Library link.

[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: