Working with SQL views in Entity Framework Core

Question

For example, I have such model:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public BlogImage BlogImage { get; set; }
}

public class BlogImage
{
    public int BlogImageId { get; set; }
    public byte[] Image { get; set; }
    public string Caption { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
} 

I want to return in ImageView view Url and Image.

Where do I need to create and define that SQL view?

Answer 1

In Entity Framework Core 2.1 we can use Query Types as Yuriy N suggested.

A more detailed article on how to use them can be found here

The most straight forward approach according to the article's examples would be:

1.We have for example the following entity Models to manage publications

public class Magazine
{
  public int MagazineId { get; set; }
  public string Name { get; set; }
  public string Publisher { get; set; }
  public List<Article> Articles { get; set; }
}

public class Article
{
  public int ArticleId { get; set; }
  public string Title { get; set; }
  public int MagazineId { get; set; }
  public DateTime PublishDate { get;  set; }
  public Author Author { get; set; }
  public int AuthorId { get; set; }
}
public class Author
{
  public int AuthorId { get; set; }
  public string Name { get; set; }
  public List<Article> Articles { get; set; }
}

2.We have a view called AuthorArticleCounts, defined to return the name and number of articles an author has written

SELECT
  a.AuthorName,
  Count(r.ArticleId) as ArticleCount
from Authors a
  JOIN Articles r on r.AuthorId = a.AuthorId
GROUP BY a.AuthorName

3.We go and create a model to be used for the View

public class AuthorArticleCount
{
  public string AuthorName { get; private set; }
  public int ArticleCount { get; private set; }
}

4.We create after that a DbQuery property in my DbContext to consume the view results inside the Model

public DbQuery<AuthorArticleCount> AuthorArticleCounts{get;set;}

4.1. You might need to override OnModelCreating() and set up the View especially if you have different view name than your Class.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Query<AuthorArticleCount>().ToView("AuthorArticleCount");
}

5.Finally we can easily get the results of the View like this.

var results=_context.AuthorArticleCounts.ToList();

UPDATE According to ssougnez's comment

It's worth noting that DbQuery won't be/is not supported anymore in EF Core 3.0. See here

Answer 2

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")

Answer 3

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

Answer 4

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.

Answer 5

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; }

Answer 6

QueryTypes is the canonical answer as of EF Core 2.1, but there is another way I have used when migrating from a database first approach (the view is already created in the database):

• define the model to match view columns (either match model class name to view name or use Table attribute. Ensure [Key] attribute is applied to at least one column, otherwise data fetch will fail
• add DbSet in your context
• add migration (Add-Migration)
• remove or comment out code for creation/drop of the "table" to be created/dropped based on provided model
• update database (Update-Database)

Answer 7

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";