Performing Database Operations using Entity Framework Core and ASP.NET Core

Entity Framework Core (EF Core) is the newest database programming framework for .NET developers. It's an obvious choice for ASP.NET Core developers. To that end, this article illustrates how to utilize Entity Framework Core for performing CRUD (create, read, update, and delete) operations on data.

Note: This article uses RC2 of EF Core. Although there won't be many code level changes under the final version, you may need to adjust the package references accordingly.

Create and Configure the Web Application

To begin, create a new ASP.NET Web Application using Visual Studio 2015. You may read this article for quick instructions to do just that. Once the Web application is created, open the Project.json file and add the Entity Framework-specific entries. The following markup shows the relevant entries of Project.json.

"Microsoft.EntityFrameworkCore.SqlServer":
   "1.0.0-rc2-final",
"Microsoft.EntityFrameworkCore.Tools": {
   "version": "1.0.0-preview1-final",
   "type": "build"
},

You also may consider modifying the tools section of Project.json file to include this:

"Microsoft.EntityFrameworkCore.Tools": {
   "version": "1.0.0-preview1-final",
   "imports": [
      "portable-net45+win8+dnxcore50",
      "portable-net45+win8"
   ]

You can grab the complete Project.json from the code download of this article.

Then, open the Startup class and modify it as shown in the following code:

public class Startup
{
   public static IConfiguration Configuration { get; set; }

   public Startup(IHostingEnvironment env)
   {
      ConfigurationBuilder builder =
         new ConfigurationBuilder();
      builder.SetBasePath(env.ContentRootPath);
      builder.AddJsonFile("AppSettings.json");
      Configuration = builder.Build();
   }

   public void ConfigureServices(IServiceCollection services)
   {
      services.AddMvc();
      services.AddEntityFrameworkSqlServer();
   }

   public void Configure(IApplicationBuilder app)
   {
      app.UseStaticFiles();
      app.UseMvc(routes =>
      {
         routes.MapRoute(
            name: "default",
            template: "{controller=Home}/
               {action=Index}/{id?}");
      });
   }
}

The preceding code consists of a constructor and two methods, ConfigureServices() and Configure(). The constructor reads the AppSettings.json file. The database connection string is typically stored in the AppSettings.json file. You can add this file to the project by using the Add New Item dialog and then add the following database connection string to it:

{
   "Data": {
      "DefaultConnection": {
         "ConnectionString": "Server=.;
            Database=EFCoreCRUDDemo;
            Trusted_Connection=True;"
      }
   }
}

This configuration is read inside the Startup constructor and is loaded into a static property, Configuration. We do this so that the configuration information can be retrieved anywhere in the application.

Note: There are better ways, such as DI, to pass configuration information to the rest of the application. However, to keep things simple, this example uses a static variable.

The ConfigureServices() method adds MVC and Entity Framework for SQL Server services to the container.

Creating the DbContext and Entity Class

Now that you have created and configured the project, add a Models folder to it and add a class named Customer. The Customer entity class is shown in the next code:

[Table("Customers")]
public class Customer
{
   [Key]
   [Required]
   [StringLength(5)]
   public string CustomerID { get; set; }
   [Required]
   [StringLength(30)]
   public string CompanyName { get; set; }
   [Required]
   [StringLength(40)]
   public string ContactName { get; set; }
   [Required]
   [StringLength(40)]
   public string Country { get; set; }
}

The [Table] attributes added to the Customer class indicates that it maps with the underlying entity maps to the Customers table of the database. The Customer class has four properties: CustoemrID, CompanyName, ContactName, and Country. These properties also have data annotations for the sake of validation. These attributes also are used during the database creation process (more on that later).

Then, add the DbContext class named AppDbContext. The AppDbContext class is shown below:

public class AppDbContext:DbContext
{
   public DbSet<Customer> Customers { get; set; }

   protected override void OnConfiguring
      (DbContextOptionsBuilder optionsBuilder)
   {
      optionsBuilder.UseSqlServer(Startup.Configuration
         ["Data:DefaultConnection:ConnectionString"]);
   }
}

The AppDbContext class inherits from the DbContext base class and overrides the OnConfiguring() method of the base class. Inside the OnConfiguring() method, the code calls the UseSqlServer() method of DbContextOptionsBuilder and passes it a database connection string. Notice how the database connection string is read using the Configuration static property. The AppDbContext class also has a Customers DbSet.

Creating the HomeController

Now, add a Controllers folder to the project and add a HomeController class to it. The HomeController class consists of six actions: Index(), two Insert() variations, two Update() variations, and Delete(). The Index() action is shown below:

public IActionResult Index()
{
   using (AppDbContext db = new AppDbContext())
   {
      var query = from c in db.Customers
                  orderby c.CustomerID ascending
                  select c;
      return View(query.ToList());
   }
}

The Index() action instantiates the AppDbContext and fetches all the Customer records. A List of Customer objects is passed to the Index view as its model.

The Insert() method has two overloads, one that handles GET requests and the other that handles POST requests. These variations are shown below:

public IActionResult Insert()
{
   return View();
}

[HttpPost]
public IActionResult Insert(Customer obj)
{
   if (ModelState.IsValid)
   {
      using (AppDbContext db = new AppDbContext())
      {
         db.Customers.Add(obj);
         db.SaveChanges();
      }
   }
   return View(obj);
}

The first Insert() action simply returns an empty Insert view to the browser. The second Insert() action is called when the user fills the Insert view and clicks the Save button. Through the model binding, a Customer object is received and is added to the Customers DbSet. The SaveChanges() method is called to persist the changes to the database.

The Update() actions are similar to the Insert() actions with a little change.

public IActionResult Update(string id)
{
   using (AppDbContext db = new AppDbContext())
   {
      Customer obj = db.Customers.Where(c => c.CustomerID ==
         id).SingleOrDefault();
      return View(obj);
   }
}

[HttpPost]
public IActionResult Update(Customer obj)
{
   if (ModelState.IsValid)
   {
      using (AppDbContext db = new AppDbContext())
      {
         db.Entry(obj).State = EntityState.Modified;
         db.SaveChanges();
      }
   }
   return View(obj);
}

The first Update() action receives a CustomerID through the id route parameter. It then fetches an existing Customer based on this CustomerID. The Customer object thus obtained is passed to the Update view. This is done so that the Update view can display existing customer data for modification.

The second Update() is called when the user modifies a customer and clicks the Save button. This method sets the State property to Modified to mark that the entity is a modified one. Then, SaveChanges() is called to save the changes to the physical database.

The Delete() action is quite straightforward and is as follows:

public IActionResult Delete(string id)
{
   using (AppDbContext db = new AppDbContext())
   {
      Customer obj = db.Customers.Where(c => c.CustomerID ==
         id).SingleOrDefault();
      db.Customers.Remove(obj);
      db.SaveChanges();
      return RedirectToAction("Index");
   }
}

The Delete() action receives a CustomerID through the id route parameter. The code then finds an existing Customer entry matching that CustomerID. The Customer object is then deleted from the DbSet using the Remove() method. The SaveChanges() method is called as before to persist the changes. Once a record is deleted, the user is taken back to the Index page by using the RedirectToAction() method.

Creating the Views

Now, add a Views folder under the project folder and also add a Home subfolder under it. Then, add an Index.cshtml view file to the Views > Home folder. The Index view is shown below:

@model List<Customer>

<html>
   <head>
      <title>Index</title>
   </head>
   <body>
      <h1>List of Customers</h1>

      <a asp-action="Insert" asp-controller="Home">
         Add New Customer</a>
      <br />

      <table cellpadding="10">
         @foreach (var item in Model)
         {
            <tr>
               <td>@item.CustomerID</td>
               <td>@item.CompanyName</td>
               <td>@item.ContactName</td>
               <td>@item.Country</td>
               <td><a asp-action="Update" asp-controller="Home"
                  asp-route-id="@item.CustomerID">Edit</a></td>
               <td><a asp-action="Delete" asp-controller="Home"
                  asp-route-id="@item.CustomerID">Delete</a></td>
               </tr>
            }
         </table>
      </body>
</html>

The @model directives are used to specify the model to be a List of Customer objects. A table is then rendered by using a foreach loop. The table displays the CustomerID, CompanyName, ContactName, and Country. The last two columns of the table display hyperlinks to the Update and Delete actions. At the top, there is a hyperlink to Insert action. Notice how the code uses tag helpers to display the links. The asp-action, asp-controller, and asp-route-id attributes of the anchor tag helper indicate the name of the action, name of the controller, and id route parameter value, respectively.

Figure 1 shows a sample run of the Index view once the application is complete:

Crud1
Figure 1: Following a sample run of the Index view

Now, add Insert view to the Views > Home folder and key in the following markup to it. Add the following markup to the Insert view:

@model Customer

<html>
   <head>
      <title>Add New Customer</title>
      <script src="~/Scripts/jquery-2.1.1.js"></script>
      <script src="~/Scripts/jquery.validate.js"></script>
      <script src="~/Scripts/jquery.validate.unobtrusive.js"
         ></script>
   </head>
   <body>
      <h1>Add New Customer</h1>

      <a asp-action="Index" asp-controller="Home">Back</a>
      <br />

      <form asp-action="Insert" asp-controller="Home">
         <table>
             <tr>
               <td><label asp-for="CustomerID">Custoemr ID :
                  </label></td>
               <td><input type="text" asp-for="CustomerID" />
                  </td>
            </tr>
            <tr>
               <td><label asp-for="CompanyName">Company Name :
                  </label></td>
               <td><input type="text" asp-for="CompanyName" />
                  </td>
            </tr>
            <tr>
               <td><label asp-for="ContactName">Contact Name :
                  </label></td>
               <td><input type="text" asp-for="ContactName" />
                  </td>
            </tr>
            <tr>
               <td><label asp-for="Country">Country :</label></td>
               <td><input type="text" asp-for="Country" /></td>
            </tr>
            <tr>
               <td><input type="submit" value="Save" /></td>
            </tr>
         </table>
         <br />
         <div asp-validation-summary="All"></div>
      </form>
   </body>
</html>

The Insert view sets the model to Customer by using the @model directive. The form tag helper is used to render a <form> element. The asp-action and asp-controller attributes indicate that the form will be POSTed to the Insert() action of the HomeController. Inside, the code uses label and input tag helpers to display labels and textboxes, respectively. A submit button submits the form to the specified action. Notice that a <div> also is used to display validation summary errors. Figure 2 shows how the Insert view looks like at run time.

Crud2
Figure 2: The Insert view at run time

The Update view is quite similar to the Insert view and hence not discussed here.

Creating the Database

Now that you have created the model-view-controller required by the application, proceed to create the database required by the application. Although you can use SQL Server manager or Visual Studio database tools to create the database, here we will use EF Core command line tools.

Open a command prompt and navigate to the project root folder. Then, issue the following two commands, one after the other.

> dotnet ef migrations add MyMigrations
> dotnet ef database update

The first command adds a Migrations folder under the project folder. The folder contains certain EF migration-related classes. The second command applies the migrations created earlier to the database. These command automatically read the AppDbContext, entity classes, and the connection string to create the required database and tables. Figure 3 shows the Customers table of the EFCoreCRUDDemo database (we specified it in the AppSettings.json file).

Crud3
Figure 3: The Customers table of the EFCoreCRUDDemo database

Notice how the column lengths are derived from the [StringLength] attributed added to the properties.

This completes the application. You can now run it and test the CRUD operations.

Injecting DbContext Using DI Features

In the preceding example, you manually instantiated the AppDbContext in the controller. You also can inject the DbContext into the controller using the DI features. Let's see how.

Modify the ConfigureServices() method of Startup class as follows:

public void ConfigureServices(IServiceCollection services)
{
   services.AddMvc();
   services.AddEntityFrameworkSqlServer();
   services.AddDbContext<AppDbContext>(o =>
      o.UseSqlServer(Configuration
      ["Data:DefaultConnection:ConnectionString"]));
}

Here, you registered the DbContext with the DI framework. Because you specify the database connection string while registering the DbContext, you can remove the OnConfiguring() method from the AppDbContext class. The modified AppDbContext will look like this:

public class AppDbContext:DbContext
{
   public AppDbContext(DbContextOptions<AppDbContext>
      options):base(options)
   {
   }
   public DbSet<Customer> Customers { get; set; }
}

Notice that the AppDbContext now has a constructor that takes a DbContextOptions<TContext> parameter.

Then, modify the HomeController to receive the injected AppDbContext. This is shown below:

public class HomeController : Controller
{
   private AppDbContext db = null;

   public HomeController(AppDbContext db)
   {
      this.db = db;
   }
   ....
   ....
   ....
   ....
}

You now can use the db anywhere in the HomeController rather than creating your own instance of AppDbContext.

Download the Code

You can download the code that accompanies the article here.



Related Articles

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date