How to create a database view using Entity Framework Code first approach?

User case scenario:

There are several cases that the aplications may need to display the data by combiniting two or more tables, sometimes even more than 7-8 tables. In such scenario, using entity framework may results in slow performance beacause we need to process by selecting data from a table then running loop to for another tables.

However, database itself has features, stored procudures or creating views which can is most recommended and result in best performance. This blog will show to how to overcome the problem by creating view in entity framework.

Option 1

Create a view combining multiple tables in databse manually, subcequently add a entity for the view. Finally, we can add ignore for the entity OnmodelCreating enitity bulder.

Sample code:

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

Option 2

Alternatevely, you can create extension or property for handling view in database. In this option, we have to create view manually in databse then add 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");
    }
}

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

Hence, these are some quick way to implement database views in entity framwork code first approach.

Leave a Reply

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