Entity Framework core introduction

Hendi Suhardja
14 min readJul 2, 2020

Entity Framework Core can serve as an object-relational mapper (ORM), enabling.NET developers to work with a database using .NET objects, and eliminating the need for most of the data-access code they usually need to do CRUD operation.

Prerequisites

If you install .NET Core 3 or 3.1, you need to run below command on CMD to install EF Core

dotnet tool install --global dotnet-ef --version 3.1.0

Creating Angular Web Application

  • Open Visual Studio Community, click on “Create a new project”
  • Choose “ASP.NET Core Web Application”
  • Choose your project directory and solution name, then click “Create”
  • Choose Angular template, then click “Create”
  • Test your application by opening command prompt, navigate to your project directory then type below command
dotnet run

or if you want to auto compile it everytime you change your code, you could use below

dotnet watch run
  • Navigate to localhost:5001 on your browser

Create DBContext and Migration Files using EF Core

  • Open Visual Studio, then open Nuget Package Manager, and install these packages :

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.SqlServer

  • Open appsettings.json, add your connection strings
"ConnectionStrings": {"DbConnectionString": "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestAngularDB;Data Source=DESKTOP-PC;"}
  • Create new folder Extensions, add new interface “IQueryObject”
namespace AngularEFSQL.Extensions{public interface IQueryObject{string SortBy { get; set; }bool IsSortAscending { get; set; }int Page { get; set; }int PageSize { get; set; }}}
  • Create new folder “Data”, then inside folder “Data”, create new class “Invoice” and new class “Customer”
using AngularEFSQL.Extensions;using System.Collections.Generic;using System.ComponentModel.DataAnnotations;namespace AngularEFSQL.Data{public class Customer{[Key]public int CustomerId { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string Address { get; set; }public virtual List<Invoice> Invoices { get; set; }}public class CustomerQuery : IQueryObject{public int? CustomerId { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string Address { get; set; }public string SortBy { get; set; }public bool IsSortAscending { get; set; }public int Page { get; set; }public int PageSize { get; set; }}}using System;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;namespace AngularEFSQL.Data{public class Invoice{[Key]public int Id { get; set; }public DateTime Date { get; set; }public int CustomerId { get; set; }[ForeignKey("CustomerId")]public virtual Customer Customer { get; set; }}}
  • Then create new folder “Persistence”, create new class “SQLDBContext”, copy below code
using AngularEFSQL.Data;using Microsoft.EntityFrameworkCore;namespace AngularEFSQL.Persistence{public class SQLDBContext : DbContext{public DbSet<Customer> Customers { get; set; }public DbSet<Invoice> Invoices { get; set; }public SQLDBContext(){}public SQLDBContext(DbContextOptions<SQLDBContext> options) : base(options){}}}
  • Open Startup.cs, copy below code inside method ConfigureServices
services.AddDbContext<SQLDBContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DbConnectionString")));
  • Now we will create migration files, open your command prompt, navigate to your project directory, then run below command
dotnet ef migrations add "InitFiles"

Note : make sure you have stop your dotnet run process, if not Build will be failed

Now go back to Visual studio, you could see there will be new folder called “Migrations”, open InitFiles.cs, you could see there will be code for generating your database tables

  • Go back to CMD, run below command
dotnet ef database update

Now check on you SQL Management Studio, your database and table will be generated now

Create Repository and Unit of Work

  • On folder Extensions, add new class “IQueryableExtensions”
using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;namespace AngularEFSQL.Extensions{public static class IQueryableExtensions{public static IQueryable<T> ApplyOrdering<T>(this IQueryable<T> query, IQueryObject queryObj, Dictionary<string, Expression<Func<T, object>>> columnsMap){if (String.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))return query;if (queryObj.IsSortAscending)return query.OrderBy(columnsMap[queryObj.SortBy]).AsQueryable();elsereturn query.OrderByDescending(columnsMap[queryObj.SortBy]).AsQueryable();}public static IQueryable<T> ApplyPaging<T>(this IQueryable<T> query, IQueryObject queryObj){if (queryObj.PageSize <= 0)queryObj.PageSize = 10;if (queryObj.Page <= 0)queryObj.Page = 1;return query.Skip((queryObj.Page - 1) * queryObj.PageSize).Take(queryObj.PageSize);}}}
  • On folder “Dto”, create new class “QueryResultDto”
using System.Collections.Generic;namespace AngularEFSQL.Dto{public class QueryResultDto<T>{public int TotalItems { get; set; }public IEnumerable<T> Items { get; set; }}}

Unit of Work is the concept related to the effective implementation of the Repository Pattern. Unit of Work is referred to as a single transaction that involves multiple operations of insert/update/delete and so on kinds. To say it in simple words, all the transactions like insert/update/delete and so on are done in one single transaction, rather then doing multiple database transactions. This means, one unit of work here involves insert/update/delete operations, all in one single transaction.

  • Inside folder “Persistence”, create folder “Interface”, add new interface “IUnitOfWork”, add below code
using System.Threading.Tasks;namespace AngularEFSQL.Persistence.Interface{public interface IUnitOfWork{Task CompleteAsync();}}
  • Inside folder Interface, create new interface “ICustomerRepository.cs”, then add below code
using AngularEFSQL.Data;using AngularEFSQL.Dto;using System.Threading.Tasks;namespace AngularEFSQL.Persistence.Interface{public interface ICustomerRepository{Task Add(Customer customer);Task<Customer> GetCustomer(int id, bool includeRelated = true);void Remove(Customer customer);Task<QueryResultDto<Customer>> GetCustomers(CustomerQuery filter);}}
  • Now inside folder Persistence, create new class “UnitOfWork.cs”
using AngularEFSQL.Persistence.Interface;using System.Threading.Tasks;namespace AngularEFSQL.Persistence{public class UnitOfWork : IUnitOfWork{private readonly SQLDBContext context;public UnitOfWork(SQLDBContext context){this.context = context;}public async Task CompleteAsync(){await context.SaveChangesAsync();}}}
  • Inside folder Persistence, add new class “CustomerRepository”, add below code
using AngularEFSQL.Data;using AngularEFSQL.Dto;using AngularEFSQL.Extensions;using AngularEFSQL.Persistence.Interface;using Microsoft.EntityFrameworkCore;using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;using System.Threading.Tasks;namespace AngularEFSQL.Persistence{public class CustomerRepository : ICustomerRepository{private readonly SQLDBContext context;public CustomerRepository(SQLDBContext context){this.context = context;}public async Task<Customer> GetCustomer(int id, bool includeRelated = true){if (!includeRelated)return await context.Customers.FindAsync(id);var customer = await context.Customers.Include(x => x.Invoices).SingleOrDefaultAsync(x => x.CustomerId == id);return customer;}public async Task Add(Customer customer){await context.Customers.AddAsync(customer);}public void Remove(Customer customer){context.Customers.Remove(customer);}public async Task<QueryResultDto<Customer>> GetCustomers(CustomerQuery queryObj){var result = new QueryResultDto<Customer>();var query = context.Customers.Include(x => x.Invoices).AsQueryable();query = ApplyFiltering(queryObj, query);var columnsMap = new Dictionary<string, Expression<Func<Customer, object>>>{["address"] = v => v.Address,["customerId"] = v => v.CustomerId,["firstName"] = v => v.FirstName,["lastName"] = v => v.LastName};query = query.ApplyOrdering(queryObj, columnsMap);result.TotalItems = await query.CountAsync();query = query.ApplyPaging(queryObj);result.Items = await query.ToListAsync();return result;}private IQueryable<Customer> ApplyFiltering(CustomerQuery queryObj, IQueryable<Customer> query){if (!string.IsNullOrEmpty(queryObj.Address))query = query.Where(v => v.Address.Trim().ToLower().Contains(queryObj.Address.Trim().ToLower()));if (queryObj.CustomerId.HasValue)query = query.Where(v => v.CustomerId == queryObj.CustomerId.Value);if (!string.IsNullOrEmpty(queryObj.FirstName))query = query.Where(v => v.FirstName.Trim().ToLower().Contains(queryObj.FirstName.Trim().ToLower()));if (!string.IsNullOrEmpty(queryObj.LastName))query = query.Where(v => v.LastName.Trim().ToLower().Contains(queryObj.LastName.Trim().ToLower()));return query;}}}
  • Open Startup.cs, inside method “ConfigureServices”, add below codes
services.AddScoped<ICustomerRepository, CustomerRepository>();services.AddScoped<IUnitOfWork, UnitOfWork>();

Now your repository is ready, now let’s create the Web API

Web API — Create Customer API

  • On Folder “Dto”, create new class “SaveCustomerDto” , “CreateInvoiceDto”, “CustomerDto” and “InvoiceDto”
using System.Collections.Generic;using System.ComponentModel.DataAnnotations;namespace AngularEFSQL.Dto{public class SaveCustomerDto{[Required]public string FirstName { get; set; }public string LastName { get; set; }[Required]public string Address { get; set; }public ICollection<CreateInvoiceDto> Invoices { get; set; }}}using System;namespace AngularEFSQL.Dto{public class CreateInvoiceDto{public DateTime Date { get; set; }}}using System.Collections.Generic;namespace AngularEFSQL.Dto{public class CustomerDto{public int CustomerId { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string Address { get; set; }public List<InvoiceDto> Invoices { get; set; }}}using System;namespace AngularEFSQL.Dto{public class InvoiceDto{public int Id { get; set; }public DateTime Date { get; set; }}}
  • Open Nuget Package Manager, install “AutoMapper” and “AutoMapper.Extensions.Microsoft.DependencyInjection”
  • Add new Folder “Common” on the project, add new class called “MappingProfile.cs”, put below code
using AngularEFSQL.Data;using AngularEFSQL.Dto;using AutoMapper;using System.Linq;namespace AngularEFSQL.Common{public class MappingProfile : Profile{public MappingProfile(){CreateMap<Customer, CustomerDto>().ForMember(vr => vr.Invoices, opt => opt.MapFrom(v => v.Invoices.Select(vf => new InvoiceDto { Id =  vf.Id, Date = vf.Date })));CreateMap<SaveCustomerDto, Customer>().ForMember(v => v.CustomerId, opt => opt.Ignore()).ForMember(vr => vr.Invoices, opt => opt.MapFrom(v => v.Invoices.Select(vf => new Invoice { Date = vf.Date })));}}}
  • Open Startup.cs, add below code inside method ConfigureServices
services.AddAutoMapper(typeof(Startup));
  • On Controller folder, add new controller called “CustomersController”, add below codes
using AngularEFSQL.Data;using AngularEFSQL.Dto;using AngularEFSQL.Persistence.Interface;using AutoMapper;using Microsoft.AspNetCore.Mvc;using System.Threading.Tasks;namespace AngularEFSQL.Controllers{[Route("/api/customers")]public class CustomersController : Controller{private readonly IUnitOfWork _unitOfWork;private readonly ICustomerRepository _customerRepository;private readonly IMapper _mapper;public CustomersController(IUnitOfWork unitOfWork, ICustomerRepository customerRepository, IMapper mapper){_unitOfWork = unitOfWork;_customerRepository = customerRepository;_mapper = mapper;}[HttpPost]public async Task<IActionResult> CreateCustomer([FromBody] SaveCustomerDto saveCustomerDto){//throw new Exception();if (!ModelState.IsValid)return BadRequest(ModelState);var customer = _mapper.Map<SaveCustomerDto, Customer>(saveCustomerDto);await _customerRepository.Add(customer);await _unitOfWork.CompleteAsync();customer = await _customerRepository.GetCustomer(customer.CustomerId);var result = _mapper.Map<Customer, CustomerDto>(customer);return Ok(result);}}}

Add Swagger

  • Open Nuget Package Manager, install “Swashbuckle.AspNetCore”
  • Open Startup.cs, add below code inside method ConfigureServices
services.AddSwaggerGen();
  • Inside method “Configure”, add below codes
app.UseSwagger();app.UseSwaggerUI(c =>{c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");});
  • Test it by running your application, open CMD, execute below command
dotnet run
  • Test your API by clicking Execute
  • You could check your database too, you could see the records are inserted to DB

Create Update, Delete and Get Customer API

  • Inside folder “Dto” add new class “CustomerQueryDto”, add below codes
namespace AngularEFSQL.Dto{public class CustomerQueryDto{public int? CustomerId { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string Address { get; set; }public string SortBy { get; set; }public bool IsSortAscending { get; set; }public int Page { get; set; }public int PageSize { get; set; }}}
  • Open MappingProfile.cs, add below code inside constructor
CreateMap<CustomerQueryDto, CustomerQuery>();CreateMap(typeof(QueryResultDto<>), typeof(QueryResultDto<>));
  • Open CustomersController.cs, add below code
[HttpPut("{id}")]public async Task<IActionResult> UpdateCustomer(int id, [FromBody] SaveCustomerDto saveCustomerDto){if (!ModelState.IsValid)return BadRequest(ModelState);var customer = await _customerRepository.GetCustomer(id);if (customer == null){ModelState.AddModelError("CustomerId", "Invalid Customer Id.");return BadRequest(ModelState);}_mapper.Map<SaveCustomerDto, Customer>(saveCustomerDto, customer);await _unitOfWork.CompleteAsync();customer = await _customerRepository.GetCustomer(id);var result = _mapper.Map<Customer, CustomerDto>(customer);return Ok(result);}[HttpDelete("{id}")]public async Task<IActionResult> DeleteCustomer(int id){var customer = await _customerRepository.GetCustomer(id, includeRelated: false);if (customer == null)return NotFound();_customerRepository.Remove(customer);await _unitOfWork.CompleteAsync();return Ok(id);}[HttpGet("{id}")]public async Task<IActionResult> GetCustomer(int id){var customer = await _customerRepository.GetCustomer(id);if (customer == null)return NotFound();var result = _mapper.Map<Customer, CustomerDto>(customer);return Ok(result);}[HttpGet]public async Task<QueryResultDto<CustomerDto>> GetCustomers(CustomerQueryDto filterDto){var filter = _mapper.Map<CustomerQueryDto, CustomerQuery>(filterDto);var customers = await _customerRepository.GetCustomers(filter);return _mapper.Map<QueryResultDto<Customer>, QueryResultDto<CustomerDto>>(customers);}

Create Angular UI

On this section I use Visual Code, because with Visual Code, we could install some Angular extension to make our development easier. Below my Angular extension

Create Customer and Invoice Model

  • Open your angular code on folder “ClientApp”, inside src -> app directory, create new directory “models”
  • Inside folder “models”, create new ts file called “invoice.ts”, then put below codes
export interface Invoice {id: number;date: Date;}
  • Inside folder “models”, create new ts file called “customer.ts”, then put below codes
import { Invoice } from './invoice';export interface Customer {customerId: number;firstName: string;lastName: string;address: string;invoices: Invoice[];}
  • Inside folder “models”, create new ts file called “invoice-save.ts”, then put below codes
export interface SaveInvoice {date: Date;}
  • Inside folder “models”, create new ts file called “customer-save.ts”, then put below codes
import { SaveInvoice } from './invoice-save';export interface Customer {firstName: string;lastName: string;address: string;invoices: SaveInvoice[];}

Create Customer Service

  • Then inside src -> app directory, create new directory “services”
  • Add customer service by right click on services folder, then choose generate service, then type customer

It will create customer.service.ts

Then put below codes on customer.service.ts

import { Injectable } from '@angular/core';import { HttpClient } from '@angular/common/http';import { map, catchError } from 'rxjs/operators';import { throwError, Observable } from 'rxjs';import { SaveCustomer } from '../models/customer-save';import { Customer } from '../models/customer';@Injectable({providedIn: 'root'})export class CustomerService {private readonly customersEndpoint = '/api/customers';constructor(private http: HttpClient) { }create(customer: SaveCustomer) {return this.http.post(this.customersEndpoint, customer).pipe(map(response => {return response;}),catchError(err => {return throwError(err);}));}update(customer: SaveCustomer){return this.http.put(this.customersEndpoint + '/' + customer.id, customer).pipe(map(response => {return response;}),catchError(err => {return throwError(err);}));}delete(id){return this.http.delete(this.customersEndpoint + '/' + id).pipe(map(response => {return response;}),catchError(err => {return throwError(err);}));}getCustomer(id): Observable<Customer> {return this.http.get<Customer>(this.customersEndpoint + '/' + id, { observe: 'response'}).pipe(map(response => {return response.body;}),catchError(err => {return throwError(err);}));}getCustomers(filter): Observable<Customer[]> {return this.http.get<Customer[]>(this.customersEndpoint + '?' + this.toQueryString(filter), { observe: 'response'}).pipe(map(response => {return response.body;}),catchError(err => {return throwError(err);}));}toQueryString(obj){var parts = [];for(var property in obj){var value = obj[property];if(value != null && value != undefined){parts.push(encodeURIComponent(property) + '=' + encodeURIComponent(value));}}return parts.join('&');}}

Create View Customer UI

  • On directory “app”, create new folder “customer”, then right click on customer directory, then choose “Generate Component”, give name “customer-view”

It will generate customer-view

  • On “app.module.ts” add “CustomerViewComponent” on declarations section
  • On “app.module.ts” add new route for Customer View inside RouterModule
{ path: 'customer', component: CustomerViewComponent },
  • On nav-menu folder, navigate to “nav-menu.component.html”, add new menu that will navigate to Customer View
<li class="nav-item" [routerLinkActive]="['link-active']"><a class="nav-link text-dark" [routerLink]="['/customer']">Customer</a></li>
  • Test Customer View by running your solution
  • Now navigate to customer-view folder, open “customer-view.component.html”, then put below codes
<h2>Customers</h2><p><a [routerLink]="['/customer/new']"  class="btn btn-primary">New</a></p><div class="card card-body bg-light"><div class="form-group"><label for="make">First Name</label><input type="text" id="firstName" class="form-control" [(ngModel)]="query.firstName" (change)="onFilterChange()" /></div><button class="btn btn-primary col-md-3" (click)="resetFilter()">Reset</button></div><table class="table"><thead><tr><th *ngFor="let c of this.columns" ><div *ngIf="c.isSortable" (click)="sortBy(c.key)">{{ c.title }}<i *ngIf="query.sortBy === c.key"class="fa"[class.fa-sort-asc]="query.isSortAscending"[class.fa-sort-desc]="!query.isSortAscending"></i></div><div *ngIf="!c.isSortable">{{ c.title }}</div></th></tr></thead><tbody><tr *ngFor="let c of queryResult.items"><td>{{ c.customerId }}</td><td>{{ c.firstName }}</td><td>{{ c.lastName }}</td><td>{{ c.address}}</td><td><a [routerLink]="['/customer/', c.customerId]">Edit</a></td></tr></tbody></table>
  • Open “customer-view.component.ts”, put below codes
import { Component, OnInit } from '@angular/core';import { CustomerService } from 'src/app/services/customer.service';@Component({selector: 'app-customer-view',templateUrl: './customer-view.component.html',styleUrls: ['./customer-view.component.css']})export class CustomerViewComponent implements OnInit {private readonly PAGE_SIZE = 10;queryResult: any = {};query:any = {pageSize: this.PAGE_SIZE};columns = [{ title: 'Id' },{ title: 'First Name', key: 'firstName', isSortable:true },{ title: 'Last Name', key: 'lastName', isSortable:true },{ title: 'Address', key: 'address', isSortable:true },{}];constructor(private customerService: CustomerService) { }ngOnInit() {this.populateCustomers();}onFilterChange(){this.query.page = 1;this.populateCustomers();}private populateCustomers(){this.customerService.getCustomers(this.query).subscribe(results =>{ this.queryResult = results;});}resetFilter(){this.query ={page:1,pageSize:this.PAGE_SIZE};this.populateCustomers();}sortBy(columnName){if(this.query.sortBy !== columnName){this.query.sortBy = columnName;}this.query.isSortAscending = !this.query.isSortAscending;this.populateCustomers();}}
  • Run your solution to test Customer View

Create Add and Edit Customer UI

  • On app -> customer directory, add new component “customer-form” by right clicking customer directory and choose Generate Component and give name “customer-form”
  • On app.module.ts, add “CustomerFormComponent” on declarations section, and add new route
{ path: 'customer/new', component: CustomerFormComponent },{ path: 'customer/:id', component: CustomerFormComponent },
  • Navigate to “customer-form.component.ts”, put below codes
import { Component, OnInit } from '@angular/core';import { SaveCustomer } from 'src/app/models/customer-save';import { ActivatedRoute, Router } from '@angular/router';import { CustomerService } from 'src/app/services/customer.service';import { Customer } from 'src/app/models/customer';import { Observable } from 'rxjs';@Component({selector: 'app-customer-form',templateUrl: './customer-form.component.html',styleUrls: ['./customer-form.component.css']})export class CustomerFormComponent implements OnInit {customer: SaveCustomer = {id:0,address:'',firstName:'',lastName:'',invoices:[]};constructor(private route: ActivatedRoute,private router:Router,private customerService:CustomerService) {route.params.subscribe(p =>{this.customer.id = +p['id'] || 0;});}ngOnInit() {if(this.customer.id)this.customerService.getCustomer(this.customer.id).subscribe(data => {if(this.customer.id){this.setCustomer(data);}}, err =>{if(err.status == 404){this.router.navigate(['/']);}});}private setCustomer(c: Customer){this.customer.id = c.customerId;this.customer.firstName = c.firstName;this.customer.lastName = c.lastName;this.customer.address = c.address;this.customer.invoices = [];}submit() {var result$ = (this.customer.id) ? this.customerService.update(this.customer) : this.customerService.create(this.customer);result$.subscribe(customer => {alert('Data was successfully saved.');this.router.navigate(['/customer']);});}delete(){if(confirm("Are you sure?")){this.customerService.delete(this.customer.id).subscribe(x =>{this.router.navigate(["/customer"]);});}}}
  • Navigate to “customer-form.component.html”, put below codes
<h1>Customer Form</h1><form #f="ngForm" (ngSubmit)="submit()" novalidate><div class="form-group"><label for="firstName">First Name</label><input id="firstName" type="text" class="form-control" [(ngModel)]="customer.firstName"name="firstName" #firstName="ngModel" required><div class="alert alert-danger" *ngIf="firstName.touched && !firstName.valid">Please specify First Name</div></div><div class="form-group"><label for="lastName">Last Name</label><input id="lastName" type="text" class="form-control" [(ngModel)]="customer.lastName"name="lastName" #lastName="ngModel" required><div class="alert alert-danger" *ngIf="lastName.touched && !lastName.valid">Please specify Last Name</div></div><div class="form-group"><label for="address">Address</label><input id="address" type="text" class="form-control" [(ngModel)]="customer.address"name="address" #address="ngModel" required><div class="alert alert-danger" *ngIf="address.touched && !address.valid">Please specify Address</div></div><button class="btn btn-primary" [disabled]="!f.valid">Save</button><button class="btn btn-danger" *ngIf="this.customer.id" type="button" (click)="delete()">Delete</button></form>
  • Run your application, now you could add, update and delete customer

You could find complete source code on Github. Thanks for reading

--

--

No responses yet