Working with SQL views in Entity Framework Core

Views are not currently supported by Entity Framework Core. See https://github.com/aspnet/EntityFramework/issues/827.

That said, you can trick EF into using a view by mapping your entity to the view as if it were a table. This approach comes with limitations. e.g. you can't use migrations, you need to manually specific a key for EF to use, and some queries may not work correctly. To get around this last part, you can write SQL queries by hand

context.Images.FromSql("SELECT * FROM dbo.ImageView")

EF Core supports the views, here is the details.

This feature was added in EF Core 2.1 under the name of query types. In EF Core 3.0 the concept was renamed to keyless entity types. The [Keyless] Data Annotation became available in EFCore 5.0.

It is working not that much different than normal entities; but has some special points. According documentation:

• Cannot have a key defined.
• Are never tracked for changes in the DbContext and therefore are never inserted, updated or deleted on the database.
• Are never discovered by convention.
• Only support a subset of navigation mapping capabilities, specifically:
• They may never act as the principal end of a relationship.
• They may not have navigations to owned entities
• They can only contain reference navigation properties pointing to regular entities.
• Entities cannot contain navigation properties to keyless entity types.
• Need to be configured with a [Keyless] data annotation or a .HasNoKey() method call.
• May be mapped to a defining query. A defining query is a query declared in the model that acts as a data source for a keyless entity type

It is working like below:

public class Blog
{
   public int BlogId { get; set; }
   public string Name { get; set; }
   public string Url { get; set; }
   public ICollection<Post> Posts { get; set; }
}

public class Post
{
   public int PostId { get; set; }
   public string Title { get; set; }
   public string Content { get; set; }
   public int BlogId { get; set; }
}

If you don't have an existing View at database you should create like below:

db.Database.ExecuteSqlRaw(
@"CREATE VIEW View_BlogPostCounts AS 
    SELECT b.Name, Count(p.PostId) as PostCount 
    FROM Blogs b
    JOIN Posts p on p.BlogId = b.BlogId
    GROUP BY b.Name");

And then you should have a class to hold the result from the database view:

  public class BlogPostsCount
  {
     public string BlogName { get; set; }
     public int PostCount { get; set; }
  }

And then configure the keyless entity type in OnModelCreating using the HasNoKey:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder
          .Entity<BlogPostsCount>(eb =>
          {
             eb.HasNoKey();
             eb.ToView("View_BlogPostCounts");
             eb.Property(v => v.BlogName).HasColumnName("Name");
          });
    }

Lastly, configure the DbContext to include the DbSet:

public DbSet<BlogPostsCount> BlogPostCounts { get; set; }

The EF Core doesn't create DBset for the SQL views automatically in the context calss, we can add them manually as below.

public partial class LocalDBContext : DbContext
{ 

    public LocalDBContext(DbContextOptions<LocalDBContext> options) : base(options)
    {

    }

    public virtual DbSet<YourView> YourView { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<YourView>(entity => {
            entity.HasKey(e => e.ID);
            entity.ToTable("YourView");
            entity.Property(e => e.Name).HasMaxLength(50);
        });
    }

}

The sample view is defined as below with few properties

using System;
using System.Collections.Generic;

namespace Project.Entities
{
    public partial class YourView
    {
        public string Name { get; set; }
        public int ID { get; set; }
    }
}

After adding a class for the view and DB set in the context class, you are good to use the view object through your context object in the controller.

Query Types is a new way to work with SQL views in EF Core.

It is possible to scaffold a view. Just use -Tables the way you would to scaffold a table, only use the name of your view. E.g., If the name of your view is ‘vw_inventory’, then run this command in the Package Manager Console (substituting your own information for "My..."):

PM> Scaffold-DbContext "Server=MyServer;Database=MyDatabase;user id=MyUserId;password=MyPassword" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Temp -Tables vw_inventory

This command will create a model file and context file in the Temp directory of your project. You can move the model file into your models directory (remember to change the namespace name). You can copy what you need from the context file and paste it into the appropriate existing context file in your project.

Note: If you want to use your view in an integration test using a local db, you'll need to create the view as part of your db setup. If you’re going to use the view in more than one test, make sure to add a check for existence of the view. In this case, since the SQL ‘Create View’ statement is required to be the only statement in the batch, you’ll need to run the create view as dynamic Sql within the existence check statement. Alternatively you could run separate ‘if exists drop view…’, then ‘create view’ statements, but if multiple tests are running concurrently, you don’t want the view to be dropped if another test is using it. Example:

  void setupDb() {
    ...
    SomeDb.Command(db => db.Database.ExecuteSqlRaw(CreateInventoryView()));
    ...
  }
  public string CreateInventoryView() => @"
  IF OBJECT_ID('[dbo].[vw_inventory]') IS NULL
    BEGIN EXEC('CREATE VIEW [dbo].[vw_inventory] AS
       SELECT ...')
    END";