Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Use SQL Server Views with the Entity Framework


By:   |   Last Updated: 2010-04-22   |   Comments (6)   |   Related Tips: More > Entity Framework

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:

The Entity Framework (EF) is an Object Relational Mapping (ORM) tool that allows developers to work with the database by simply writing .NET code

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:

My starting point is a Visual Studio 2008 SP1 Console application

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

Add an ADO.NET Entity Data Model to the project and name it MSSQLTipsModel.edmx

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

select an existing database connection or click New Connection to create one

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:

then select the views to be included in the model from the Choose Your Database Objects dialog

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.


Last Updated: 2010-04-22


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, June 15, 2014 - 1:04:37 AM - dave Back To Top

WHat about the total lack of support from Code First. Each approach in EF is half baked and all 3 cannot be used togehter, and none ae complete.


Saturday, February 22, 2014 - 3:54:23 PM - Rajesh Mishra Back To Top

Hi,

This fine but i would like to use this in MVC project code first approach. I went through above mentioned msdn link as well but i dont think that will help me creating edm files.


Saturday, September 07, 2013 - 7:49:51 PM - Ray Barley Back To Top

Take a look at this: http://msdn.microsoft.com/en-us/library/cc982038(v=vs.100).aspx

It's pretty close to what you want


Saturday, September 07, 2013 - 7:26:12 AM - Prakash Joshi Back To Top

I have to create a sql server view using entity framework 4.1 in MVC3 project. In my project I am able to create database and tables automatically using Entity Framework but don't know how to create Views automatically. Is there any solution for this.


Wednesday, February 01, 2012 - 10:40:22 AM - Ray Barley Back To Top

I think the DBA should own everything!

Seriously though the DBA should own the SSDL and the MSL.  The beauty of EF is that really all a developer should care about is the CSDL and most would probably be happy with that I think.  

I work both as a developer and a DBA and when I'm building an app it would be great if I had the CSDL and everything else is just a black box.

 


Wednesday, February 01, 2012 - 10:27:42 AM - Colby Back To Top

I am also a DBA and our group is also using Entity Framework.   This article is very interesting and learned a lot. There are a lot of "how to" using SQL and the entity framwork that you could write about.  The question is in the entity framework model who owns the SSDL, the MSL and CSDL.  I think the DBA owns the SSDL.  Thoughts?


Learn more about SQL Server tools