Post
Ask Question

Entity Framework (5), With .Net Core MVC, Database-First

After I wrote several articles on this site, I found out it seemed almost for every article, I needed to set up a sample application associated with an entity framework if accessing the database. And, every time, I needed to rewrite the setup process from scratch in order for a new reader to follow along easily. Even for introducing a very simple concept, such as Caching, I needed to spend 80% of the time setting up the sample app, and only 20%  on introducing the Caching concept itself.

Therefore, I think it is better to write a basic model such as entity framework sample for various approaches, and then I can reuse them when needed. I made a list of the series of articles below, I will write them one by one, while the Entity framework overview and concept will be covered in the article (0):

Note

We write the Entity Framework for MVC module, but the pattern is the same or similar when applying to Web Application or Web API.

Introduction

This article is about Entity Framework with .Net Core MVC, Database-First approach. 

  • Step 1: Create an ASP.NET Core MVC application
  • Step 2: Reverse engineer Entity model from database (database first approach for entity)
  • Step 3: Scaffold Controller with View using Entity Framework
  • Step 4: Run and Test app

At the end, you have an MVC app that can consume a database directly through entity framework.

Note

This article is actually from my previous article Part A: Build AS.P.NET MVC in .NET Core with Entity Framework Database First.

Step 1: Create an ASP.NET Core MVC application

We use the current version of Visual Studio 2019 16.8 and .NET 5.0 SDK to build the app.

  1. Start Visual Studio and select Create a new project.
  2. In the Create a new project dialog, select ASP.NET Core Web Application > Next.
  3. In the Configure your new project dialog, enter MVCCallWebAPI for Project name.
  4. Select Create.
  5. In the Create a new ASP.NET Core web application dialog, select,
     
    1. .NET Core and ASP.NET Core 5.0 in the dropdowns.
    2. ASP.NET Core Web App (Model-View-Controller).
    3. Create

Build and run the app, you will see the following image shows the app,

Step 2: Reverse engineer Entity model from database (database first approach for Entity)

We use a local Microsft SQL server, and the sample database pubs and its table stores as our database sample. We try to reverse engineer to get the table Stores into the project and make an entity model Store.

Click "Tools->NuGet Package Manager->Package Manager Console" as shown below i.e.

This is the reverse engineering command (when you run the command in PMC, you need to make it in one line),

  1. Scaffold-DbContext "Data Source=localhost; Initial Catalog=pubs; Integrated Security=True; Connect Timeout=30; Encrypt=False; TrustServerCertificate=False; ApplicationIntent=ReadWrite; MultiSubnetFailover= False" Microsoft.EntityFrameworkCore.SqlServer   
  2. -OutputDir Models/DB   
  3. -Table dbo.stores  

Run the command in the PMC,

We got error message above that Microsoft.EntityFrameworkCore.Design is required, but not installed. Click "Tools->NuGet Package Manager->Manage NuGet Packages for Solution" as shown below,

Choose and install: Microsoft.EntityFrameworkCore.Design,

Run the PMC command again, 

We got: Unable to find provider assembly 'Microsoft.EntityFrameworkCore.SqlServer', install it in the same way above from Manage NuGet Packages for Solution, and then reRun PMC command. This was successful and two classes are reverse engineered under Models/DB as shown below: pubsContext.cs and Store.cs

Step 3: Add Controller with View using Entity Framework

For adding controller using entity framework, we need to modify the reverse engineered classes pubsContext and Store.cs. 

1. Modify the data connection

For the class pubsContext, we need to comment out the data connection part, 

//        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
//        {  
//            if (!optionsBuilder.IsConfigured)  
//            {  
//#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.  
//                optionsBuilder.UseSqlServer("Data Source=localhost;Initial Catalog=pubs;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");  
//            }  
//        }  

and move the data connection string into file appsettings.json,

{        
  "Logging": {        
    "LogLevel": {        
      "Default": "Information",        
      "Microsoft": "Warning",        
      "Microsoft.Hosting.Lifetime": "Information"        
    }        
  },        
        
  "ConnectionStrings": {        
    "DevConnection": "Data Source=localhost;Initial Catalog=pubs;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"        
  },        
      
  "AllowedHosts": "*"        
}    

Register the database connection context into Class starup.cs inside ConfigureServices,

public void ConfigureServices(IServiceCollection services)        
{        
    // Register SQL database configuration context as services.         
    services.AddDbContext<pubsContext>(options =>        
    {        
        options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));        
    });        
        
    services.AddControllersWithViews();        
}    

Otherwise,  we could make a controller with view using this entity framework, and this would not work.

2. Modify the model

In class pubsContext, we can also comment out the data constrain part, 

//protected override void OnModelCreating(ModelBuilder modelBuilder)    
//{    
//    modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");    
    
//    modelBuilder.Entity<Store>(entity =>    
//    {    
//        entity.HasKey(e => e.StorId)    
//            .HasName("UPK_storeid");    
    
//        entity.ToTable("stores");    
    
//        entity.Property(e => e.StorId)    
//            .HasMaxLength(4)    
//            .IsUnicode(false)    
//            .HasColumnName("stor_id")    
//            .IsFixedLength(true);    
    
//        entity.Property(e => e.City)    
//            .HasMaxLength(20)    
//            .IsUnicode(false)    
//            .HasColumnName("city");    
    
//        entity.Property(e => e.State)    
//            .HasMaxLength(2)    
//            .IsUnicode(false)    
//            .HasColumnName("state")    
//            .IsFixedLength(true);    
    
//        entity.Property(e => e.StorAddress)    
//            .HasMaxLength(40)    
//            .IsUnicode(false)    
//            .HasColumnName("stor_address");    
    
//        entity.Property(e => e.StorName)    
//            .HasMaxLength(40)    
//            .IsUnicode(false)    
//            .HasColumnName("stor_name");    
    
//        entity.Property(e => e.Zip)    
//            .HasMaxLength(5)    
//            .IsUnicode(false)    
//            .HasColumnName("zip")    
//            .IsFixedLength(true);    
//    });    
    
//    OnModelCreatingPartial(modelBuilder);    
//}    
    
//partial void OnModelCreatingPartial(ModelBuilder modelBuilder);   

 but, we need to modify the data model to make the table member names exactly the same as they are in database, such as StorId into Stor_Id, and add a  [Key] for primary key in database.

The class Store.cs, before,

using System.ComponentModel.DataAnnotations;    
  
#nullable disable    
    
namespace MVCCallWebAPI.Models.DB    
{    
    public partial class Store    
    {    
        public string StorId { get; set; }    
        public string StorName { get; set; }    
        public string StorAddress { get; set; }    
        public string City { get; set; }    
        public string State { get; set; }    
        public string Zip { get; set; }    
    }    
}     

After

using System.ComponentModel.DataAnnotations;    
  
#nullable disable    
    
namespace MVCCallWebAPI.Models.DB    
{    
    public partial class Store    
    {    
        [Key]    
        public string Stor_Id { get; set; }    
        public string Stor_Name { get; set; }    
        public string Stor_Address { get; set; }    
        public string City { get; set; }    
        public string State { get; set; }    
        public string Zip { get; set; }    
    }    
}    

The final class pubsContext will be,

using Microsoft.EntityFrameworkCore;  

#nullable disable  
  
namespace MVCCallWebAPI.Models.DB  
{  
    public partial class pubsContext : DbContext  
    {  
        public pubsContext()  
        {  
        }  
  
        public pubsContext(DbContextOptions<pubsContext> options)  
            : base(options)  
        {  
        }  
  
        public virtual DbSet<Store> Stores { get; set; }  
  
//        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
//        {  
//            if (!optionsBuilder.IsConfigured)  
//            {  
//#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.  
//                optionsBuilder.UseSqlServer("Data Source=localhost;Initial Catalog=pubs;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");  
//            }  
//        }  
  
        //protected override void OnModelCreating(ModelBuilder modelBuilder)  
        //{  
        //    modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");  
  
        //    modelBuilder.Entity<Store>(entity =>  
        //    {  
        //        entity.HasKey(e => e.StorId)  
        //            .HasName("UPK_storeid");  
  
        //        entity.ToTable("stores");  
  
        //        entity.Property(e => e.StorId)  
        //            .HasMaxLength(4)  
        //            .IsUnicode(false)  
        //            .HasColumnName("stor_id")  
        //            .IsFixedLength(true);  
  
        //        entity.Property(e => e.City)  
        //            .HasMaxLength(20)  
        //            .IsUnicode(false)  
        //            .HasColumnName("city");  
  
        //        entity.Property(e => e.State)  
        //            .HasMaxLength(2)  
        //            .IsUnicode(false)  
        //            .HasColumnName("state")  
        //            .IsFixedLength(true);  
  
        //        entity.Property(e => e.StorAddress)  
        //            .HasMaxLength(40)  
        //            .IsUnicode(false)  
        //            .HasColumnName("stor_address");  
  
        //        entity.Property(e => e.StorName)  
        //            .HasMaxLength(40)  
        //            .IsUnicode(false)  
        //            .HasColumnName("stor_name");  
  
        //        entity.Property(e => e.Zip)  
        //            .HasMaxLength(5)  
        //            .IsUnicode(false)  
        //            .HasColumnName("zip")  
        //            .IsFixedLength(true);  
        //    });  
  
        //    OnModelCreatingPartial(modelBuilder);  
        //}  
  
        //partial void OnModelCreatingPartial(ModelBuilder modelBuilder);  
    }  
} 

3. Add the controller

In Solution Explorer, right-click the Controllers folder > Add > New Scaffolded Item. Then, select MVC Controller with views, using Entity Framework > Add.

Complete the Add MVC Controller with Views, using Entity Framework dialog,

  • Model class - Store(MVCCallWebAPI.Models.DB)
  • Data context class - pubsContext (MVCCallWebAPI.Models.DB)
  • Views - Keep the default of each option checked
  • Controller name - Change the default StoresController to StoresMVCController
  • Select Add

Visual Studio creates,

  • A StroesMVC controller (Controllers/StoresMVCController.cs)
  • Razor view files for Create, Delete, Details, Edit, and Index pages (Views/StoresMVC/*.cshtml)

The automatic creation of these files is known as scaffolding.

Step 4. Run and Test the app

Before we run the app, modify the header of the file: Views/Shared/_layout.cshtml Views, shown below, change the controller as StoreMVC and the app name as MVC app:

<header>    
    <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">    
        <div class="container">    
            <a class="navbar-brand" asp-area="" asp-controller="StroeMVC" asp-action="Index">MVC app</a>    
            <button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"    
                    aria-expanded="false" aria-label="Toggle navigation">    
                <span class="navbar-toggler-icon"></span>    
            </button>    
            <div class="navbar-collapse collapse d-sm-inline-flex justify-content-between">    
                <ul class="navbar-nav flex-grow-1">    
                    <li class="nav-item">    
                        <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>    
                    </li>    
                    <li class="nav-item">    
                        <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>    
                    </li>    
                </ul>    
            </div>    
        </div>    
    </nav>    
</header>     

Now, we run the app,

Click MVC app, we got the screen,

This is a MVC app that consumes the database directly through entity framework.

Summary

The .NET Core MVC module for Database-First approach is very different from the MVC module:

  1. There is no Entity Data Model Wizard available for .NET Core as .NET does;
  2. You have to run PMC command to do the Reverse engineering Entity model from database
  3. The created code is very clear, not a black box as .NET, i.e., Developer can control the created code much easier. 

Reference


Similar Articles