Introduction
This article describes how to use the Entity Framework for building applications through a tutorial. The Entity Framework enables developers
to create data access applications by programming against a conceptual application model instead of programming directly against a relational
storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.
The end product will be a group of unit tests that use the Entity Framework to query and update the database. This code can be used in your data-oriented
application for data access and updates. No ADO.NET code is used to communicate with the database. The objects that are returned are enumerable types that can
be traversed using LINQ.
Development Environment
The following applications are required for this tutorial. Is it assumed that you have some experience using Visual Studio either version 2010 or 2012
and that you have it installed on your computer.
The NuGet package manager is not required but it is highly recommended for installing external components like the Entity Framework.
Not only will it copy the libraries to your VS solution, it will automatically create the reference to these libraries in your project.
The NuGet package manager can be downloaded at the link below and it is part of this tutorial:
http://visualstudiogallery.msdn.microsoft.com/27077b70-9dad-4c64-adcf-c7cf6bc9970c/.
- .NET Framework 4.5
- Visual Studio 2012
- NuGet Package Manager extension for Visual Studio 2012
- SQL Server 2005 or higher
Setting up the Database
The first step that will need to be performed is installing the AdventureWorks SQL 2008 R2 OLTP database. Go to the Microsoft's CodePlex
page for AdventureWorks at http://msftdbprodsamples.codeplex.com/releases/view/59211
and download the MDF file. Copy the MDF file to a folder in which your SQL server account has the appropriate permission to read. Depending on your version of SQL Server,
your super user account may not have permissions to read files in your Download folder. You can also adjustment the ACL in the Download folder for your SQL Server super user account.
Then open SQL Server Management Studio and enter the following command:
CREATE DATABASE AdventureWorks ON (
FILENAME = 'C:\Users\Public\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG;
This command will create attach the MDF file to your SQL Server instance and create a log (LDF) file for the database.
Refresh your database to see the new AdventureWorks database.
For this tutorial we will create some views and a Stored Procedure. I want my data access code to be agnostic to changes in the database schema
so I prefer to link the Entity Framework model (that I will create later) to views instead of tables. However you can link your entities directly to tables.
The update method of the Product entity will be done via a stored procedure dbo.updateProduct so I included that as well.
CREATE VIEW [dbo].[Products]
AS
SELECT ProductID, Name, ProductNumber, ListPrice, StandardCost, ProductSubcategoryID, ProductModelID
FROM Production.Product
GO
CREATE VIEW [dbo].[ProductSubcategories]
AS
SELECT ProductSubcategoryID, Name
FROM Production.ProductSubcategory
GO
CREATE VIEW [dbo].[ProductModels]
AS
SELECT Name, ProductModelID
FROM Production.ProductModel
GO
CREATE VIEW [dbo].[ProductSearchItems]
AS
SELECT
Production.Product.ProductID AS ProductId
,Production.Product.Name AS ProductName
,Production.Product.ProductNumber AS ProductNumber
,Production.ProductCategory.Name AS ProductCategory
,Production.ProductModel.Name AS ProductModel
,Production.ProductSubcategory.Name AS ProductSubcategory
FROM Production.Product INNER JOIN
Production.ProductModel ON Production.Product.ProductModelID =
Production.ProductModel.ProductModelID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID =
Production.ProductCategory.ProductCategoryID
GO
CREATE VIEW [dbo].[TransactionHistory]
AS
SELECT TransactionID, ProductID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate
FROM Production.TransactionHistory
GO
CREATE PROCEDURE dbo.updateProduct
@productId int,
@productName nvarchar(50),
@productNumber nvarchar(25),
@listPrice money,
@standardCost money,
@productSubcategoryId int,
@productModelId int
AS
BEGIN
SET NOCOUNT ON;
UPDATE Production.Product
SET Name = @productName,
ProductNumber = @productNumber,
ListPrice = @listPrice,
StandardCost = @standardCost,
ProductSubcategoryID = @productSubcategoryId,
ProductModelID = @productModelId
WHERE ProductID = @productId
END
GO
Creating the Solution
The next step is create the Visual Studio solution. For this tutorial create a solution with a single Class Library project using .NET Framework 4.5.
For this exercise I named the solution "AdventureWorks" and the project "AdventureWorksService" (since I will have a AdventureWorksWeb project
in the future that will use this library).
Setting up the Entity Framework
The next step is to install the Entity Framework. After you have installed NuGet Package Manager extension for Visual Studio go to Tools -> Library
Package Manager -> Package Manager Console. The NuGet console will appear. In the console type "Install-Package
EntityFramework -Version 4.3.1" ad hit ENTER. This will copy the Entity Framework version 4.3.1 libraries to a "packages"
folder in your solution and automatically add the references to these libraries to your project so you can use them immediately.
Generating an Entity Data Model
In Solution Explorer, right-click on the Class Library project that you created and select Add -> New Item.
In the Add New Item box, select Visual C# Items -> Data and then select ADO.NET Entity Data Model.
Give the EMDX file a name. In my sample, I called it "AdventureWorksEntities.emdx".
The Entity Data Model Wizard gives you the option of creating a blank entity model or generating one from a database. For this tutorial we will generate one
from the AdventureWorks database. Select the "Generate from database" option and click Next. The next steps in the wizard will assist you in creating
an Entity Framework connection string in your app.config file. If this is the first time you are using this wizard you will not have a saved connection string
and you will need to click the "New Connection" to create a new connection string. Chose you instance of SQL Server that you want
to use and select the AdventureWorks database. When you are finished with the wizard it will confirm your connection information for your SQL Server
instance and Adventureworks database. The image for my SQL connection string is below but it will be different for you depending on your install of SQL Server.
Click the Next button to choose your database objects that you created in the previous step for the Entity model.
Open the Views and select ProductModels, Products, ProductSearchItems, ProductSubcategories, and TransactionHistory views.
In the Stored Procedures, select dbo.updateProduct.
All of the options should be checked.
Click the Finish button to make Entity Framework generate the entities for you. When it is finished you will have five entity types each corresponding
to a view. Now we will link the stored procedure to the entity model. Right-click on the Products entity and select "Stored Procedure Mapping".
Click on the "Select Update Function" and select "updateProduct" in the drop-down. Entity Framework will analyze the updateProduct
stored procedure and prompt you to map the fields on the Product entity to the input parameters of the stored procedure. Enter the fields as depicted below.
This will instruct Entity Framework to use the appropriate fields of the Product entity when invoking the Save method.
By default Entity Framework will make all non-foreign key fields Entity keys so we need to turn this off in the Product entity in order
for the Save function to work. Select the Product entity again and then select the Name field. In the Properties section, change the "Entity Key" value
to False. Do the same for ProductNumber, ListPrice, and StandardCost. This will allow us to update these fields in the Save function.
The last step for this exercise is to create tests that emulate the functionality of the web site. The tests will perform the following functions:
- Search for Products by Name or Number.
- Return all available Product Models.
- Return all available Product Subcategories.
- Return the details of Product by Product ID.
- Return all of the transaction history for a product by Product ID.
- Update a Product.
I create a separate Unit Test project and referenced the AdventureWorksService project. I also needed to reference System.Data and System.Data.Entity
DLLs for the Entity Framework functionality. The unit tests below will test all of the six scenarios above using the entities that you created in the previous step.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using AdventureWorksService.Entities;
namespace UnitTests
{
[TestClass]
public class EntityTests
{
[TestMethod]
public void SearchProducts()
{
string searchCriteria = "bike";
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
List<ProductSearchItem> products = (from s in _db.ProductSearchItems
where s.ProductName.Contains(searchCriteria) ||
s.ProductNumber.Contains(searchCriteria) select s).ToList();
}
}
[TestMethod]
public void GetProductModes()
{
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
List<ProductModel> productModels = (from m in _db.ProductModels select m).ToList();
}
}
[TestMethod]
public void GetProductSubcategories()
{
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
List<ProductSubcategory> productSubcategories =
(from s in _db.ProductSubcategories select s).ToList();
}
}
[TestMethod]
public void GetProductDetails()
{
int productId = 879;
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
Product product = _db.Products.First(p => p.ProductID == productId);
}
}
[TestMethod]
public void GetTransactionHistory()
{
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
int productId = 879;
List<TransactionHistory> transactionHistoryCollection = (from t in _db.TransactionHistories
where t.ProductID == productId
select t).ToList();
}
}
[TestMethod]
public void UpdateProduct()
{
int productId = 879;
using (AdventureWorksEntities _db = new AdventureWorksEntities())
{
Product product = _db.Products.First(p => p.ProductID == productId);
product.Name = "All-Purpose Bike Stand";
product.ProductNumber = "ST-1401";
product.StandardCost = 59.46m;
product.ListPrice = 159.00m;
product.ProductModelID = 122;
product.ProductSubcategoryID = 27;
_db.SaveChanges();
}
}
}
}
AdventureWorkEntities is the name of the Entity Framework database context class that was created when you created the entity data model. Wrap the instantiation
of this class in a using clause to ensure that the object is properly disposed. The context class allows you to query the database using LINQ syntax.
The ADO.NET boilerplate code to connect to the database, open a SQL Command, and load a SQLReader is handled internally by Entity Framework.
Because I created a separate project for unit testing my Entities I had to copy the Entity Framework configuration from my Service project
to an app.config of my Unit test project. This is necessary for Entity Framework to connect to the database from within your unit tests.
Of course if your unit tests are in the same project as your Entities then this is not necessary. If you use this code in a web project
you will need to copy the the configuration to your web.config.
<configuration>
<configSections>
<section name="entityFramework"
type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework,
Version=4.3.1.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
<parameters>
<parameter value="Data Source=.\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True" />
</parameters>
</defaultConnectionFactory>
</entityFramework>
<connectionStrings>
<add name="AdventureWorksEntities"
connectionString="metadata=res://*/Entities.AdventureWorksEnities.csdl|res://*/
Entities.AdventureWorksEnities.ssdl|res://*/Entities.AdventureWorksEnities.msl;
provider=System.Data.SqlClient;provider connection string="
data source=OWNER-PC\SQLEXPRESS;initial catalog=AdventureWorks;
integrated security=True;multipleactiveresultsets=True;App=EntityFramework""
providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
I hope that you enjoy this tutorial and you find it useful if you are learning to use the Entity Framework. You can download the entire source code
at the following location on CodePlex: http://adventureworksportal.codeplex.com.
It has source for both the service project with the Entity Framework and the consuming web project (which I will discuss in the my next tutorial).
Please feel free to reply with comments and questions.
