How to Use SQL Server Views with the Entity Framework

By: Ray Barley | Read Comments (2) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem

I'm a DBA and the developers in my organization want to use the Entity Framework (EF) in their applications.  While I understand how EF can dynamically generate the SQL for queries, we have many existing views that I would like to be able to continue to use.  How can I do this with the Entity Framework?

Solution

The Entity Framework (EF) is an Object Relational Mapping (ORM) tool that allows developers to work with the database by simply writing .NET code.  For an introduction to EF take a look at our earlier tip Intro to Entity Framework with SQL Server.  EF has built-in support for using existing views.  Essentially it will treat the results returned from a view as an entity, similar to a table.  When you use Visual Studio's Entity Data Model Wizard to generate a model from an existing database, an entity is created for each table that you select.  When you select a view an entity will be created for it as well.

To demonstrate let's start out with a very simple hypothetical schema that represents a database for the the MSSQLTips web site:

As is typical we have a normalized schema with foreign key relationships between the Tip table and the TipCategory and Author tables.  In order to simplify querying we make use of the following view to return the data that we need for a particular web page:

CREATE VIEW [dbo].[vTipList]
AS
SELECT 
  t.TipID
, a.FirstName + ' ' + a.LastName Author
, t.Title
, c.TipCategoryName
, t.PublishDate
, t.URL
FROM  dbo.Author a
INNER JOIN dbo.Tip t ON t.AuthorID = a.AuthorID 
INNER JOIN dbo.TipCategory c ON c.TipCategoryID = t.TipCategoryID
WHERE (t.PublishDate IS NOT NULL)

Creating the EF Model

We are now ready to create our EF model that will make our view available.  My starting point is a Visual Studio 2008 SP1 Console application.  Add an ADO.NET Entity Data Model to the project and name it MSSQLTipsModel.edmx as shown below:

Click Add then choose Generate from database in the Choose Model Contents dialog as shown below:

Click Next then select an existing database connection or click New Connection to create one as shown below:

Note that the Save entity connection settings in the App.Config checkbox is checked which puts the necessary connection information into the application's configuration file.  Typically you would want to do this.  Click Next then select the views to be included in the model from the Choose Your Database Objects dialog as shown below:

Click Finish to complete the Entity Data Model Wizard.  You will see the following message displayed:

The table/view 'EFTipsSample.dbo.vTipList' does not
have a primary key defined. The key has been inferred and the
definition was created as a read-only table/view.

When you create a model, EF will automatically generate the classes required to query and manipulate the entities.  However, this is not the case for an entity that is based on a view.  You'll get what you need to query the entity but you can't perform inserts, updates or deletes automatically.  You can create your own stored procedures to do this.  You can refer to our earlier tip How To Use SQL Server DML Stored Procedures with the Entity Framework for the details. 

Sample Code

Now we are ready to write some sample code to query based on the view in our EF model.  You can take a look at the code generated by EF; you will find it in the MSSQLTipsModel.Designer.cs file.  There are a couple of noteworthy things to point out:

• The EFTipsSampleEntities class is the container for your entities; this class name was automatically generated on the Choose Your Data Connection dialog
• The vTipList class represents the entity created from the view of the same name; it has a property for each column in the view

The following is the simplest bit of code that will perform a query using the view and display each row and its columns:

  using (EFTipsSampleEntities ctx = new EFTipsSampleEntities())
  {
    var result = from t in ctx.vTipList
                 select t;
    foreach (vTipList t in result)
    {
      Console.WriteLine("{0} {1} {2} {3} {4}",
          t.TipID, t.Author, t.TipCategoryName,
          t.Title, t.URL);
    }
  }

The above code snippet uses LINQ to Entities in order to perform the query (shown in bold).  The output looks like this:

Next Steps

• The Entity Framework can use existing views in your database.  Views can be used to simplify queries and provide the data required without having to deal with the necessary joins to accomplish your goal.
• You can download a backup of the SQL Server 2008 sample database and the Visual Studio 2008 sample project here and use it as a basis for your own testing and experimentation. 
• In a future tip we will look at how to handle complex queries in EF without creating a view.

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code