During this session of ASP.NET Core Tutorial, we discuss connecting SQL Server with EF Core and MySQL with EF Core, also we will talk about EF Core Migration. In Session 45 we have made our Web App ready to work with Entity Framework Core. Moreover, we installed the EF Core required NuGet Packages, and created the DBContext class. In this session, we add DbSet to the DBContext and connect EF Core with SQL Server and MySQL database engine.
What is Migrations in EF Core?
Through Migration in Entity Framework Core, we can keep the database in sync with EF Core Models. It means we can modify the database structure and keep the data when we change Entity Framework Domain Models.
Connect SQL Server with Entity Framework Core
To connect the Web App and its DBContext with SQL Server, the Web App needs some Nuget Packages that we installed during the previous session. Then, the Web App can be connected to the SQL Server through Services in Startup Class. Also, we want to store the Connection String outside of our source code, thus we keep them inside appsettings.json. Hence for accessing the Connection String inside StraStartuptup class we need to create a Contractor for the class and inject an instance of the IConfiguration interface.
|
1 2 3 4 5 6 7 8 9 10 |
private readonly IConfiguration Configuration; public Startup(IConfiguration configuration) { Configuration = configuration; } |
Then, we create respective Connection Strings and you can see two Connection Strings. The first one is related to a version of SQL Server that is installed automatically with Visual Studio. And, the second one is related to the server version of SQL Server.
|
1 2 3 4 5 6 7 |
"ConnectionStrings": { "CostDBConnectionSQLLocal": "server=(localdb)\\MSSQLLocalDB;database=CostDB;Trusted_Connection=true", } |
|
1 2 3 4 5 6 7 |
"ConnectionStrings": { "CostDBConnectionSQLServer": "server=.;database=CostDB;user=sa;password=goldencourses2022;Persist Security Info=False", } |
Next, we need to create a service that connects SQL Server with EF Core through the above Connection Strings.
|
1 2 3 4 5 6 7 8 9 |
public void ConfigureServices(IServiceCollection services) { services.AddDbContextPool<WebAppDBContext> (options=> options.UseSqlServer(Configuration.GetConnectionString("CostDBConnectionSQLServer"))); } |
Connect MySQL with Entity Framework Core
First, we need to install the respective Entity Framework Core Provider based on the selected database engine. Hence, we can find a proper provider through the EF Core provider List and install it via NuGet Package Manager. As we decide to use the MySQL DB engine we should install MySql.EntityFrameworkCore Nuget package. Then, we need to create a Connection String for this DB engine.
|
1 2 3 4 5 6 7 |
"ConnectionStrings": { "CostDBConnectionMySQL": "server=localhost;database=CostDB;user=root;password=;Persist Security Info=False" } |
Afterward, we need to create a Service inside the Startup class as shown below.
|
1 2 3 4 5 6 7 8 9 |
public void ConfigureServices(IServiceCollection services) { services.AddDbContextPool<WebAppDBContext> (options=> options.UseMySQL(Configuration.GetConnectionString("CostDBConnectionMySQL"))); } |
Persist Security Info & Trusted_Connection in Connection String
Persist Security Info feature just apply on connection string when we use sensitive information inside the connection string same as UserName and Password. if we set the feature to False, sensitive data is not returned as part of the connection when it is open. Also, Persist Security Info default value is false.
Trusted_Connection or Integrated Security would help the application to use Windows authentication to authenticate with SQL Server. for enabling this option we should set Trusted_Connection= True or Integrated Security=True/SSPI.
Migration in EF Core
Through Migration in Entity Framework Core, we can keep the database in sync with EF Core Models. It means we can modify the database structure and keep the data when we change Entity Framework Domain Models. Thus, we need to add Domain Models to DBContext as a property with the type of DbSet.
|
1 2 3 4 5 6 7 8 9 10 11 |
public class WebAppDBContext : DbContext { public WebAppDBContext(DbContextOptions<WebAppDBContext> options) : base(options) { } public DbSet<Category> Categories { get; set; } } |
Then, we can migrate the Domain Models to Database with Migration commands that should be run in Package Manager Console. You can find this console in View -> Other Windows -> Package Manager Console.
EF Core Migration commands need Microsoft.EntityFrameworkCore.Tools NuGet package that should be installed.
- Add-Migration: Creates a migration by adding a migration snapshot based on Domain Models.
- Remove-Migration: Removes the last migration snapshot.
- Update-Database: Updates the database schema based on the last migration snapshot.