Entity Framework Core (EF) with SQL Server LocalDB

This blog is going to illustrate how to implement Entity Framework (EF) Core with SQL Server LocalDB. The Entity Framework Core enables access to data from the database. This allows developers to avoid having to write most of the data access code. And the SQL Server LocalDB works similarly to the SQL SERVER with few features. You can get the LocalDB when you install visual studio or visual studio express. You can find the .mdf and _log.ldf files in the C:/Users/{user} directory.

Let see how to create a SQL Server LocalDB and implement Entity Framework Core

Step 1:
The first step is to add a connection. In the menu -> click Tools and select Connect to Database

It will display the Add Connection window. Enter (localdb)\mssqllocaldb in the server name textbox. Then in the Select or enter a database, select master and click ok

Now in the server explorer, you can find the master DB containing Tables, Views, Stored Procedures, etc.

Step 2:
This step, going to describe how to create a new database. To create a new database, right-click on the master database in the server explorer and select New Query. It will open the New Query window.

In the query window, you can write the query to create a new database like the one below. Click the Execute button to run the query or press Ctrl + Shift + E to run the Query

CREATE DATABASE SampleDB
GO
USE SampleDB

Also, you can create the database using GUI. For that, In Server Explorer, right-click on the Data Connection and select Create New SQL Server Database... It will open the Create New SQL Server Database window. Enter the server name (localdb)\mssqllocaldb in the server name textbox and enter your new database name and click the ok button to create a database.

Step 3:
After creating the database, you have to create a table. You can create a table using a query window or GUI. Copy and paste the following query to create a table using the query window.

CREATE TABLE MovieTable
(
MovieID INT IDENTITY(1,1) PRIMARY KEY,
MovieName VARCHAR(100),
ReleasedYear INT
)

For GUI, Right-click on the Table folder in Server Explorer and select Add New Table.

Step 4:
To use the Database in ASP.NET Core, I have to use Microsoft Entity Framework core. The following is a command to install the Entity Framework core packages using Package Manager Console. To get a Package Manager Console click Tools -> NuGet Package Manager -> Package Manager Console

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

Install-Package Microsoft.EntityFrameworkCore.Tools

The below one is a screenshot of Solution Explorer. You can find a list of entity framework core packages under the package.

Step 5:
The next step is a DB Model creation. I have created a folder called Models to store the models. Now following command is to create a DBContext. Run this command in Package Manager Console

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=SampleDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

The following is a screenshot of the solution explorer after executing the Scaffold-DbContext command. Here you can see the SamepleDBContext.cs and MovieTable.cs files under the Models folder.

Step 6:
This step is going to explain how to update the Entity Framework Core. I have added a new column CoProduction to the table MovieTable.

ALTER TABLE MovieTable ADD CoProduction VARCHAR(100)

The following is a Scaffold-DbContext command to update the Entity Framework Core.

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=SampleDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -force

After executing this command, you can see the new column update in the MovieTable.cs file.

namespace SampleWebApplication.Models

{

    public partial class MovieTable

    {

        public int MovieId { get; set; }

        public string MovieName { get; set; }

        public int? ReleasedYear { get; set; }

        public string CoProduction { get; set; }

    }

}

I hope this helps you. Keep coding.