Acceptance criteria
- The Netflix Titles database is created using the supplied data and instructions.
- The Web API project uses the required name and initial authentication setting.
Map and query the stored titles
Acceptance Criteria
Acceptance criteria
- Title, Category, TitleCategory, and EntertainmentType match the reference relationships.
- DataContext maps the entities, keys, and relationships.
- The configured connection and DataService provide the required database access.
Instructions
3.Create the Models that will be used to represent the data in the application's database, these Model classes should be…
Create the Models that will be used to represent the data in the application's database, these Model classes should be stored in a new Models folder.
A
Create a new class named Category as shown in the code below.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace _12._3LabLocke.Models
{
/// <summary>
/// The class that is used to represent a Netflix category.
/// </summary>
/// <summary>
/// The class that is used to represent a Netflix category.
/// </summary>
[Table("Category")]
public class Category
{
/// <summary>
/// Gets or sets the category id.
/// </summary>
/// <summary>
/// Gets or sets the category id.
/// </summary>
public int Id { get; set; }
/// <summary>
/// Gets or sets the category name.
/// </summary>
/// <summary>
/// Gets or sets the category name.
/// </summary>
[Column("Category")]
public string Name { get; set; }
}
}Note the Table and Column attributes. These attribute specifies the database table and column that the entity and entity property should map to when the entity and entity's property name and database table and database column name differ. It uses the System.ComponentModel.DataAnnotations.Schema namespace.
B
Create the rest of the model classes as shown in the class diagram below.
- New
- Changed
- Removed
Be sure to add the TitleCategory property to the Category class.
1
Add a column attribute named "Title" to the Name property of the Title class, and a table attribute named "Title" at the class level.
2
Add a table attribute named "EntertainmentType" to the EntertainmentType class.
3
Add a table attribute named "TitleCategory" to the TitleCategory class.
4.Create the DataContext class. This will be used as the source to map all entities over a database connection.
A
In the Data folder, create a new class named DataContext, that implements the DBContext class.
You will need to install the Microsoft.EntityFrameworkCore package.
B
Paste the following code for the DataContext's constructor. This initializes a new instance of the DataContext class by…
Paste the following code for the DataContext's constructor. This initializes a new instance of the DataContext class by referencing the connection and mapping source.
/// <summary>
/// Initializes a new instance of the data context class.
/// </summary>
/// <param name="options">The data context connnection options.</param>
/// <summary>
/// Initializes a new instance of the data context class.
/// </summary>
/// <param name="options">The data context connnection options.</param>
public DataContext(DbContextOptions<DataContext> options)
: : base(options)
{ }C
Add properties of type DBSet for each of the models in the Models folder as shown below.
public DbSet<Category> Categories { get; set; }D
Override the OnModelCreating method to configure the models to the data context.
1
Add this method to the DataContext class.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}2
Create a one to one relationship between title and entertainment type as shown below.
// 1 to 1 relationship between title and entertainment type.
modelBuilder.Entity<Title>()
.HasOne(t => t.Type);3
Create a one to many relationship between title and title category as shown below.
// 1 to many relationship between title and title category.
modelBuilder.Entity<TitleCategory>()
.HasOne(tc => tc.Title)
.WithMany(t => t.TitleCategory);
..HasForeignKey(tc => tc.TitleId)4
Create a one to many relationship between category and title category as done in the previous step.
5.Create a connection with SQL Server and the NetflixTitles database.
You will need to install the Microsoft.EntityFrameworkCore.SqlServer package
A
In your appsettings.json file, after AllowedHosts, add a ConnectionString object containing the SQL connection string as…
In your appsettings.json file, after AllowedHosts, add a ConnectionString object containing the SQL connection string as shown below. You will need to replace the Server property value with your local machine's SQL Server host name.
"ConnectionStrings": { "SQL": "Server=YourServerNameHere; Database=NetflixTitles; Trusted_Connection=TRUE" }
You can find your server name when connecting to your SQL Server Management Studio, as shown in the image below.
The Trusted_Connetion property when set to TRUE, enables Window's authentication. If you would use SQL Server Authentication, you would need to provide user and password properties, and remove the TrustedConnection property.
B
Add the DataContext to the application services.
1
In Program.cs, either at the end of the ConfigureServices method or in the body of a .Net Core Program.cs script, add the…
In Program.cs, either at the end of the ConfigureServices method or in the body of a .Net Core Program.cs script, add the following code to add the context to the services.
_ = services.AddDbContext<DataContext>
(options => options.UseSqlServer(Configuration.GetConnectionString("SQL"))
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));6.Create the DataService class to define the basic CRUD operations.
A
Create a new folder named services, in the folder, add a new class named DataService.
B
Create a private readonly field of type DataContext named _dataContext.
C
Create a constructor to initialize the instance of the DataService class. Give it a parameter named dataContext of type DataContext.
1
In the constructor set the dataContext parameter to the _dataContext field.
