32

Using SQL Views with Entity Framework Code First

Posted by:Morgan Kamoga On:January 11, 2017

I have a complex linq query with lots of joins, group by, some data aggregations, data formatting and or data projection that takes long to execute in code. How do i convert this query into a sql view and execute it on the database side using Entity framework Code first?

Solution
While there is a straight forward way of creating tables from your Entity Framework (EF) entity models by simply writing .NET code; the reverse is false with SQL Views using code first approach. EF has built-in support for using existing views using the database first approach. 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. But with code first; its a different approach all together. In this article we are going to discuss how to use Entity Framework code first approach to generate and query SQL Views. Check here on how to install entity framework and here to enable migrations. After successful installation of EF and Migrations, your solution structure should look as the one below.

structure

For this article, let’s assume we have the following entities and context:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public class Movie
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public DateTime ReleaseDate { get; set; }
 
        public virtual Actor Actor { get; set; }
    }
 
public class Actor
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DateOfBirth { get; set; }
 
        public virtual ICollection Movies { get; set; }
    }
 
//DB context
 
  public class VideoLibraryContext : DbContext
    {
        public virtual DbSet Movies { get; set; }
        public virtual DbSet Actors { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly());
            base.OnModelCreating(modelBuilder);
        }
    }

In the code above, we have two entities i.e. Actor storing all movie actors and Movie for storing movie details. We also have the Db context as “VideoLibraryContext” that we use to register the DBSets so EF can generate tables for us in the Database.

Next we want to add a sql View called “V_MovieActors”. We are going to follow the steps below to achieve our goal.

  1. Create a POCO class for the view called “VMovie”
  2. Add the DbSet property in the DbContext class
  3. Add a new sql file called “MovieActorsView.sql” to the Migration folder
  4. Seed the MovieActorsView script in the configuration file.
  5. In the Project Manager console window add a new migration file called “AddMovieView”.
  6. Test our new sql view

Lets try to explain or demonstrate all these steps in details.

Step 1:
Create a POCO class for the view called “VMovie”. Using data annotations or fluent API, map the VMovie model to the actual sql view name in the db called V_MovieActors

1
2
3
4
5
6
7
8
9
10
    [Table("V_MovieActors")]
    public class VMovie
    {
        [Key]
        public Guid Id { get; set; }
        public string Title { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Actor { get; set; }
        public DateTime DateOfBirth { get; set; }
    }

Step 2:
Add the DbSet property in the DbContext class. Our new class should look like the one below:

1
2
3
4
5
6
7
8
9
10
11
12
  public class VideoLibraryContext : DbContext
    {
        public virtual DbSet Movies { get; set; }
        public virtual DbSet Actors { get; set; }
        public virtual DbSet VMovies { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly());
            base.OnModelCreating(modelBuilder);
        }
    }

Step 3:
Add a new sql file called “MovieActorsView.sql” to the Migration folder. Replace the contents of this file with the following.

1
2
3
4
5
6
7
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MovieActors]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[V_MovieActors]
AS
SELECT       NEWID() AS Id, dbo.Movie.Title, dbo.Movie.ReleaseDate, dbo.Actor.FirstName + '' '' + dbo.Actor.LastName AS Actor, dbo.Actor.DateOfBirth
FROM            dbo.Actor INNER JOIN
                         dbo.Movie ON dbo.Actor.Id = dbo.Movie.Actor_Id
'

This sql script checks and creates a new view called “V_MovieActors” if one does not exist already. Note that the fields of this view are exactly the same as the properties of VMovie model defined above. If these properties are different, EF will fail to map the Sql view to the object model. However if for some reason you want these fields to be different, then you can use the Column data annotation to specify which column in sql view does the property map to.

[Column(“differnt name as defined in the script”)]
public DateTime DateOfBirth { get; set; }

Step 4:
Seed the MovieActorsView script in the configuration file. You can run the sql script manually in SQL Management studio and skip this step or follow this step for an automated approach. Locate the configuration file found in the Migration folder. In the seed method write code to execute the sql script for creating a view in the database. The new configuration file should look like the one below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    using System.IO;
    using System.Reflection;
    using System;
    using System.Data.Entity.Migrations;
    public sealed class Configuration : DbMigrationsConfiguration
    {
        public Configuration()
        {
            //ContextKey = "VideoLibraryContext";
        }
 
        protected override void Seed(VideoLibraryContext context)
        {
            string codeBase = Assembly.GetExecutingAssembly().CodeBase;
            UriBuilder uri = new UriBuilder(codeBase);
            string path = Uri.UnescapeDataString(uri.Path);
            var baseDir = Path.GetDirectoryName(path) + "\\Migrations\\MovieActorsView.sql";
 
            context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir));
        }
    }

In this piece of code, we get the path to the MovieActorsView.sql file that we created in the Migrations folder using the executing assembly (line 15 to 18). With this approach, we don’t have to worry about where the script file is because .net will always give us the executing path. Check here and here for other ways to locate a file.

Note that for the file to be found in the executing assembly, you must go to the properties of MovieActorsView.sql file and set “Copt to Output Directory” to “Copy always”
view-script

Next we call the ExecuteSql Command passing the contents of MovieActorsView.sql file. This creates V_MovieActors view in the database.
Step 5:
From the Tools menu, click Library Package Manager and then Package Manager Console. At the PM> prompt enter the following commands:

Add-Migration AddMovieView

A new file called [version]_AddMovieView (where version number is generated by EF. In my case the file is named “201701041354381_AddMovieView.cs”) will be added to the Migrations folder with two method overrides. i.e. Up and Down. Remove contents of these methods and leave them empty so that duplicate objects are not created in the db as shown below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    using System.Data.Entity.Migrations;
 
    public partial class AddMovieView : DbMigration
    {
        public override void Up()
        {
 
        }
 
        public override void Down()
        {
 
        }
    }

The new solution file structure should look like the one below. migrations-file-structure

Finally go back to the Package Manager Console; at the PM> prompt enter the following commands:

Update-Database

Step 6:
By this step we have covered the major steps required for us to use sql views in EF. Now its time to write some linq queries to fetch data from our view. The procedure is the same as querying any other table using EF. Lets look at the example below.

1
2
3
4
5
6
7
8
    public List FetchMovieDetails()
        {
            using (var db = new VideoLibraryContext())
            {
                var movies = db.VMovies.ToList();
                return movies;
            }
        }

With this simple linq query, we can now get data stored in two tables with the help of SQL views.
Summary
In this example we have used only two simple tables but views are more useful in many other ways that include the following.

  • Query Simplicity: A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.
  • Security: Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data
  • Structural simplicity: Views can give a user a “personalized” view of the database structure, presenting the database as a set of virtual tables that make sense for that user
  • Transfer complex queries from your business logic to the database
  • if you have a complex select with lots of joins, you can implement it in a view and simply call the view without need to consider all these joins. You can then reuse this view. And the list is endless

Morgan Kamoga is a Software Devleoper at Laboremus Uganda.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Comments

    markus

    very nice :)

    Jean-Christophe

    Thanks Morgan for your solution, it was very useful in my situation.

    Wilson Kiggundu

    Very useful article. It is spot on.

Leave a Reply

Your email address will not be published. Required fields are marked *

Our Clients

We pride ourselves in long-standing client relationships and a culture of accountability and high ethical standards.

obos-bank-logo
bank2no
atlas_logo_nb
pareto-bank-logo2
hedaer
uia_logo_5
nitau_0
Orient_Bank
uganda-aids-commission
All rights reserved
Copyrights © Laboremus Uganda*