.net is a free cross-platform, open source developer platform to help software developers develop different types of applications. Using .net provides developers the ability to use a variety of different languages, editors and libraries to develop applicationsfd for web, mobile, desktop, gaming and IoT.
Entity Framework Core is an Object Relational Mapper (ORM) for .net Core projects, supported by Microsoft, enabling developers to work with data using objects of domain specific classes without focusing on the underlying database tables and columns where the data is stored. Using Entity Framework developers work at a higher level of abstraction which helps to create and maintain data oriented applications with less code and without having to work directly within in the Database platform.
Entity Framework Features
- Cross-platform: EF Core is a cross-platform framework which can run on Windows, Linux and Mac
- Modelling:Entity Framework creates POCO (Plain Old CLR Object) entities using get/set properties of different data types.
- Change Tracking:tracks of changes occurred to instances of entities (Property values) which need to be submitted to the database.
- Concurrency:Optimistic Concurrency by default to protect overwriting changes made by another user since data was fetched from the database.
- Transactions:automatic transaction management while querying or saving data, with options to further customise transaction management.
- Caching:First level of caching out of the box. Repeated querys will return data from the cache instead of hitting the database
- Configurations:Configure EF model by using data annotation attributes or Fluent API to override default conventions.
- Migrations:Set of migration commands that can be executed on the NuGet Package Manager Console or the Command Line Interface to create or manage underlying database Schema.
In this post we will walk you through the process of running creating a simple EF Core Database, with Migrations with Entity Model and Database Contexts in separate class libraries in a .net core Web Api project.
The Web API itself will be responsible for maintaining a list of known Referrer Spammers.
Often developers will aim to ensure their Database layer is entirely separate from business logic. In a lot of cases, they will also like to have their Database Entities and Domain entities as separate entities preferring to Map between these and only use Domain entities in the within their business logic.
Using an ORM, you should ensure to make your model objects as simple as possible. Be more vigilant about simplicity to make sure your model objects really are just Plain Old Data Objects (PODO), otherwise you may end up wrestling with your ORM to make sure the persistence works like you expect it to, and its not looking for methods and properties that aren't actually there.
The Benefits of using ORM's include:
- Facilitates implementing domain model pattern.
- A huge reduction in code.
- Takes care of vendor-specific code by itself.
- Cache Management - Entities are cached in memory thereby reducing the
load on the DB.
To further abstract your Database logic from your business logic you may want to implement what is known as the Generic Repository Pattern and a Service Layer pattern this will usually help to ensure that no database logic or interference bleeds into the business logic of your application.
Updates in Progress
I am currently in the process of updating this post to detail the steps to include some new handy features in EF Core and also how to implement this project using the API Template Pack
Create Database Class Library Project
We make use of solution and projects files in the source code, so if you are not familiar with working with solution files using .net core you may want to check out my post -Creating and Editing Solutions files with .net core CLI.
I'll add a new class library project, primary purpose of this project is to create Database Context object which well share across a couple of projects. Eventually for this particular project, we are going to create Database context for three of the main database providers. i.e. Postgres, Mysql and MS SQL.
We will mainly be dealing with the PostgreSQL context in this article. The primary reason why we will be creating separate projects for each of the anticipated contexts, is that there are a number of quirks of each Relational Database system that will need to catered for in each of the different contexts. Check out Why use PostgreSQL for your database
Although it is true, that EF core in theory enables you to connect and interact with many different data sources, you still need to cater for the slight quirks of the different databases within the DbContexts. I will walk-through some of these within this article.
dotnet new classlib -n Database
Rename the generated class to DiogelContext.cs
We'll also add a reference Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore
In the case and purpose we going to use PostgreSQL
So let's go ahead and add a reference to those packages too.
dotnet add package Npgsqldotnet add package Npgsql.EntityFrameworkCore.PostgreSQLdotnet add package Microsoft.EntityFrameworkCore.Designdotnet add package Microsoft.EntityFrameworkCore.Relationaldotnet add package Microsoft.Extensions.Configuration.FileExtensionsdotnet add package Microsoft.Extensions.Configuration.Json
We can now create a barebones Database Context.
using Microsoft.EntityFrameworkCore;namespace Database{ public class DiogelContext : DbContext { public DiogelContext(DbContextOptions<DiogelContext> options) : base(options) { } }}
Advice
The steps above detail the Manual process of setting up a new project. The API template Pack will generate a new API solution with all the configuration done and a new project ready for you to start and concentrate on your development using dotnet new apisolution -n <name of your project> -- Root <company or project code name>
Database Entities
We'll now create a new project which we'll use to create a Database Entity objects we'll call it rather unimaginatively Models
. The primary reason why we will be creating this as a totally separate project is to enable the referencing of entities across our various projects and Context Projects.
For the sake of example, We'll create 3 additional classes:
Status.cs
Threat.cs
Type.cs
Classification.cs
public class Status : BaseEntity{ public Status() { } public string Name { get; set; } public string Description { get; set; } public virtual ICollection<Threat> Threats { get; set; }}public class ThreatType : BaseEntity{ public Type() { } public string Name { get; set; } public virtual ICollection<Threat> Threats { get; set; }}public class Threat : BaseEntity{ public string Referer { get; set; } public string Host { get; set; } public string UserAgent { get; set; } public string XForwardHost { get; set; } public string XForwardProto { get; set; } public string QueryString { get; set; } public string Protocol { get; set; } public Guid TypeId { get; set; } public Guid StatusId { get; set; } public virtual Type Type { get; set; } public virtual Status Status { get; set; }}
The API Template Pack provides a handy Base Class for Entities, BaseEntity
that will ensure all entity classes use some common properties. i.e. ID, Created Date, Modified Date and Active property we will discuss these in more detail later.
Principles of Web API
Delivering Value with APIs and Microservices
Principles of Web API Designbrings together principles and processes to help you succeed across the entire API design lifecycle.
Read Review
How to create a Database Context
DbContext is an important class in Entity Framework API. It is a bridge between your domain or entity classes and the database.
- The primary class that is responsible for interacting with data as objects DbContext.
- DbContext APIs simplify your application interaction with the database.
- Reduces the number of methods and properties required to access commonly used tasks.
The Entity Framework API's are not released as part of the .net core framework and an additional step if required to install them locally on your machine, if you would like to use them for either Code-First or Database-First Development. To install the EF Core APi's globally - you can use them across any project -simply execute the following command in your terminal window:
dotnet tool install --global dotnet-ef
We are now ready to edit our previously created DiogelContext.cs
and class library project to include a reference to our Database
project.
using Models.Threats;using Models.Configuration;using Microsoft.EntityFrameworkCore;namespace Database{ public class DiogelContext : DbContext { public DiogelContext(DbContextOptions<DiogelContext> options) : base(options) { } public DbSet<Threat> Threats { get; set; } public DbSet<Type> Type { get; set; } public DbSet<Status> Status { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { } }}
How to define Entity Configuration
Earlier in this post, I pointed out that I prefer to keep my entities as simple POCO classes and I don't use the Component Annotations to define the database properties. My preference for this is to make use of IEntityTypeConfiguration
, this is based on ensuring the Separation of Concerns principle and decoupling the persistence layer from the domain model logic. Principles defined in Eric Evans Domain-Driven Design: Tackling Complexity in the Heart of Software
Domain Driven Design
Tackling Complexity in the Heart of Software
A fantastic book on how you can make the design of your software match your mental model of the problem domain you are addressing
Read Review
IEntityTypeConfiguration
enables configuration for an entity type to be factored into a separate class, rather than in-line inOnModelCreating
. Implement this interface, applying configuration for the entity in theConfigure
method, and then apply the configuration to the model usingApplyConfiguration<TEntity>T
inOnModelCreating
.
Personally I have a strong preference for making use of the Entity Type Fluent Configuration for Entities over the Attribute based approach. In my experience the Attribute based approach only really works for simple database like, if you building that great world beating To Do List app or some such, but the minute you need to develop anything vastly complex like your average enterprise application or Microservice its completely useless and very difficult to map complex object relationships etc.
Taking the time to learn the Entity Type Configuration will pay huge dividends and save on frustration later. Microsoft themselves often highlight the benefits of using the Fluent Configuration, so read into that what you will.
In our Entity Type Configuration we'll make use of another API Template Pack, the BaseEntityTypeConfiguration
Class which will take care of registering the common properties and attributes of your classes enabling you to focus on the properties you need to add.
using Api.Database.Entity.Threats;using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Metadata.Builders;namespace Database.Configurations{ internal class ThreatConfiguration : BaseEntityTypeConfiguration<Threat> { public override void Configure(EntityTypeBuilder<Threat> builder) { builder.ToTable(nameof(Threat).ToLower()); builder.Property(x => x.Host) .HasColumnName("host") .HasColumnType("varchar") .HasMaxLength(41); builder.Property(x => x.Referer) .HasColumnName("referrer") .HasColumnType("varchar") .HasMaxLength(256); builder.Property(x => x.QueryString) .HasColumnName("query_string") .HasMaxLength(256) .HasColumnType("varchar"); builder.Property(x => x.XForwardHost) .HasColumnName("x_forward_host") .HasMaxLength(256) .HasColumnType("varchar"); builder.Property(x => x.XForwardProto) .HasColumnName("x_forward_proto") .HasMaxLength(256) .HasColumnType("varchar"); builder.Property(kt => kt.Protocol) .HasColumnName("protocol") .HasMaxLength(256) .HasColumnType("varchar"); builder.HasOne(x => x.Status) .WithMany(t => t.Threats) .HasForeignKey(dt => dt.StatusId); builder.HasOne(d => d.ThreatType) .WithMany(t => t.Threats) .HasForeignKey(dt => dt.TypeId); } }}
Information
To find out how to implement a UUID and autogeneration for ID columns in PostgreSQL check out How to generate PostgreSQL UUID with EF Core
Once we have finished creating all our Entity Type Configuration classes we just need to update our DiogelContext to use use them.
Here we can make use of a handy feature from EF Core the ApplyConfigurationsFromAssembly
enabling you to apply configuration from allIEntityTypeConfiguration<TEntity>/> instances that are defined in provided assembly.
public class DiogelContext : DbContext { public SwcContext(DbContextOptions<DiogelContext> options) : base(options) { } public DbSet<Threat> Threats { get; set; } public DbSet<ThreatType> Type { get; set; } public DbSet<Status> Status { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly()); } }
How to Define Migrations Assembly
The default behaviour of EF core is to place the Migration script with the assembly that defines the DbContext.
If you want to change the default behaviour, to say place the migrations script within your Web Application or API project, then you can make use of the DbContextOptionsBuilder
and the new MigrationsAssembly
method which you can pass the name of the assembly which you would like to include the Migrations in.
You define this by adding options when add the context configuriton during the ConfigureServices
in your application Startup
Advice
If you don't define a Migrations Assembly, EF will default to placing the migrations code to the library where the repository is defined.
If you want to have your separate library to contain the migrations, then there is no need to define a migrations library. However, if you want your API or web project to contain the migration code, then you need to configure the migrations library.
This is up to your or your organisations best practice guidelines.
In this post, I will just provide instructions on how to make use of the MigrationsAssembly feature.
For my particular project, I am not going to do this, because I would like to keep all my database specific code within my database project.
How to create a DbContextFactory
We now need to create factory class for creating derivedDbContextinstances. This class will Implement the IDesignTimeDbContextFactory
interface to enable design-time services for context types that do not have a public default constructor.
At design-time, derivedDbContextinstances can be created in order to enable specific design-time experiences such as model rendering, DDL generation etc.
Design-time services will auto-discover implementations of this interface that are in the same assembly as the derivedDbContexttype.
The ContextFactory is an implementation and example of the Factory Design pattern that is fairly common within the .net core framework code base.
Design Patterns
Elements of Reusable Object-Oriented Software
23 patterns allow designers to create more flexible, elegant, and ultimately reusable designs without having to rediscover the design solutions themselves
Read review
In order to implement this factory we will need to add and additional class to our project and call it SwcContextFactory.cs
using System.IO;using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Design;using Microsoft.Extensions.Configuration;namespace Api.Database.Postgre{ internal class DiogelContextFactory : IDesignTimeDbContextFactory<DiogelContext>{ public DiogelContext CreateDbContext(string[] args) { DbContextOptionsBuilder<DiogelContext> dbContextOptionsBuilder = new(); dbContextOptionsBuilder.UseNpgsql(@"localBuild"); return new DiogelContext(dbContextOptionsBuilder.Options); }}}
How to create Entity Framework Core Migration
Once this is done you can now, as in my case I tend to the terminal window in Rider : cross platform .net IDE , to navigate to my project directory and simply use:
dotnet ef migrations add [whatever name you want to give the migration]
For the curious, who want to take a look and examine the SQL Scripts that will be generated to create the migrations you can use
dotnet ef migrations add Initial --startup-project ../Api/swcApi.csproj
To view the SQL script your Migrations will use for your specific Database environment.
Check out the full list of Migration Commands
Open a Terminal window and navigate to the folder containing Api.Database.Postgre.csproj i.e. cd Api.Database.Postgre
. Due to the fact that we have created a Api.Database.Postgre as Class library project we will not be able to execute our dotnet ef commands directly against it, we will have to use the API as the startup project. Notice --startup-project
dotnet ef database update --startup-project ../Api/swcApi.csproj
After this command has completed you will notice a new folder has been created in your Database project, which will contain the migration scripts, however these scripts have not been applied to your database yet.
We can execute these scripts against our database to create database. Using the following command
dotnet ef database update --startup-project ../Api/swcApi.csproj
We can open our Database in whichever client we prefer. I use Jetbrains Rider which has a Database viewer and installed the PostgreSQL driver. I can then simply connect to my database and navigate to it to view
Summary
Using the above technique you can encapsulate all your database logic within a separate class library project that you can share across multiple projects. You will not be dependent on any project to build or execute any database changes, the result being all your database activities are encapsulated and constrained to one library.
- About
- Latest Posts
Technical Director at threenine.co.uk
Gary is Technical Director at threenine.co.uk, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scale-able.
Latest posts by Gary Woodfine (see all)
- What is this Directory.Packages.props file all about? - January 25, 2024
- How to add Tailwind CSS to Blazor website - November 20, 2023
- How to deploy a Blazor site to Netlify - November 17, 2023