Views in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Database Views in Entity Framework with Examples

In this article, I am going to discuss How to use Database View in Entity Framework Database First Approach with Examples. Please read our previous article where we discussed How to use Stored Functions in Entity Framework Database First Approach. We are going to work with the same example that we created in our Introduction to Entity Framework Database First Approach article. Please read our introduction to Entity Framework Database First article before proceeding to this article.

What is a View in SQL Server Database?

The views in SQL Server are nothing more than a compiled SQL query. We can also consider the Views as virtual tables. As a virtual table, the Views do not store any data physically by default. But if you want then you can change this default behavior. So, when we query a view it actually gets the data from the underlying database tables as shown in the below image.

Simply we can say that the views in SQL Server act as an interface between the Table(s) and the user. Please Click Here to learn more about SQL Server Views.

Example to Understand Database Views in Entity Framework Database First Approach

Let us understand, how to query the database views using Entity Framework Database-First approach with an example. If you remember, in our EF_Demo_DB database, we have created the following Complex View to fetch the data from the Student, Course, and StudentCourse tables using Joins.

-- Student Course View
CREATE VIEW vwStudentCourse
AS
    SELECT	
       s.StudentId, 
       s.FirstName, 
       s.LastName,
       sc.CourseId, 
       c.CourseName
    FROM    Student s INNER JOIN StudentCourse sc
    ON		s.StudentId = sc.StudentId 
    INNER JOIN Course c ON sc.CourseId = c.CourseId
GO

Now, we want to execute and read data from the above vwStudentCourse view using Entity Framework Database First Approach. To use the above View using Entity Framework, first of all, we have to add database views to the Entity Data Model. And if you remember we have already included the above view while creating our Entity Data Model as shown in the below image.

Like the database table, for Views, the Entity Data Model also going to generate an entity. So, you can check the vwStudentCourse Entity in the EDM as shown in the below image.

You can check the Entity in the EDMX class as well. A class file with the name vwStudentCourse.cs will be created with the following auto-generated code. As you can see the following Entity is created based on the number, name, and type of columns that we defined in our views.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace DBFirstApproach
{
    using System;
    using System.Collections.Generic;
    
    public partial class vwStudentCourse
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int CourseId { get; set; }
        public string CourseName { get; set; }
    }
} 

The point that you need to remember is that you can work with the above Entity which is created based on the View in the same way you work Entities that are created based on the Tables, except that you cannot perform INSERT, UPDATE, and DELETE Operation. That means you can only perform the SELECT Operation.

Accessing Data using Views in Entity Framework Database First Approach:

Now, modify the Main Method of the Program class as follows. Here, we are accessing the data using the vwStudentCourse Entity.

using System;
using System.Linq;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                context.Database.Log = Console.Write;
                var studentAndCourseList = context.vwStudentCourses.ToList();

                foreach (var item in studentAndCourseList)
                {
                    Console.WriteLine($"Student Name: {item.FirstName} {item.LastName}. Course: {item.CourseName}");
                }
            }
                
            Console.Read();
        }
    }
}

Output:

Advantages of using Views

We are getting the following advantages of using Views.

1. Hiding the Complexity
2. Implementing Row and Column Level Security.
3. Presenting the aggregated data by hiding the detailed data.

In the next article, I am going to discuss How to use Transaction in Entity Framework Database First Approach with Examples. Here, in this article, I try to explain How to use Views in Entity Framework Database First Approach with Examples. I hope you enjoy this How to use Views in Entity Framework Database First Approach article.