Working with SQL views in Entity Framework Core

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

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

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

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.