ADO.NET 3.0 Framework - Entity Data Model - Entity SQL - LINQ - A Thing of Beauty
by David Hayden ( Florida .NET Developer )
If you want to get up close and personal with the future of ADO.NET and the ADO.NET 3.0 Framework, you have to check out the ADO.NET 3.0 Framework Screencasts. Shyam Pather, the Development Lead on the ADO.NET 3.0 Team, has done an excellent job of presenting the new ADO.NET 3.0 features that shows you how wonderfully the ADO.NET Team has built an architecture that is a perfect blend of the “old” ADO.NET 2.0 Data Access Model and new Entity Data Model, complete with Entity SQL and LINQ. I am really impressed with how the ADO.NET Team provided an easy and intuitive migration path from todays existing Connected ADO.NET to an Entity-Based View of the Database.
The screencast is in 2 parts:
Part 1: Introduction
0:00-2:25 Intro and demo of basic ADO.NET 2.0 code.
2:25-8:00 Creating a conceptual data model
8:00-12:30 Using the Map Provider to query with Entity SQL
12:30-16:05 Explicit relationship navigation in Entity SQL
16:05-17:50 Accessing result metadata via IExtendedDataRecord
17:50-21:55 Polymorphic Queries
21:55-23:40 Filtering on entity type at the server
Part 2 builds on this and covers the following additional topics:
0:00-6:15 Obtaining results as objects
6:15-9:34 Polymorphic queries with results as objects
9:34-11:53 Removing the connection handling code
11:53-14:48 Using LINQ to express queries
14:48-21:02 Adding and updating entities
Check out the blog post here that points you to the screencasts.
Here is my summary.
Entity Data Model
The heart of the ADO.NET 2.0 Framework is the Entity Data Model, which is essentially a conceptional view of the database schema created by you, the developer. Under the covers, this view is described as an XML Mapping File in your application, where entity properties and relationships are mapped to database tables and FK relationships. This mapping abstracts your application from changes to the relational database schema. Rather than changing your application when a change occurs to the database schema, you ownly change the XML Mapping File to reflect that schema change without changing any source code.
Now let's say you have several applications that access the same database, but they each want to look at the database from a different view. No worries! Each can create and share their own Entity Data Model, which allows each application to work with the database in a way that offers an easier and more expressive view of it as defined by their problem domain.
Migrating Connected ADO.NET to the Entity Data Model
There is an incredibly easy migration path from Connected ADO.NET 2.0 by using the new Mapping Provider as opposed to the .NET Database Providers for the specific database. Hence this
using (SqlConnection connection = new SqlConnection(...))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "...";
using (IDataReader dr = command.ExecuteReader())
{
// ... Do things ...
}
}
}
would only cause a change from SqlConnection to MapConnection and SqlCommand to MapCommand to take advantage of the Entity Data Model:
using (MapConnection connection = new MapConnection(...))
{
using (MapCommand command = connection.CreateCommand())
{
command.CommandText = "...";
using (IDataReader dr = command.ExecuteReader())
{
// ... Do things ...
}
}
}
As you can see above, the application has been abstracted from the actual database. This application is not only independent of the database schema, but also the database. This is a 2-for-1 special and I like it.
From Database Schema Specific T-SQL to Entity SQL
Since the Mapping Providers ( via MapConnection and MapCommand ) are now querying against our Entity Data Model ( e.g. are preferred application view of the database schema ), the queries often become much easier to write and understand, because we don't have to query against a normalized database schema that can be complicated with multiple joins. Hence, even though the information on a SalesPerson may span several tables in the database, our Entity Data Model views SalesPerson as a single entity, with all the information about that SalesPerson as properties of the entity.
T-SQL against the database schema may look like this with several joins across tables:
SELECT Employee.Name, Region.Name
FROM SalesPeople
INNER JOIN
Employees ON SalesPeople.EmployeeId = Employees.EmployeeId
INNER JOIN
Region ON SalesPeople.RegionId = Region.RegionId
Using Entity SQL based on our wonderful Entity Data Model, the Entity SQL could look like this:
SELECT Name, Region FROM SalesPeople
as Region and Name, which are located in different tables in the database schema, are properties of our SalesPerson Entity in the Entity Data Model. By the way, I just made up those queries on the fly, so they may not be accurate, but you get the idea :)
I am already impressed, because the ADO.NET Team has removed the dependency of my database application on a particular database and database schema by introducing an Entity Data Model, which consists of my custom application view of the data ( domain model ) and a mapping layer described by an XML file. This in itself is a giant leap forward to uncoupling our applications from the database.
Using Entities in Entity Data Model
The use of the MapConnection and MapCommand objects above are great when you are working with legacy code, but if you are creating a new application you will probably want to use the entities in the Entity Data Model directly. Instead of working with MapConnection and MapCommand objects above, you will probably use code as such:
using (CompanyDB db = new CompanyDB())
{
Query<SalesPerson> salesPeople =
db.GetQuery<SalesPerson>(
"SELECT * FROM SalesPeople");
foreach (SalesPerson sp in salesPeople)
{
...
}
}
You can see that we have removed the concepts of connections and commands and focued solely on the Entity Data Model and Queries using Entity SQL. The idea of a database is becoming less and less a part of our application.
Using LINQ
Further evolving our example to use LINQ, we get the following strongly typed code that eliminates the use of Entity SQL:
using (CompanyDB db = new CompanyDB())
{
var salesPeople =
from c in db.SalesPeople
select c
foreach (SalesPerson c in salesPeople)
{
...
}
}
Aside from my poor naming of CompanyDB, is there a database here? I am not so sure and I don't really care :)
Conclusion
The ADO.NET 3.0 Framework is getting us closer and closer to real-world development IMHO. Database applications that were once tied to a particular database and database schema become less coupled by using a view of the database schema, called the Entity Data Model. The Entity Data Model provides a richer and more intutive view of the data model from the application's perspective as opposed to dealing with a more normalized view of the data as based on optimal storage.
Futher support for Entities and LINQ provides us further abstraction and type safety that allows us to build better and less error-prone applications that contain more business specific information and less database specific code. Add to this the code generation capabilities that will be wrapped around this by the Visual Studio IDE, and you create less and more quality code in a much faster timeframe.
I look forward to the CTP, which is expected to show up sometime in August. Expect a ton of examples on the new ADO.NET 3.0 Framework as I can hardly contain my excitement.
Source: David Hayden ( Florida .NET Developer )
Filed: ADO.NET 3.0