Tuesday, April 14, 2009

Entity Framework in .Net

Eliminate the impedance mismatch between data models and between languages that application developers would otherwise have to deal with new features available in the upcoming version of ADO.NET.

Key Points
  • This introduces a new model called Entity Data Model (EDM)
  • It uses a new provider called MapProvider.
  • The mapping provider is given the EDM schema and the mapping information, so it can internally use the mapping infrastructure to translate between the logical and conceptual schemas.
  • Integration with LINQ(Language Integrated query): This query written using LINQ will be processed by the compiler, which means that you'll get compile-time validation as the rest of the application code would.
  • Visual Studio tools can generate EDM Schema from logical and conceptual schema.
SELECT sp.FirstName, sp.LastName, sp.HireDateFROM SalesPerson sp INNER JOIN Employee e ON sp.SalesPersonID = e.EmployeeIDINNER JOIN Contact c ON e.EmployeeID = c.ContactID WHERE e.SalariedFlag = 1 AND e.HireDate >= '2006-01-01'

In the above query we have made unnecessary join to the Contact and Employee table even though we concern only about person data. Now that we have a higher-level EDM model, we could write this same query against a set of SalesPeople entities as:

SELECT sp.FirstName, sp.LastName, sp.HireDateFROM AdventureWorks.AdventureWorksDB.SalesPeople AS spWHERE e.HireDate >= '2006-01-01'

So, for example, to execute a query against the EDM model that finds the names and hire-dates of the sales people after a given hire date, the ADO.NET code would be:

using(MapConnection con = new MapConnection(connectionName))
MapCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT sp.FirstName, sp.LastName, sp.HireDate " + "FROM AdventureWorks.AdventureWorksDB.SalesPeople AS sp " + "WHERE sp.HireDate > @date"; cmd.Parameters.AddWithValue("date", hireDate);
DbDataReader r = cmd.ExecuteReader();
while(r.Read()) {
Console.WriteLine("{0}\t{1}", r["FirstName"], r["LastName"]);

Steps to create a project using .net Entity Framework

1. Creating an Entity Data Model using ADO.NET Entity Data Model (.csdl file)

2. Using Entity Client
using (EntityConnection cn = new

NorthwindEntities is the connection string created automatically while creating data model.

3. Working with Objects Using ObjectQuery (Of T)

4 Using LINQ to Entities to Query your database

creating sample project is here