RAW Development Power: An Entity Framework Sample
In this example, I have a super-simple database for tracking contacts. The database has two tables: Contacts and PhoneNumbers (see Figure 2). A Contact can have multiple phone numbers in the PhoneNumbers database. The two tables are related via the Contacts.ID and the PhoneNumbers.ContactID fields.

Figure 2. The Contacts Database Schema

We are going to build an EF model that allows us to work with the data for basic CUD-related activities. One caveat here is that this sample only works against SQL Server 2005 for now. Support for SQL Server 2008 will be provided in a service pack for Visual Studio 2008. Look for it close to SQL Server 2008's launch date.

The code samples that follow work against the database schema shown in Figure 2. This database is included in the article's source code but you can create it just as easily.

I recommend downloading the source code to follow along. In order for the code to work on your system you will need to install the following components:

1. Microsoft Visual Studio 2008 Express Editions
2. Microsoft SQL Server 2008 Express CTP
3. ADO.NET Entity Framework Beta 3
4. Entity Framework Tools December 2007 CTP

Figure 3. Choosing the Tables for the Data Model

In order to build the sample EF, open Visual Studio 2008 and create a new VB.NET-based Windows Form project. Once Visual Studio 2008 creates the project, perform the following steps:

1. Open the Add New item dialog box (Project > Add New Item) and select the ADO.NET Entity Data Model. Name it ContactModel.edmx. Click Add.
2. In the Entity Data Model Wizard dialog box, select Generate from database and click Next.
3. In the Choose Your Data Connection section, setup a connection to the sample Contacts database (see the Requirements call out). In the dialog box's Save entity connection settings section, enter TestEFEntities as the connection name. Click Next.
4. In the Choose Your Database Objects dialog, select only the Contacts and PhoneNumbers tables (see Figure 3). Leave the Model Namespace as TestEFModel. Click Finish to create the model.

Visual Studio 2008 creates a data model that contains entity objects for the Contacts and PhoneNumber tables (see Figure 4). The model resembles the physical data model shown earlier but there is a key difference.

Figure 4. The Contacts and PhoneNumbers Entities

Notice the Navigation Properties at the bottom of each box? These properties contain references to the other objects required to make a complete entity. For example, a Contact is not complete unless its phone numbers are included (and vice versa). Best of all, all the required SQL code for reading and writing data to the database is included as method calls of the entity classes (see Figure 5).

Using the EF you can work with the underlying entities by executing strongly-typed method calls. Working with these calls requires some knowledge of the .NET Language Integrated Query toolset, but as you will see below, if you know SQL syntax, you can pick up LINQ rather quickly. Let's take a look at some of the code required to work with the Contacts and PhoneNumber entities.

The following code listings show how easy it is to start working the EF model. As you can see by these examples, you can immediately start writing code that handles your application's business rules.

Selecting Data
This method selects all the records from the contacts table and then fills a data grid control with the retrieved data. Four lines of code are all it takes.

 Private Sub LoadContacts()
        Dim data As New TestEFEntities

        Dim contacts = From c In data.Contacts _
                              Select c.FirstName, c.LastName, c.Email
        DataGridView1.DataSource = contacts
        DataGridView1.Refresh()

    End Sub 

Figure 5. The Methods of the TestEFEntities Object

Add and Updating Data
Adding data is as simple as selecting data. Here, I create a new Contacts object, set its properties, add the new object to entities collection object and save it. The changes are immediately reflected in the database.

Private Sub AddContact()
        Dim data As New TestEFEntities
        Dim newContact As New Contacts
        With newContact
            .FirstName = "Ty"
            .LastName = "anderson"
            .Email = "ty@test.com"

        End With

        data.AddToContacts(newContact)
        data.SaveChanges()
    End Sub 

Updating a record works in a similar fashion. The only difference in the following procedure is that I first need to retrieve the record I want to update.

Private Sub UpdateContact()
        Dim data As New TestEFEntities

        Dim myContact As New Contacts
        myContact = (From c In data.Contacts Where _
         c.LastName = "Jagger" Select c).First
        myContact.Email = "changedemail@test.com"
        data.SaveChanges()

    End Sub 

Working with Related Data
An aspect I particularly like about EF is how it simplifies working with related data. The following example adds a new phone number record for a contact.

Private Sub AddPhoneNumber()
        Dim data As New TestEFEntities
        Dim newNumber As New PhoneNumbers
        Dim myContact As New Contacts
        myContact = (From c In data.Contacts _ 
         Where c.ID = 1 Select c).First
        
        With newNumber
            .PhoneNumberType = txtType.Text
            .PhoneNumber = txtNumber.Text
            .Contacts = myContact
        End With
        data.AddToPhoneNumbers(newNumber)
        data.SaveChanges()
    End Sub 

To relate one entity to another, all you need to do is add the related entity to the appropriate property of the entity you are creating. In this case, we select a contact and then set it as the Contacts property for the new phone number. The EF model handles the rest.

This sample took about 15 minutes to create. Granted, it did take a few hours to get my mind around EF and LINQ, but the benefits are obvious having invested the time.

SQL Express Works with PHP (And Other Non-Microsoft Technologies)
SQL Express is a Microsoft product and runs only in Windows. This requirement does not prevent SQL Express for working with solutions built with other platforms like PHP (although PHP can run on a Windows server).

The truth is the success of Microsoft technology in the enterprise sector increases the likelihood of working with SQL Express databases. PHP supports SQL Server as a database option for your solutions but it does require some extra configuration to setup the connection. See the PHP Web site for detailed setup instructions.

If you are using other database products for your PHP -based solutions (or any non-Microsoft technologies) the option to port to SQL Express (or any other SQL Server edition) is that your solution can quickly be deployed in your customer's Microsoft environment without requiring significant changes to their infrastructure. Also, your developers can continue to develop your product using the development tools of your choice.

Conclusion
I started this article explaining some of the new features in SQL Server Express 2008 that I believe should interest you as a developer of data applications. SQL Server 2008 Express provides many of the features of SQL Server 2008, but without the overhead involved with an enterprise relational database management system. As a result, SQL Server 2008 Express makes a great foundation for your applications. If your needs exceed SQL Server 2008 Express's capabilities, then you have the ability to scale-up to higher-end versions of SQL Server with little effort. The combination the SQL Server Express 2008 feature set, the ADO.NET Entity Framework, and integration with Visual Studio and .NET provides a level of developer productivity that will be difficult for Microsoft's competitors to overcome.

* This article was commissioned by and prepared for Microsoft Corporation. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Ty Anderson runs Cogent Company, a consultancy in Dallas specializing in leveraging technology to enable business strategy. Ty is a regular contributor to the Microsoft Developer Network (MSDN) and has recently written a book, Office Programming 2003: Real World Applications, focused entirely on building applications with the Microsoft Office System 2003.