How To Implement Database Views Using Entity Framework (EF) Code First Approach

Background

 

There are several situations where your applications may need to display data by combining two or more tables, sometimes even more than 7-8 tables. In such a scenario, using Entity framework may result in a slow performance because we need to process by selecting data from a table, then run some loops from other tables.

 

However, the database itself has many features to handle the performance in these cases, such as stored procedures or creating views that are most recommended and result in better performance.

 

On the other hand, entity framework, open source ORM framework, is gaining huge popularity among the .net developer because of numerous advantages and speedup the coding as well as quite handy to control database directly form code.

 

In this article, I will show how to how to take the advantages of database views in entity framework and overcome the problems of complex joining/query by creating a view and handling those views in Entity framework.

 

Database view

 

A view is considered as a virtual table which is formed based on SQL statement of other tables. This can be considered as a real table; however, we cannot do commands like delete or update. In simple terms, it contains query to pull data from table(s). We generally use WHERE, FUNCTION and/or JOIN to tables to form a view.

 

We create views for query simplicity: we can write complex queries to select data from various tables and instead of writing those complex queries each time; we create views to use it like simple tables. Other advantages are performance improvements, data security and ease of use.

 

We can create view two ways (MS SQL server): SQL Script and Query Designer.

 

SQL Script Syntax

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

We can write complex queries using where, function, join etc. or you can even do union.

 

Query Designer

 

We can take advantages of query designer as shown,

 

 

We can add tables, add relations (auto relations based primarykey-foreignkey), modify alias as depicted in above diagram. There is option to modify query manually and check the results.

 

Handling Views in Entity Framework

 

We can utilize views in entity framework database first approach easily considering a model. However, in entity framework code first approach, we need to do tricks. If we create models of views then it will create tables of those views in add-migration and database update command.

 

Tricks

 

We can handle views in entity framework in two ways.

 

Option 1

 

Create a view combining multiple tables in the database manually and subsequently add an entity for the view. Finally, we can add ignore for the entity OnmodelCreating Entity builder, as shown below.

 

Sample Code

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  if (IsMigration)
    modelBuilder.Ignore<ViewEntityName>();
 ...
}

With above trick, we can simply take advantages of entity model and ignore in migrations and database update in code first approach.

 

Option 2

 

Alternatively, you can create an extension or property for handling views in the database. In this option, we can create a view manually in the database then add an extension or property.

 

Sample Code

//Property
class DBContext
{
    public IQueryable<YourView> YourView
    {
        get
        {
            return this.Database.SqlQuery<YourView>("select * from dbo.ViewName");
        }
    }
}

Extension

static class DbContextExtensions
{
    public static IQueryable<ViewNameModel>(this DbContext context)
    {
        return context.Database.SqlQuery<ViewNameModel>("select * from dbo.ViewName");
    }
}

We can build database context extension to handle view and use it in our solution with code first approach.

 

There are some other alternative methods as well, however, I prefer these options, as they are easy to implement.

 

Conclusion

 

In this article, we have learned how to implement database views in entity framework in code first approach and take advantages of those views to handle complex queries and overcome the problem of complex joining/query in entity framework. Database views is quite effective for complex queries in terms of performance, ease of use, data security and most importantly query simplicity.