Mvc core with Sql View

anil kumar 61 Reputation points
2021-11-08T13:36:06.62+00:00

how can I use sql view in mvc core with entity framework codefirst

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,786 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,946 Reputation points Microsoft Vendor
    2021-11-09T08:40:20.48+00:00

    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:

    147640-image.png

    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. 147693-image.png
    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: 147656-1.gif

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.