Creating a Data Access Layer Using the Entity Framework

As this book is focused on Silverlight as a technology, we won't be going into too much depth on database-related concepts such as database terminology and principles, creating and managing databases in SQL Server Management Studio, or methods of accessing data, such as ORMs, the Entity Framework, LINQ to SQL, ADO.NET, and so on. These are considered prerequisite knowledge for this book, so we'll focus on how to actually expose the data publicly via a domain service, and then focus on how to consume it in a Silverlight application in the next chapter.

Configuring the Database

Throughout this book you'll be creating a business application in Silverlight for a fictional bicycle sales chain called Adventure Works Cycles. The application will enable employees to manage the various aspects of the business, including product inventory, sales, and purchasing. For this, we'll be using SQL Server 2008 as our database server (the free Express version installed with Visual Studio will be sufficient), and the database that will be supporting this scenario will be the AdventureWorks OLTP sample SQL Server database from Microsoft, which you can download from http://msftdbprodsamples.codeplex.com. Choose the AdventureWorks 2008R2 SR1 release. We're using the AdventureWorks database because it is an example of a typical business database with a suitably complex structure, and is also well populated with sample data.

About the Entity Framework

In order to retrieve and persist data to the database, we'll be using the Entity Framework as our data access layer. The Entity Framework is an object-relational mapper (ORM) that enables you to create a conceptual object model, known as an entity model, and map it to the database schema—for example, tables, views, and stored procedures. You can then query this object model in code using LINQ to Entities. LINQ is specifically designed for querying an entity model.

The Entity Framework takes care of translating the LINQ to Entities queries to SQL queries in the database, and returns the data as the objects, known as entities, that you've defined in your model. Essentially, the Entity Framework makes working with database data in code a much more pleasant and robust experience, as it enables you to work with the data as objects and collections instead of as rows and tables, and to write strongly typed queries against the entity model.

The Entity Framework was subject to some controversy at its initial release, including a “vote of no confidence” petition signed by many prominent members in the developer community. However, it has undergone numerous improvements since then (with the .NET Framework version 4) to counter the issues that were raised with the first version, and it is working its way toward becoming a robust yet easy-to-use ORM.

One of the benefits of using the Entity Framework for this project is that RIA Services has been designed to work very smoothly in conjunction with it, enabling services with the standard CRUD (Create, Read, Update, Delete) operations to be created very easily for given entities.

However, the Entity Framework is by no means the only data access technology supported by RIA Services. It also has support for LINQ to SQL models, which can be found in the WCF RIA Services Toolkit (discussed in the “WCF RIA Services Toolkit” section, later in the chapter), and in fact it can work with any data source, although doing so involves a degree of additional work when creating a domain service. So if you have an existing data access layer to access your database, or you want to use a different ORM, such as nHibernate, RIA Services can also support these scenarios.

In fact, employing the built-in support for exposing entities directly from your Entity Framework model could in some ways be considered bad practice. While taking advantage of this support to expose given entities to the client makes creating and maintaining services incredibly easy, the entities are essentially a model of your data layer, and ideally these entities should not be exposed to the presentation layer. Whether you pass entities or plain-old CLR objects (referred to as POCOs, but also known as presentation model types in RIA Services) of your own design back and forth is a decision you will have to make, dependent on many factors. Using entities will make development much faster, but will also be less flexible than using custom presentation model types. RIA Services works just as well using presentation model types as it does with entities, despite more work being involved in initially creating the domain services. Therefore, the best practice would be to use presentation model types as the data transfer mechanism, which can be populated with data from your entity model. That said, we will take a look at both of these methods, beginning with directly exposing entities because they provide the easiest and fastest means to get started.

Workshop: Creating an Entity Model

After you've installed the database, you can create your entity model. This is a very straightforward process, which the Entity Data Model Wizard will guide you through.

1. Add a new item to your Web Application project, select the ADO.NET Entity Data Model item template (from under the Data category), and name it AdventureWorksModel.edmx.
2. In the first step of the wizard that appears, select the “Generate from database” option, and press the Next button.
3. Create a connection to the database, pointing it to the AdventureWorks database you previously installed. Ensure the “Save entity connection settings in Web.Config as:” check box is selected, and name the setting “AdventureWorks-Entities". Press the Next button.
4. The tables, views, and stored procedures from the database will be loaded into a tree. Select the “Tables” item to include all the tables in the database in your entity model. Ensure that the “Pluralize or singularize generated object names” and the “Include foreign key columns in the model” check boxes are selected. Also ensure that the model namespace is “AdventureWorksModel". Press the Finish button.

The entity model will now be generated, and displayed in the entity model designer.