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

User case scenario:

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

However, the database, itself has many features to handle the performance for these cases, like stored procedures or creating views which are most recommended and result in better 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 the database manually, subsequently add a entity for the view. Finally, we can add ignore for the entity OnmodelCreating entity builder.

Sample code:

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

Option 2

Alternatively, you can create extension or property for handling view in database. In this option, we have to create view manually in database 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 framework code first approach.

Leave a Reply

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