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