Exercise - Reverse-engineer from an existing database

Completed

Contoso Pizza's manager asked you to add an endpoint so that the company can display coupons in its app. The coupons reside in an existing database. In this unit, you create scaffolding from an existing database and modify the resulting entity class.

Inspect the promotions database

Take a look at the database you're going to use to generate the scaffolded code:

1. On the Explorer pane, expand the Promotions directory, right-click the Promotions.db file, and then select Open Database.

   The new database opens in the SQLite Explorer folder.

2. In the SQLite Explorer folder, expand the Promotions.db and Coupons nodes. Note the data schema.

3. Right-click the Coupons node and select Show table. Inspect the coupon data.

Scaffold the promotions context and coupon model

Now, you use the database to scaffold the code:

1. Run the following command:

   dotnet ef dbcontext scaffold "Data Source=Promotions/Promotions.db" Microsoft.EntityFrameworkCore.Sqlite --context-dir Data --output-dir Models   
   

   The preceding command:

   • Scaffolds DbContext and model classes by using the provided connection string.
   • Specifies to use the Microsoft.EntityFrameworkCore.Sqlite database provider.
   • Specifies directories for the resulting DbContext and model classes.

   Note

   In this exercise, you can ignore the warning about your connection string being in source code. In real-world code, always store your connection strings in a secure location.

2. Open Models\Coupon.cs and inspect the scaffolded model.

   using System;
   using System.Collections.Generic;
   
   namespace ContosoPizza.Models
   {
       public partial class Coupon
       {
           public int Id { get; set; }
           public string Description { get; set; } = null!;
           public DateOnly? Expiration { get; set; }
       }
   }
   

   Since SQLite has a limited set of types compared to C#, the scaffolding tool made inferences as to what C# types to use. For example, the Expiration database column was defined as a string because SQLite doesn't have a date data type. The scaffolding tool inferred that the C# type should be DateOnly? based on the data in the database.

   Tip

   If the database changes, you can generate new scaffolded files. The generated files are overwritten each time, but they're created as partial classes, so you can extend them with custom properties and behaviors in your own, separate files.

Add a coupon endpoint

Before you can test the scaffolded code, you need to add an endpoint to the API. So, you next add a new API controller.

To better understand how API controllers work, see Create a web API with ASP.NET Core controllers.

1. In the Controllers folder, add a file named CouponController.cs that contains the following code:

   using ContosoPizza.Data;
   using ContosoPizza.Models;
   using Microsoft.AspNetCore.Mvc;
   using Microsoft.EntityFrameworkCore;
   
   namespace ContosoPizza.Controllers;
   
   [ApiController]
   [Route("[controller]")]
   public class CouponController : ControllerBase
   {
       PromotionsContext _context;
   
       public CouponController(PromotionsContext context)
       {
           _context = context;
       }
   
       [HttpGet]
       public IEnumerable<Coupon> Get()
       {
           return _context.Coupons
               .AsNoTracking()
               .ToList();
       }
   }
   

   This code adds an api/coupon endpoint to the API.

   In the preceding code:

   • A PromotionsContext object is injected into the constructor.
   • The Get method returns all the coupons.

2. In Program.cs, replace the // Add the PromotionsContext comment with the following code:

   builder.Services.AddSqlite<PromotionsContext>("Data Source=Promotions/Promotions.db");
   

   This code registers PromotionsContext with the dependency injection system.

3. Save all your changes and run the app by using dotnet run.

Test the endpoint

Now that the endpoint is added, test the coupon operations:

1. Go to the API's Swagger UI like you did in an earlier exercise (or refresh the existing UI in your browser).

2. Under the Coupon heading, expand the GET operation and select the Try it out button.

3. Select Execute. The response body shows the coupons from the database:

   [
   {
       "id": 1,
       "description": "Buy 1 get 1 free",
       "expiration": "2025-01-01T00:00:00"
   },
   {
       "id": 2,
       "description": "4 large pizzas for $40",
       "expiration": "2024-06-30T00:00:00"
   }
   ]
   

   Notice that expiration is a datetime value.

That's it! You created and modified scaffolding from an existing database!