Entity Framework with .NET Core 7.0
What is ORM?
ORM stands for Object to Relational Mapping.
- Object: It is class's that we have in our programming language(C#, Python etc.)
- Relational: This is Relational Database Manager System like MS-SQL etc.
- Mapping: This is the part which bridges between objects and tables.
ORM is a technique that lets you query and manipulate data from a database using and object-oriented program.
With ORM code is simplified. We can also use LINQ queries.
With ORM: book_list = BookTable.query(author = 'sam');
// dummy example Without ORM
book_list = new List();
sql = "Select book From library Where author='sam'";
using (SqlConnection con = new SqlConnection(conString)){
using (SqlCommand cmd = new SqlCommand(sql,con)){
SqlDataReader sdr = cmd.ExecuteReader();
while(sdr.Read()){
book_list.Add(sdr["book"].ToString());
}
}
}
// With ORM
book_list = BookTable.query(author = 'sam');
- You get to write in the language you are already using anyway.
- It abstracts away the database system so that switching database is not that difficult.
- Many of the queries you write will perform better than you wrote them yourself.
- Saves you time as compared to writing SQL and Wrappers.
- It can generate database from your models.
What is Entity Framework Core?
- Entity Framework Core is the new version of Entity Framework after EF 6.x.
- It is open-source, lightweight, extensible and a cross-platform version of Entity Framework data access technology.
- Entity Framework Core is an ORM. It is an enhancement to ADO.NET that gives developers and automated mechanism for accessing & storing the data in the database.
- You can write your queries using LINQ as compared to SQL.
We have three types of approaches in Entity Framework
which are
- Code First Approach
- Model First Approach
- Database First Approach
Advantages of Entity Framework?
- Generate models based from database and vice versa.
- Saves time from respective tasks.
- More secure.
- Cross Platform
- No need to manage mapping manually.
- No need fro stored procedure, but you can still use if needed.
Tools:
Visual Studio 2022 with .NET 7
SQLServer Developer Version
SSMS (SQL Server Management Studio)
Nuget Packages:
Microsoft.EntityFrameworkCore - 7.0.0
Microsoft.EntityFrameworkCore.SqlServer - 7.0.0
Microsoft.EntityFrameworkCore.Tools - 7.0.0
Microsoft.EntityFrameworkCore.Design - 7.0.0
1.Code First Approach:
Code First is a technique that helps us to create a database, and migrate and maintain the database and its tables from the code.
From the code, means that you directly maintain the database and its corresponding tables from the .NET Code.
It is helpful when you don’t have a database ready and you want to start working with a new fresh project and want to create a database and maintain the database directly from your code.
Migration:
- When a data model change is introduced, the developer uses EF Core tools to add a corresponding migration describing the updates necessary to keep the database schema in sync. EF Core compares the current model against a snapshot of the old model to determine the differences, and generates migration source files; the files can be tracked in your project's source control like any other source file.
- Once a new migration has been generated, it can be applied to a database in various ways. EF Core records all applied migrations in a special history table, allowing it to know which migrations have been applied and which haven't.
Step 1: Create a Model
using System.ComponentModel.DataAnnotations;
namespace CoreAPI.Models
{
public class Departments
{
// When ever Id is declared in model it will treat as primary key
//otherwise we have decorate with the attribute [Key]
public int ID { get;set; }
// Since we already declared ID above
// It will automatically treat it as Primary Key and
// it will apply Identity to ID
// Below DeptID no need commenting it
//[Key]
//public int DeptID { get; set; }
public string? Name { get;set; }
public string? Location { get; set; }
}
}
namespace CoreAPI.Models
{
public class Employees
{
public int ID { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? Gender { get; set; }
public string? Salary { get; set; }
public int DepartmentId { get; set; }
}
}
Step 2 (Create a DbContext):
using CoreAPI.Models;
using Microsoft.EntityFrameworkCore;
namespace CoreAPI.Contexts
{
public class SampleDBContext:DbContext
{
public DbSet<Departments>? departments { get; set; }
public DbSet<Employees>? employees { get; set; }
protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer
(@"Data Source=.\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=True");
}
}
}
Step 3 (Add-Migration):
GoTo Package Manager Console and execute below command.
Syntax: Add-Migration <Name>
Example: Add-Migration InitialMigration
Step 4 (Update-Database):
GoTo Package Manager Console and execute below command.
Syntax: Update-Database <Name:Optional>
Example: Update-Database (OR) Update-Database InitialMigration
Migration-Snapshot:
Migration snapshot will be saved in a table in database, You can observe below image.
From code also it will maintain the history of model change. You can observe below screenshot
Removing Migration and Update-Database:
For example if we add new column to model that need to sync with db, We can use remove migration and update-database.
For example am changing salary data type in Employees model.
Step 1:
namespace CoreAPI.Models
{
public class Employees
{
public int ID { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? Gender { get; set; }
// Converting Salary to decimal
public decimal Salary { get; set; }
public int DepartmentId { get; set; }
}
}
Step 2:
Goto -> Package Manager Console and type below command
Add-Migration SalaryChangedToDecimalForEmployeesModel
Observations After Adding Migration:
You may observe below decimal precision is 18,2.
But i want it as 12,2, To make this happend we can add precision from dbcontext while Model Creating.
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace CoreAPI.Migrations
{
public partial class SalaryChangedToDecimalForEmployeesModel : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterColumn<decimal>(
name: "Salary",
table: "employees",
// You may observe below decimal precision is 18,2
// But i want it as 12,2
// To make this happend we can add precision from dbcontext
// while modelcreating
type: "decimal(18,2)",
nullable: false,
defaultValue: 0m,
oldClrType: typeof(string),
oldType: "nvarchar(max)",
oldNullable: true);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterColumn<string>(
name: "Salary",
table: "employees",
type: "nvarchar(max)",
nullable: true,
oldClrType: typeof(decimal),
oldType: "decimal(18,2)");
}
}
}
Add below code in DbContext:
using CoreAPI.Models;
using Microsoft.EntityFrameworkCore;
namespace CoreAPI.Contexts
{
public class SampleDBContext:DbContext
{
public DbSet<Departments>? departments { get; set; }
public DbSet<Employees>? employees { get; set; }
protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer
(@"Data Source=.\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=True");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employees>().Property(i => i.Salary).
HasPrecision(12, 2);
}
}
}
Step 3 (Remove-Migration):
GoTo Package Manager console and execute below command.
Remove-Migration
Step-4 (Update-Database):
GoTo Package Manager console and execute below command.
Update-Database
Similar way you can add a new column and Add-Migration and Update-Database.
Nullable:
If you observe below Name Field i removed '?' coalesing operator it will be treated as no-nullable column, Where as Location will be treated and Nullable.
using System.ComponentModel.DataAnnotations;
namespace CoreAPI.Models
{
public class Departments
{
// When ever Id is declared in model it will treat as primary key
//otherwise we have decorate with the attribute [Key]
public int ID { get;set; }
// Since we already declared ID above
// It will automatically treat it as Primary Key and it will apply Identity to ID
// Below DeptID no need commenting it
//[Key]
//public int DeptID { get; set; }
public string Name { get;set; } // Non-Nullable Column
public string? Location { get; set; }
}
}
Migration Scenarios
- Add a new class / table in the database.
- Add a new property / column to table.
- Modify existing property / column in a table.
- Delete existing property / column in a table.
- Delete a class / table in the database.
Rolling back old Migration
Goto package manager console and execute below command
Syntax: Update-Database <Migration-Name>
Example: Update-Database IsActiveAddedToDept
More Commands ...
get-migration - To display all migrations
drop-database - To Drop database
Seed Data From Migration (Inserting some data while Model Creating)
If we want to seed some initial data to tables we can declare them while model creating.
Below example will elaborate more
using CoreAPI.Models;
using Microsoft.EntityFrameworkCore;
namespace CoreAPI.Contexts
{
public class SampleDBContext : DbContext
{
public DbSet<Departments>? departments { get; set; }
public DbSet<Employees>? employees { get; set; }
public IConfiguration Configuration;
public SampleDBContext(IConfiguration configuration)
{
Configuration = configuration;
}
protected override void OnConfiguring
(DbContextOptionsBuilder optionsBuilder)
{
string? connectionString = Configuration.
GetConnectionString("SampleDb");
optionsBuilder.UseSqlServer(connectionString ?? "");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employees>().Property(i => i.Salary).
HasPrecision(12, 2);
modelBuilder.Entity<Departments>().HasData(new Departments
{
ID = 1,
IsActive = true,
Location = "Hyderabad",
Name = "IT"
});
modelBuilder.Entity<Employees>().HasData(new Employees
{
ID = 1,
FirstName = "Sam",
Address = "Hyderabad",
DepartmentId = 1,
Gender = "Male",
LastName = "S",
Salary = 10000.00m
});
}
}
}
add-migration SeedDataToDeptAndEmployee
update-database
Data Annotations:
[Table("<TableName>")] -> To declare the table at class level
[Column("<ColumnName>")] -> To declare the column name at property level
[Key] -> To declare the primary key at column level
[Required] -> To declare the non-nullable column at column level
[MaxLength(50)] -> To declare max length of column at column level
[NotMapped] -> To prevent creating the column in table
Those which ever not mapped may can be used for computation but not to create in database table.
[ForeignKey] -> To Create Foreign key relationship.
Filtering Query:
GetAllEmployees:
Below code get all the employees data.
using CoreAPI.Contexts;
using CoreAPI.Models;
using Microsoft.AspNetCore.Mvc;
namespace CoreAPI.Controllers
{
[ApiController]
[Route("[controller]")]
public class HomeController : Controller
{
public IConfiguration configuration { get; set; }
public HomeController(IConfiguration _configuration)
{
configuration = _configuration;
}
[HttpGet(Name = "GetAllEmployees")]
public IEnumerable<Employees> Get()
{
using var context = new SampleDBContext(configuration);
List<Employees>? employees = new List<Employees>();
if (context != null && context.employees != null)
{
employees = context.employees.ToList();
}
return employees;
}
}
}
Postman Output:
SaveEmployee:
using CoreAPI.Contexts;
using CoreAPI.Models;
using Microsoft.AspNetCore.Mvc;
namespace CoreAPI.Controllers
{
[ApiController]
[Route("[controller]")]
public class HomeController : Controller
{
public IConfiguration configuration { get; set; }
public HomeController(IConfiguration _configuration)
{
configuration = _configuration;
}
[HttpGet(Name = "GetAllEmployees")]
public IEnumerable<Employees> Get()
{
using var context = new SampleDBContext(configuration);
List<Employees>? employees = new List<Employees>();
if (context != null && context.employees != null)
{
employees = context.employees.ToList();
}
return employees;
}
[HttpPost]
public IActionResult Post([FromBody]Employees employee)
{
string Result = string.Empty;
using var context = new SampleDBContext(configuration);
if (context != null && context.employees != null)
{
var employees = context.employees.Add(employee);
context.SaveChanges();
Result = "Employee Added Successfully";
}
else
{
Result = "Unable to Add the Employee";
}
return Json(Result);
}
}
}
Postman Output:
Difference between First() and FirstOrDefault()
Both are used to get the first record from table.
When we use First() if there is no records in the table it will through an exception as Sequence contain no elements.
Where as when we use FirstOrDefault() it will not throw exception but it returns null.
[HttpGet]
public Employees Get()
{
using var context = new SampleDBContext(configuration);
//List<Employees>? employees = new List<Employees>();
Employees? employees = new Employees();
if (context != null && context.employees != null)
{
employees = context.employees.Where(p => p.DepartmentId == 1).FirstOrDefault();
}
return employees;
}
Find():
Find is used to get the data with Primary key.
So using Find() we can retrieve the result faster.
[HttpGet(Name = "GetEmployeeWithId")]
[Route("~/Home/GetEmployeeWithId")]
public Employees GetEmployeeWithId([FromBody] Employees emp)
{
using var context = new SampleDBContext(configuration);
Employees? result = new Employees();
if (context != null && context.employees != null)
{
// Find is Used to Get the data with Primary Key
result = context.employees.Find(emp.ID);
}
return result;
}
Output:
Single() and SingleOrDefault()
Single will always return one element
In employee Table we may have multiple employees with same department
So, Single should also need to use always with Primary key column
Difference between Single() and SingleOrDefault() is: When we use single if there are no records in table it will throw an exception.
Where as SingleOrDefault will not throw an exception.
[HttpGet(Name = "GetEmployeeWithId")]
[Route("~/Home/GetEmployeeWithId")]
public Employees GetEmployeeWithId([FromBody] Employees emp)
{
using var context = new SampleDBContext(configuration);
Employees? result = new Employees();
if (context != null && context.employees != null)
{
// Single will always return one element
// In employee Table we may have multiple employees with same department
// So, Single should also need to use always with Primary key column
// result = context.employees.Single(p => p.DepartmentId == emp.DepartmentId);
// ID is primary key so below statement will not throw any error
result = context.employees.Single(p => p.ID == emp.ID);
}
return result;
}
Contains, Like, Aggregation:
Below example will show Contains, Like, Aggregate functions.
[HttpGet(Name = "GetAllEmployees")]
public IEnumerable<Employees> Get()
{
using var context = new SampleDBContext(configuration);
List<Employees>? employees = new List<Employees>();
if (context != null && context.employees != null)
{
// Contains Function
employees = context.employees.Where(p=>p.FirstName.Contains("s")).ToList();
// Like Function
employees = context.employees.Where(p => EF.Functions.Like(p.FirstName,"%s%")).ToList();
// Aggregate Functions
// Get Count
var count = context.employees.Count();
// Get Max Salary
var max = context.employees.Max(p => p.Salary);
// Get Min Salary
var min = context.employees.Min(p => p.Salary);
}
return employees;
}
Sort:
[HttpGet(Name = "GetAllEmployees")]
public IEnumerable<Employees> Get()
{
using var context = new SampleDBContext(configuration);
List<Employees>? employees = new List<Employees>();
if (context != null && context.employees != null)
{
// Order by Asc
employees = context.employees.OrderBy(p => p.FirstName).ToList();
// Order by Desc
employees = context.employees.OrderByDescending(p => p.FirstName).ToList();
//
employees = context.employees.OrderBy(p => p.FirstName).OrderByDescending(p => p.ID).ToList();
}
return employees;
}
Pagination:
[HttpGet(Name = "GetAllEmployees")]
public IEnumerable<Employees> Get()
{
using var context = new SampleDBContext(configuration);
List<Employees>? employees = new List<Employees>();
if (context != null && context.employees != null)
{
// Pagination with Skip
employees = context.employees.Skip(0).Take(2).ToList();
//
employees = context.employees.Skip(2).Take(3).ToList();
}
return employees;
}
Updating Records:
Find() function is used to read with primary key.
and then updating the values.
Also we can update multiple records by selecting the table with where condition.
Show examples below.
[HttpPut]
public IActionResult Put([FromBody] Employees employee)
{
string Result = string.Empty;
using var context = new SampleDBContext(configuration);
if (context != null && context.employees != null)
{
var employeeCntx = context.employees.Find(employee.ID);
employeeCntx.FirstName = employee.FirstName;
context.SaveChanges();
Result = "Employee Id " + employee.ID + " is Updated Successfully";
}
else
{
Result = "Unable to update the Employee";
}
return Json(Result);
}
var employeeCntx = context.employees.Where(p=>p.Address.Equals("Hyderabad"));
// Updating Multiple Rows
foreach(var emp in employeeCntx)
{
emp.DepartmentId = 2;
}
context.SaveChanges();
Postman Output:
Deleting Records:
[HttpDelete]
public IActionResult Delete([FromBody] Employees employee)
{
string Result = string.Empty;
using var context = new SampleDBContext(configuration);
if (context != null && context.employees != null)
{
var empCntx = context.employees.Find(employee.ID);
context.employees.Remove(empCntx);
context.SaveChanges();
Result = "Employee Id " + employee.ID + " is deleted Successfully";
}
else
{
Result = "Unable to delete the Employee";
}
return Json(Result);
}
