How to use sql view in mvc core page

Question

I have view with multiple table records in database and I want to use it in mvc controller
Is there any procedure to do this with example

Answer 1

Hi @anil kumar ,

You can refer the following example, in this example, we create a Blog and Post class:

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

After migration and insert the seed data, the result as below:

Then, to use the SQL view with EF core, we could use the following steps:

Suppose the SQL view looks as below:

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  

1. we could accord the query result to create model:

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

2. Configure the DbContext to include the DbSet<T>: public DbSet<BlogPostsCount> BlogPostCounts { get; set; } And use fluent configuration API to configure the mapping for the keyless entity type:

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

3. Running add-migration addView_BlogPostCounts command (in the Package Manage Console) to generate the migration file. Then, update the content as below:

   public partial class addView_BlogPostCounts : Migration  
   {  
       protected override void Up(MigrationBuilder migrationBuilder)  
       {  
           var sql = @"  
               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";  
   
           migrationBuilder.Sql(sql);  
       }  
   
       protected override void Down(MigrationBuilder migrationBuilder)  
       {  
           migrationBuilder.Sql(@"DROP VIEW View_BlogPostCounts");  
       }  
   }  
   

4. Runing the update-database command in the Package Manage Console. The above code will generate the view in the database, you can use SQL Server Management Studio (SSMS) to check it.
5. Finally, in the controller, you could access the view via the dbcontext. Code like this:

   public class HomeController : Controller  
   {   
       private readonly ApplicationDbContext _context;   
       public HomeController( ApplicationDbContext applicationDbContext )  
       {   
           _context = applicationDbContext;   
       }  
       public IActionResult Index()  
       {   
           var viewresult = _context.BlogPostCounts.ToList();  
   
           return View();  
       }  
   

   The result as below:

Besides, if you have already created the view in the database, for example, you have already created the View_BlogPostCounts view.
You could base on the view result to create model and enable migration. But after running the add-migration XXX command, there is no need to call the migrationBuilder.Sql() method, the Up and Down method should be empty. Then, update the database. After that, in the controller, you could use the FromSqlRaw() method to execute the SQL queries.

Like this:

 var sqlresult = _context.BlogPostCounts.FromSqlRaw("select * from [View_BlogPostCounts]").ToList();  

The result as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion