Working with SQL Views in the Entity Framework Core

Question

For example, I have a model like this:

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 to the ImageView representation of the URL and Image .

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

Answer 1

In Entity Framework Core 2.1, we can use query types , as suggested by Yuri N.

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

. The simplest approach in accordance with the examples from the article:

1. We have, for example, the following entity models for publishing management

 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 title and number of articles written by the author

 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 that will be used for the view

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

4. After that, we create the DbQuery property in my DbContext to use the view results inside the model.

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

5. Finally, we can easily get the viewing results as follows.

 var results=_context.AuthorArticleCounts.ToList(); 

UPDATE According to the comment

It is worth noting that DbQuery will no longer / is not supported in EF Core 3.0

Answer 2

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

However, you can trick EF into using the view by mapping your entity to the view as if it were a table. This approach has limitations. for example, you cannot use migrations, you need to manually specify the key for EF, and some requests may not work correctly. To get around this last part, you can write SQL queries manually

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

Answer 3

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

Answer 4

EF Core does not automatically create DBset for SQL views in the context of calss, we can add them manually, as shown 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); }); } } 

A sample sample is defined below, with several 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 the class for the view and the DB specified in the context class, you can use the view object through your context object in the controller.

Answer 5

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

• define a model for matching view columns (either match the model class name with the view name, or use the table attribute. Make sure that the [Key] attribute is applied to at least one column, otherwise the data selection will fail
• add dbset to your context
• add migration
• delete or comment out the code for creating / deleting a “table” that will be created / deleted based on the provided model
• update database (Update-Database)