ASP.NET Core 2.2 For Beginners (Part 8): Entity Framework

In this chapter, you will set up Entity Framework (EF) and get familiar with how it works. To work with EF, you must install the proper services, either manually in the .csproj file or by using the NuGet manager.

When the services have been installed and configured in the Startup class, you need to add a data context class that inherits from the DbContext class. This class will be the con­text that you use to interact with the database. To add a table to the database, the table’s entity class must be added as a DbSet property in the context class.

When the services are installed and configured in the Startup class, you create the first migration by using the Package Manager Console and the Add-Migration command. When the initial migration has been added, the database can be generated with the Update-Database command.

If you make any changes to the database, like adding or changing columns or tables, then you must execute the Add-Migration and Update-Database commands again for the ap­plication to work properly.

In previous versions you had to install Entity Framework NuGet packages to create and use a database. In ASP.NET Core 2.2 those NuGet packages are installed as part of the Microsoft.AspNetCore.App NuGet package.

The setup for User Secrets has been incorporated into the new BuildWebHost method in the Program.cs file, which means that you no longer have to add any configuration for it in the Startup class. You can use User Secrets to store sensitive data locally in a file called secrets.json, which is stored outside the solution folder structure and is never committed to the source code repository, if you use that. You will store the connection string to the database securely in the secrets.json file.

Adding the VideoDbContext Class

Now that the NuGet packages have been installed, you can add a class called VideoDb­Context that inherits form the DbContext class. This class will be your connection to the database. It defines the entity classes as DbSet properties, which are mirrored as tables in the database.

For the AddDbContext method to be able to add the context to the services collection, the VideoDbContext must have a constructor with a DbContextOptions<VideoDbCon­text> parameter, which passes the parameter object to its base constructor. The OnModelCreating method must be overridden to enable Entity Framework to build the entity model for the database.

1. Add a new folder called Data to the project.
2. Add a class called VideoDbContext to the Data folder in the Solution Explorer.
3. Inherit the DbContext class in the VideoDbContext The DbContext class is in the Microsoft.EntityFrameworkCore namespace.

public class VideoDbContext : DbContext { }

4. Add a DbSet property for the Video class in the VideoDbContext the Video class is in the AspNetCore22Intro.Entities namespace.

public DbSet<Video> Videos { get; set; }

5. Add the constructor with a DbContextOptions<VideoDbContext>

public VideoDbContext(DbContextOptions<VideoDbContext> options) : base(options) { }

6. Override the OnModelCreating

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
}

7. Save all the files.

The complete code for the VideoDbContext class:

public class VideoDbContext : DbContext
{
    public DbSet<Video> Videos { get; set; }

    public VideoDbContext(DbContextOptions<VideoDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }
}

Configuration in the Startup Class

Before the initial migration can be applied, you have to configure Entity Framework to use the VideoDbContext, and read the connection string from the secrets.json file. Using the secrets.json file has two purposes: It stores the connection string in a safe place that is not checked into source control. It also renders the appsettings.json obso­lete for storing secret or sensitive data, which is a good thing, since it is checked into source control.

1. Right click on the project node in the Solution Explorer and select Manage User Secrets.
2. Add the following connection string property. Note that the database name is VideoCoreIntroDb. The connection string should be on one row in the file. You can add another App Settings File, copy the connection string from that file, paste it into the secrets.json file and change the database name. Don’t forget to delete the temporary appsettings file you added.

"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=VideoCoreIntroDb;Trusted_Connection=True;MultipleActiveResultSets=true"
}

3. Locate the ConfigureServices method and fetch the connection string from the json file using the Configuration object. Store the connection string in a variable called conn.

var conn = Configuration.GetConnectionString("DefaultConnection");

4. Use the AddDbContext method on the services collection to add the database context and the EF services at the beginning of the ConfigureServices Call the UseSqlServer method on the options action in its constructor to specify that you want to use a SQL Server database provider. The UseSqlServer method is in the Microsoft.EntityFrameworkCore namespace and the VideoDbContext class is in the AspNetCore22Intro.Data namespace. Note that DefaultConnection is the name of the property you added to the secrets.json file.

services.AddDbContext<VideoDbContext>(options =>
    options.UseSqlServer(conn));

The complete code for the secrets.json file:

The complete code for the secrets.json file:
{
    "ConnectionStrings": {
        "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=VideoCoreIntroDb;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
}

Note that the DefaultConnection property value should be one line of code.

The complete code for the Startup class’s ConfigureServices method:

public void ConfigureServices(IServiceCollection services)
{
    var conn = Configuration.GetConnectionString("DefaultConnection");

    services.AddDbContext<VideoDbContext>(options =>
        options.UseSqlServer(conn));

    services.AddMvc();
    services.AddSingleton<IMessageService,
        ConfigurationMessageService>();
    services.AddSingleton<IVideoData, MockVideoData>();
}

Adding the Initial Migration and Creating the Database

To add the initial migration and create the database, you execute the Add-Migration and Update-Database commands in the Package Manager Console (View-Other Windows-Package Manager Console).

When the Add-Migration command has been successfully executed, a new folder called Migrations will appear in the project. The current and all future migrations will be stored in this folder.

If you encounter the error message No parameterless constructor was found on 'VideoDbContext': Either add a parameterless constructor to 'VideoDbContext' or add an implementation of 'IDbContextFactory<VideoDbContext>' in the same assembly as 'VideoDbContext', then check that your connection string in secrets.json is correct and that it is being loaded in the Startup class, before doing any other troubleshooting.

1. Open the Package Manager Console.
2. Type in the command add-migration Initial and press Enter. Note the Migrations folder, and the migration files in it.
3. Execute the command update-database in the Package Manager Console to create the database.
4. Open the SQL Server Object Explorer from the View
5. Expand the MSSQLLocalDb node, and then the Databases If the VideoCoreIntroDb database isn’t visible, right click on the Databases node and select Refresh.
6. Expand the VideoCoreIntroDb node, and then the Tables You should now see the Videos table in the VideoCoreIntroDb database that you just created.
7. Expand the Videos table and then the Columns You should now see the columns in the table. Note that they match the properties in the Video entity class, and that they have the restrictions from the attributes you added to its properties.
   
8. Right click on the Videos table and select View Data. This will open the table in edit mode. Add a genre id from the Genres enum (it is zero based) and a title. Press Enter to commit the value to the database. Add a few more videos if you like.
   

Adding the SqlVideoData Service

To use the database in the application, you can implement the IVideoData interface in a new service component class. Then, you change the service registration in the Configure­Services method in the Startup class to create instances of the new component.

Implementing the SqlVideoData Service Component Class

Let’s begin by implementing the SqlVideoData class that will communicate with the data­base through the VideoDbContext.

1. Add a class called SqlVideoData to the Services
2. Add a private field called _db to the SqlVideoData This variable will hold the context needed to communicate with the database. Add a using statement to the VideoDbContext in the AspNetCore22Intro.Data namespace.

private readonly VideoDbContext _db;

3. Add a constructor that is injected with an instance of the VideoDbContext class; name the parameter db. Assign the injected object in the db parameter to the _db

public SqlVideoData(VideoDbContext db)
{
    _db = db;
}

4. Implement the IVideoData You can use the light bulb button when hovering over the interface name.

public class SqlVideoData : IVideoData

5. Replace the throw statement in the Add method with a call to the Add method on the _db context and pass in the video object to the method. Then call the SaveChanges method on the _db context to persist the changes in the database.

public void Add(Video newVideo)
{
    _db.Add(newVideo);
    _db.SaveChanges();
}

6. Replace the throw statement in the Get method with a call to the Find method on the _db context to fetch the video matching the id passed-in to the method. Return the fetched video.

public Video Get(int id)
{
    return _db.Find<Video>(id);
}

7. Replace the throw statement in the GetAll method with a return statement that returns all the videos in the Videos

public IEnumerable<Video> GetAll()
{
    return _db.Videos;
}

8. Open the Startup class and change the service registration for the IVideoData interface to create instances of the SqlVideoData Also, change the method from AddSingleton to AddScoped for the service to work with Entity Framework.

services.AddScoped<IVideoData, SqlVideoData>();

9. Save all the files and navigate to the root URL (/Home). The list of videos from the database should be displayed.
   
10. Add a new video by navigating to the /Home/Create URL and fill out the form. When you click the Create button in the form, the Details view should display the new video.
    
    
11. Click the Home link and make sure that the video is in the list.
    
12. Open the Video table in the SQL Server Object Explorer and verify that the video is in the table.
    

The complete code in the SqlVideoData class:

public class SqlVideoData : IVideoData
{
    private readonly VideoDbContext _db;

    public SqlVideoData(VideoDbContext db)
    {
        _db = db;
    }

    public void Add(Video newVideo)
    {
        _db.Add(newVideo);
        _db.SaveChanges();
    }

    public Video Get(int id)
    {
        return _db.Find<Video>(id);
    }

    public IEnumerable<Video> GetAll()
    {
        return _db.Videos;
    }
}

The complete code in the ConfigureServices method:

public void ConfigureServices(IServiceCollection services)
{
    var conn = Configuration.GetConnectionString("DefaultConnection");

    services.AddDbContext<VideoDbContext>(options =>
        options.UseSqlServer(conn));

    services.AddMvc();
    services.AddSingleton<IMessageService,
        ConfigurationMessageService>();

    services.AddScoped<IVideoData, SqlVideoData>();
}

Summary

In this chapter, you installed the Entity Framework and User Secrets services in the Setup class.

You also added a DbContext class that communicates with the database, and a new service component class that implements the IVideoData Interface, as a separation between the DbContext and the application.

Finally, you added a new video to the database using the Create view, and verified that it had been added to the database.