Click here to Skip to main content
Click here to Skip to main content

Using the Entity Framework 4.3 in .NET Development

, 8 Sep 2012
Rate this:
Please Sign up or sign in to vote.
This article describes how to use the Entity Framework version 4.3 in your Windows desktop or web application for database access.

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 added to prevent extra result sets from
    -- interfering with SELECT statements.
    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:

  1. Search for Products by Name or Number.
  2. Return all available Product Models.
  3. Return all available Product Subcategories.
  4. Return the details of Product by Product ID.
  5. Return all of the transaction history for a product by Product ID.
  6. 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>
    <!-- For more information on Entity Framework configuration, 
              visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <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=&quot;
            data source=OWNER-PC\SQLEXPRESS;initial catalog=AdventureWorks;
            integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" 
       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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Peter Carrasco
Team Leader
United States United States
I am a senior software engineer and technical lead for a mid-sized technology firm. I have an extensive background in designing, developing, and supporting ASP.NET web-based solutions. I am currently extending my skill-set to the area of application identity management and access control.

Comments and Discussions

 
GeneralMy vote of 1 PinmemberTemplar of Steel6-Sep-12 16:30 
I hoped the use of EF in MVC, but I don't see the use of MVC in the real solution.
GeneralRe: My vote of 1 PinmemberPeter Carrasco8-Sep-12 14:30 
QuestionReference Entity Framework 5.0 when using vs2012 PinmemberRickieChina30-Aug-12 16:55 
QuestionUnit tests with database access? Pinmemberdan.sovrea28-Aug-12 20:57 
AnswerRe: Unit tests with database access? PinmemberPeter Carrasco29-Aug-12 15:59 
GeneralMy vote of 5 Pinmemberfredatcodeproject28-Aug-12 5:03 
GeneralMy vote of 4 PinmemberChristian Amado23-Aug-12 6:03 
Questionerror in [ProductSearchItems] PinmemberGedda22-Aug-12 22:46 
AnswerRe: error in [ProductSearchItems] PinmemberPeter Carrasco23-Aug-12 0:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 9 Sep 2012
Article Copyright 2012 by Peter Carrasco
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid