Many years back I looked at Entity Framework, this was still when I wanted to learn about the Microsoft .Net framework which was before .Net Core came along. At the time most of my day was consumed by VB Script and Microsoft SQL. Typically connecting to the database in VB Script meant hand rolling the connection and working with a data reader and record set. It was, to say the least painful!
Example of my pain:
1 | set dbCon = CreateObject("ADODB.Connection") |
So when I finally started looking at C# and .Net I found something called a Repository Pattern. This was awesome as it promoted dependency inversion and I could create my own repository using an ORM (Object Relational Mapper) called Dapper which provided extension methods like:
- Select:
connection.Query<T>(sql, parameters).FirstOrDefault();
- SelectList:
connection.Query<T>(sql, parameters).ToList();
- Insert:
connection.ExecuteScalar<int>(sql, (T)poco);
- Update:
connection.Execute(sql, (T)poco);
- Delete:
connection.Execute(sql, parameters);
Entity Framework felt like it abstracted away so much of the magic
that I didnt actually know what it was doing, this worried me as what would I do when it breaks? I also profiled the SQL Database when trying out Entity Framework boilerplate code and I was amazed to see how many SQL Queries were running! I figured yeah na this is too complex for my brain.
For this reason I happily plodded along with my new friend the Dapper and the Repository Pattern. However last year I changed jobs and my new team wanted to use Entity Framework Core. I was pretty excited about this as .Net Core is amazing and it was a chance to learn by doing!
Database Setup
The database is configured in the applications db context DatabaseContext.cs with extension methods. The examples below are for SQLite and SQL Server although EF works with many more.
1 | options.UseSqlite("Data Source=sweetapp.db"); |
Although SQLite is self contained and free tools like DBeaver can manage the sweetapp.db
file I wanted to use SQL Server. The fastest way to get an instance running is with docker and the mssql-server-linux image.
1 | docker pull microsoft/mssql-server-linux:2017-latest |
Once up check you can connect with SQL Server Management Studio (SSMS). If you can it means your containerized database is ready for action.
Install the command-line interface (CLI) tools for Entity Framework Core to perform design-time development tasks.
1 | dotnet tool install --global dotnet-ef |
Create the application and install the relavant packages. The folder structure I used was /EntityFrameworkCoreDemo/src/SweetApp/
1 | dotnet new console -o SweetApp |
Domain Models
Create the domain models, these would be for internal application use and then create the infastructure database context for DbSet
commands.
Entities in the models with xId
in them are assumed as primary keys, example BlogId
. We could also use ID
. We can also then control mapping, cascase deletes, relations ect with entity propertys or builder configuration with EntityTypeBuilder.
The update/delete in Program.cs affects both tables dbo.Blog
and dbo.Post
because of the domain model relationship blog.Posts.Add( ...
Migrations
Create the migration and run it, this is a code first approach. Any changes to the domain models will be applied to the database each time you run migrations add
and then database update
. The MeaningfulMigrationName
needs to be unique for each migration.
1 | dotnet ef migrations add MeaningfulMigrationName |
This will be reflected in the __EFMigrationsHistory
table.
To make additional changes to the database update the domain models and repeat the steps above with MeaningfulMigrationName2
. If things go sideways you can revert with:
1 | dotnet ef migrations remove |
CRUD commands
Run the application. This will execute the commands in Program.cs
1 | dotnet run |
These are simple CRUD (Create, read, update and delete) commands copied from Getting Started with EF Core.
1 | using var db = new DatabaseContext(); |
If you look at the data before the delete command is run, it will be persisted to SQL.