How can I use database views in a subclass of DbContext with Entity Framework Core 1.0 (EF7)

Question

Unfortunately, Entity Framework Core 1.0 (formerly Entity Framework 7) does not yet support Views, and I'm trying to "fake" it using a table.

However, the scaffolding dotnet dbcontext ef scaffold command does not currently recognize or generate views, and I need one DbContext that allows me to query the view and update the tables. Is there any way to do this?

This is the command I use to align the DbContext:

 dotnet ef dbcontext scaffold -c MyStoreContext -o Model "Data Source=(local);Initial Catalog=DBNAME;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer --force 

(This puts all my model classes in the Model directory and makes them overwrite.)

Note. The reason I really want to use the view is the GROUP BY logic, which is not supported in EF Core 1.0

Answer 1

For me, my opinion was in a different scheme, so I had to change the attribute in the model class:

 using System.ComponentModel.DataAnnotations.Schema; namespace API.DataAccess { [Table("MyViewName", Schema = "SomeSchema")] public class MyViewName { //props } } 

To complete the entity code:

 protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<MyViewName>(entity => { entity.HasKey(e => new { e.SomeColumn }).HasName("PK_FAKEKEY"); entity.Property(e => e.SomeColumn ).IsRequired(); entity.Property(e => e.SomeColumn2 ); entity.Property(e => e.SomeColumn3 ); }); } 

Answer 2

Here is what I came up with:

I create an incomplete class that inherits from DbContext , and then add any new logic to it, overriding the OnModelCreating method.

Ultimately, the EF-scaffolder should (I hope) be able to create views for me, so in the interim I call the MyStoreContext_WithViews class, so I can do a search and replace at some point to update it.

The database view in this example is called RepeatOrderSummaryView .

I had to add all columns manually to my view class (since scaffolder does not support it). This is good at the moment.

There is no key in the view, but EF requires a key, so I just create a fake using one of the columns.

 namespace MyStore.EF.Model { public partial class MyStoreContext_WithViews : MyStoreContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<RepeatOrderSummaryView>(entity => { entity.HasKey(e => new { e.FirstOrder_Date }) .HasName("PK_FAKEKEY"); }); } public virtual DbSet<RepeatOrderSummaryView> RepeatOrderSummaryView { get; set; } } [Table("RepeatOrderSummaryView")] public partial class RepeatOrderSummaryView { public DateTime OrderDate { get; set; } public bool HasShipped { get; set; } } } 

Then I was able to successfully execute this request using the view:

  RRStoreContext_WithViews ctx = new RRStoreContext_WithViews(); var data = ctx.RepeatOrderSummaryView.Where(x => x.HasShipped == true); 

Since this class simply inherits from my generated DbContext ( MyStoreContext ), I can, of course, use all the other tables that have been tinted.

It is not checked for updated views - of course, if you try this, you will need a real PK.

---

Since you are not returning "real" entities or necessarily elements with a specific key key, you should use .AsNoTracking() . I found fewer results in my result set than I expected, and that was because he was performing a key match and thought he already had an object in memory.

Be sure to carefully check that the number of results is as expected, or if you encounter big problems.

What difference does .AsNoTracking () make?