Creating views with Entity framework core

A Technical tutorial on how to create views in code first approach with EF core

Image By Nitesh Singhal

Database Views

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

we can think of view as a virtual table which return only required information from one ore more tables by combining results based on predefined query.

Entity Framework Core

Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology.

EF Core can serve as an object-relational mapper (O/RM), which:

• Enables .NET developers to work with a database using .NET objects.
• Eliminates the need for most of the data-access code that typically needs to be written.

EF Core supports many database engines.

Sample Example

Data Model

Let’s define data model which we will use for this tutorial.

ExpenseItem

public class ExpenseItem
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string Category { get; set; }
   public List<ExpenseHistory> History { get; set; } = new   List<ExpenseHistory>();
}

ExpenseHistory

public class ExpenseHistory
{
   public int Id { get; set; }
   public int ExpenseItemId { get; set; }
   public ExpenseItem ExpenseItem { get; set; }
   public DateTime Date { get; set; }
   public double Amount { get; set; }
}

and We have controller method which returns the expense and its total.

so we have a following code to get the data.

[HttpGet("GetExpenseByTotal")]
public IActionResult GetExpenseByTotal()
{
    var items = databaseContext
                   .Items
                   .Select(x => new
                    {
                       id = x.Id,
                       name = x.Name,
                       category = x.Category,
                       total = x.History.Sum(r => r.Amount)
                    })
                   .OrderByDescending(x => x.total);
   return Ok(items);
}

And now we want to have a database view so that we simplify the Linq query.

Adding View

Let’s start by adding a migration using package manager console in Visual Studio

Add-Migration ExpenseByTotalView

and we get following empty migration class.

public partial class ExpenseByTotalView : Migration
{
  protected override void Up(MigrationBuilder migrationBuilder)
  {
  }
  protected override void Down(MigrationBuilder migrationBuilder)
  {
  }
}

So then how do we add view generation here. We need to write little script to create view.

CREATE OR REPLACE VIEW public.ExpenseByTotal AS SELECT p."Id", p."Name", p."Category", sum(h."Amount") AS TotalAmount FROM "Items" p JOIN "ItemsHistory" h ON p."Id" = h."ExpenseItemId" GROUP BY p."Id", p."Name" ORDER BY (sum(h."Amount")) DESC;

Note: I am using Postgres database. you can write the script for your database manually by creating the view in your database editor

Now we need to add this script in the in the code

public partial class ExpenseByTotalView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE OR REPLACE VIEW   public.ExpenseByTotal " + "AS SELECT p.\"Id\", p.\"Name\", p.\"Category\", sum(h.\"Amount\") " + "AS TotalAmount FROM \"Items\" p JOIN \"ItemsHistory\" h " + "ON p.\"Id\" = h.\"ExpenseItemId\" " + "GROUP BY p.\"Id\", p.\"Name\" " + "ORDER BY (TotalAmount) DESC;");
    }
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP VIEW public.ExpenseByTotal;");
    }
}

and update the database

update-database

Define the Model

public class ExpenseByTotal
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string Category { get; set; }
   [Column("totalamount")]
   public double TotalAmount { get; set; }
}

Modify DbContext

we need to add the DbSet in the DbContext.

public DbSet<ExpenseByTotal> ExpenseTotals { get; set; }

and add the following code to OnModelBuilder method

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   base.OnModelCreating(modelBuilder);
   modelBuilder
      .Entity<ExpenseByTotal>()
      .ToView("expensebytotal")
      .HasKey(t => t.Id);
}

Modify Controller

public IActionResult GetExpenseByTotal()
{
      var items = databaseContext.ExpenseTotals.ToList();
      return Ok(items);
}

Let’s verify the result

Results on Swagger UI

We can see the expense with their total.

Additional Tips

Before we end, few things that you should keep in mind.

• Don’t modify the view creation script once it is applied. always add a new migration and add your modified script in the migration step.
• This is read only view so any update will result in failure. we can create updatable view also but we need to define it that way.

Summary

In this tutorial, we have learned how we can create views with Entity Framework core by adding view creation script in the migration step and then defining model same way as we are doing for other tables.

Hope it is helpful..

Happy coding and Keep learning..!