LINQ to Entities Preview

Introduction

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:

1. Install the ADO.NET Entity Framework bits.
2. Create a VB Console Application.
3. Add a connection in Visual Studio 2008 to the AdventureWorks database.
4. From the Add New Item dialog, add ADO.NET Entity Data Model item from the list of templates. Follow the wizard steps.
5. 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:

1. Select View|Server Explorer.
2. 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.)
3. In the Add Connection dialog, type .SQLExpress for the Server name.
4. Select Use Windows Authentication.
5. Select the AdventureWorks database name.
6. Click Test connection.
7. 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.

Adding ADO.NET Entity DataModel Elements

Entity Data Model elements can be added to any kind of project. In the Project Explorer, select Add new Item and follow these steps:

1. Find the ADO.NET Entity Data Model template items and click it.
2. Change the Name to AdventureWorks (see Figure 2). (This starts the Entity Data model Wizard.)
3. In the Entity Data Model Wizard, on the Choose Your Data Connection page, select the AdventureWorks database. (You can create the connection here too; see Figure 3.)
4. In the Choose Your Database Objects step of the wizard (see Figure 4), de-select Tables, Views, and Stored Procedures. Expand Tables and individually select Employee, EmployeeDepartmentHistory, Shift, and Contact. (The sample later will use this code to select employee shift histories.)
5. Click Finish.

Figure 2: You can add an ADO.NET Entity Data model element to any kind of project.

Figure 3: The Entity Data Model Wizard is going to generate strongly typed entity classes for you after you select the database and complete the wizard.

Figure 4: Select just a few tables. You can create additional data models (or facets of the database) for different kinds of problems.

After you click Finish, your friend the CodeDOM will be employed to generate strongly typed entity classes, an Entity Data Model XML file, which is expressed visually in a data model designer (see Figure 5). These entity classes inherit from System.Data.Objects.DataClasses.EntityObject. The AdventureWorksEntities inherits from System.Data.Objects.ObjectContext.

Figure 5: The visual designer that represents entities that you can query with LINQ to Entities (or alternatively eSQL; I recommend you use LINQ to Entities).

The ObjectContext is similar in function to the DataContext from LINQ to SQL, and the EntityObject is similar in usage to the entity classes in LINQ to SQL tagged with the TableAttribute. ObjectContexts represents your database connection and EntityObjects represent your table entities.

There are two things to think about before you proceed. The first is that generated classes are partial classes, so add any custom code to a separate file containing the other part of the partial class. The second thing to keep in mind is that one EDMX file should not contain your entire database. Only capture fairly singular facets in a single EDMX file—for example, in the example you can explore employee shift histories—and create additional sub-models for additional facets. Don’t worry about overlapping generated code. It doesn’t matter.

The important thing to remember here is that you didn’t write any of this code yet. The cost is almost zero (and disk storage is cheap). Now, you are ready to start querying with LINQ.

Ready to Query

To this point, you haven’t had to write any code. The preceding steps and text take longer to read (and much longer to write) then the whole process takes. You can create a project and add an ADO.NET Entity Model in seconds. And, these models support reading and writing.

As mentioned, you can use Entity SQL to interact with Entity Models but Entity SQL (or eSQL) may have been a clever invention that preceded LINQ to Entities slightly or was a case of someone’s unnecessary cleverness. If you want to weight how you spend your time, I would suggest you focus your efforts on LINQ.

Because LINQ to Entities is similar in some ways to LINQ to SQL, you need to perform some similar kinds of steps. First, create the entity classes defined by the wizard. This will provide you access to the underlying database. Second, write your LINQ queries, interact with or update the data, and save the changes. At least in, demo scenarios, it’s that easy (see Listing 1).

Listing 1: LINQ to Entities is as easy as LINQ to Objects because you are programming against objects, entity objects.

Imports LinqToEntities_VB.AdventureWorksModel
Imports System.Linq
Imports System.Text.RegularExpressions


Module Module1

   Sub Main()
      Dim entities As AdventureWorksEntities =
         New AdventureWorksEntities

      ' Select
      Dim employees = From emp In entities.Employee _
                      Select New With { _
                         .EmployeeID = emp.EmployeeID, _
                         .FirstName  = emp.Contact.FirstName, _
                         .LastName   = emp.Contact.LastName, _
                         .Salutation = emp.Contact.Title _
                      }

      For Each employee In employees
         Console.WriteLine("{0} {1} {2}", employee.Salutation, _
            employee.FirstName, employee.LastName)
      Next

      ' Update
      Dim mispelled = (From emp In entities.Employee _
                       Where emp.EmployeeID = 1 _
                       Select emp.Contact).First()

      mispelled.LastName = "Gelbert"
      entities.SaveChanges()

   End Sub

End Module

This example is easy. However, it is deceptively easy because it depends on a rock solid framework that includes generics, extension methods, Lambda Expressions, and code generators. It’s easy because at this level it isn’t really even apparent that you are programming against database entities. LINQ to Entities is taking care of all of that.

In the example, create the ObjectContext you defined in the wizard. This was done in the wizard step shown in Figure 3. The ObjectContext represents your database connection and all of that ADO.NET plumbing you don’t have to write now. Then, write LINQ queries, use the data, change it, or whatever.

Listing 1 selects the employees and projects a new type that contains the EMployeeID and name with salutation. No SqlConnection, no SqlDataAdapter, no SqlCommand, just LINQ. The employees are displayed in the console.

The next LINQ query selects one employee by EmployeeID, modifies the LastName, and saves the changes. Underneath, there is code connecting to the database, generating SQL commands, and executing these command, but you don’t have to write this code.

Sometimes, it’s hard to let go of control. To interact with the mouse, you used to have to write interrupt 0x33 handlers, 0x09 and 0x16 for the keyboard, 0x24 for hard error handlers (can you say exceptions) and 0x10 for GUI IO. You and I let go of that code a long time ago. The truth is that it shouldn’t matter where data comes from. All we must have to spend our time on is solving the problems of the business domain—and reading and writing data, no matter where it lives. For us to get hyperproductive and write the software of the future, the core technology and routine plumbing code has to become a no brainer. LINQ to Entities looks to be a step in that direction. Huzzah!

Summary

A reasonable question is how well will LINQ to Entities scale? The answer is I don’t know yet. I think that, because of the time it’s taking to get LINQ to Entities done and the investment Microsoft is making, they are shooting for the enterprise, but I haven’t built an enterprise application leveraging LINQ to Entities yet. Remember, it’s still in alpha. There are two things I am pretty sure of: the first is that Microsoft is aiming LINQ to Entities at small to enterprise applications, and the second is that you can mix LINQ to Entities with traditional ADO.NET programming. And, of course, applications really need entities, controller, boundary, and other classes, so LINQ to Entities may mitigate the need for you to write a Data Access layer but it won’t build those other kinds of classes for you.

In closing, can we all agree that line continuations (_) in VB are dated? Please kill these. They are tedious to type and provide no value.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#; pre-order your copy today at Amazon.com. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.