Data Access in ASP.NET Core using EF Core

 

Whatever type of application you develop or create, you always use some sort of data access for the purpose of reading and writing data to and from the database in your application. Way back, the developers used ADO.NET code to make the communication possible between the application and the database. Later on, after few years, Microsoft introduced an ORM framework by the name of Entity Framework to create a smooth communication between the relational databases (SQL Server) and object-oriented programming languages such as C# in .NET.

In this tutorial, I will explain you how to use Entity Framework Core with the Code First Approach in ASP.NET to create and manage database using code in C#.

 

Let us start with the example –

Below is the diagrammatic representation of the tables and the relationships between them.

Let’s start creating the ASP.NET MVC Web application by going to File > New > Project menu. Also, before starting with the code, we need to install Entity Framework Core and SQL Server Data Provider from the Nuget Package Manager.

Next step is to create the Model classes.

Product Model:

public class Product

{

[Key]

public int Id { get; set; }

[Required]

public string Name { get; set; }

[Column(TypeName = "decimal(18, 2)")]

public decimal Price { get; set; }

}

 

Customer Model:

public class Customer

{

[Key]

public int Id { get; set; }

[Required]

public string FirstName { get; set; }

[Required]

public string LastName { get; set; }

public string Phone { get; set; }

public ICollection Orders { get; set; }

}

 

Order Model:

public class Order

{

[Key]

public int Id { get; set; }

public DateTime? OrderDate { get; set; }

public int CustomerId { get; set; }

public Customer Customer { get; set; }

public ICollection ProductOrders { get; set; }

}

 

ProductOrder Model:

public class ProductOrder

{

[Key]

public int Id { get; set; }

public int Quantity { get; set; }

public int ProductId { get; set; }

public int OrderId { get; set; }

public Product Product { get; set; }

public Order Order { get; set; }

}

 

Next step is to create the DbContext class.

public class OnlineShopDbContext : DbContext

{

public OnlineShopDbContext(DbContextOptions options)

: base(options)

{

}

public DbSet Products { get; set; }

public DbSet Customers { get; set; }

public DbSet Orders { get; set; }

public DbSet ProductOrders { get; set; }

}

 

Next step is to define the Connection String in the appsettings.json file.

{

"ConnectionStrings":{

"DefaultConnection":"Server= DDC6-L-C4DF9Y2; Database=OnlineShopDb; Trusted_Connection=True; MultipleActiveResultSets=true"

},

"Logging":{

"LogLevel":{

"Default":"Information",

"Microsoft":"Warning",

"Microsoft.Hosting.Lifetime":"Information"

}

},

"AllowedHosts":"*"

}

 

Finally, we need to register the SQL Server database provider in Startup.cs file

public void ConfigureServices(IServiceCollection services)

{

services.AddControllersWithViews();

services.AddDbContext(options =>

options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

}

 

It is the time to generate our database with the help of migrations commands as shown below –

Add-Migration InitialDatabaseCreate

The above command will create a directory called Migrations in the project and will also generate some files in this folder as shown below –

                     

Next step is to create our database and tables using the migration code generated and for which we use the below command –

Update-Database command

 

When we go to SQL Server and expand our Database, we can see the tables created along with the relationships amongst them as shown below –

                                                           

                           

                           

I hope you got a basic understanding on how to use Entity Framework Core with the Code First Approach in ASP.NET to create and manage database using code in C#.