I have seen dozens of projects in twenty years and almost every one of those has people spending time writing database plumbing code. This code exists to move data to and from a data repository to structures or objects (depending on the decade). The problem is that all of this code basically solves the same problem: getting data from a storage repository in one format into memory in another format.
For a while, there were companies working on storing data in object databases the same way one would store it memory. For whatever reason—institutional dependence on existing relational databases or something else—object-oriented databases don’t seem to have crossed the chasm into mainstream software development. Consequently, there is a problem—how to get relational data into objects that are structured differently, call an impedance mismatch—and because many groups are re-engineering solutions, the problem begs for programming tools vendors to solve it.
Microsoft is working on a solution that leverages LINQ (Language INtegrated Query). The solution is called the ADO.NET Entity Framework and it is part of ADO.NET 3.0. Going forward, you will have the option to choose between ADO.NET style database code or ADO.NET 3.0, the Entity Framework, and LINQ. It is assumed you know how to use the current version of ADO.NET, so this article talks about the Entity Framework style of programming.
Rumor has it that ADO.NET 3.0 and the Entity Framework will ship with Visual Studio 2008 SP1 sometime this year. You will have to download the alpha bits to try the demo. Finally, ADO.NET 3.0 introduces Entity SQL (or eSQL), which is SQL for entities and different than the approach leveraging LINQ in this article. In short, you can use eSQL, you can use LINQ, or you can mix and match. (In reality though, eSQL seems to be a case of the right hand and left hand not communicating, and I will be emphasizing my efforts on LINQ and not eSQL).
So, it’s time to begin.
Downloading the ADO.NET Entity Framework Preview
The Entity Framework is in alpha. At last check, the release version is 0.3. The point really is that the ADO.NET Entity Framework leverages the rock solid .NET Framework and the rock solid LINQ, so it’s pretty safe to download and use. Of course, it’s always worth mentioning that if you can avoid use alpha and beta bits on a production workstation or in a virtual machine, do so.
You can download the ADO.NET Entity Framework by Googling for it or try this link: http://www.microsoft.com/downloads/details.aspx? FamilyId=15DB9989-1621-444D-9B18-D1A04A21B519&displaylang=en. The download is a couple of megabytes and downloads and installs very quickly.
Taking the Entity Framework Out for a Spin
Entity Framework bits can be added to any project. To keep the demo simple, this example uses the AdventureWorks database that comes with SQL Server 2005 (and SQL Server Express 2005), Visual Studio 2008, the .NET Framework 3.5, VB9, and the ADO.NET Entity Framework 0.2.
To prepare for the demo, you will need to complete these steps:
- Install the ADO.NET Entity Framework bits.
- Create a VB Console Application.
- Add a connection in Visual Studio 2008 to the AdventureWorks database.
- From the Add New Item dialog, add ADO.NET Entity Data Model item from the list of templates. Follow the wizard steps.
- Write some code that queries the entity model.
The sections that follow describe the various steps in detail. (Downloading and installing the entity framework was already covered.)
Creating a Console Application
A console application is a program that interacts with the user without a GUI. All of the project types and item types for projects are installed with Visual Studio as file and project stubs. When you select them, a wizard runs and fills in the blanks, such as file and class names. The wizard is a general wizard that leverages some JScript and configuration files that contain the missing knowledge.
Tip: You can write custom wizards and install them in Visual Studio 2008.
Adding a Connection to the AdventureWorks Database
Our old friend Northwind has gotten a bit stale of late, so there is a gentle nudge to begin using the more detailed AdventureWorks database for samples. That’s fine. Plus, Northwind doesn’t ship with SQL Server 2005 anymore anyway.
To connect to the AdventureWorks database in Visual Studio 2008:
- Select View|Server Explorer.
- In the Server Explorer, click the Connect to Database toolbar button. (This will open the Add Connection dialog. Fill it out as shown in Figure 1.)
- In the Add Connection dialog, type .\SQLExpress for the Server name.
- Select Use Windows Authentication.
- Select the AdventureWorks database name.
- Click Test connection.
- If the test succeeds, click OK; if not, you may not have the AdventureWorks database installed.
Figure 1: Adding a data connection to the sample database.