2021-Feb

Entity Framework Core

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!

VB Script example of my pain

set dbCon = CreateObject("ADODB.Connection")	
dbCon.Open "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;MultipleActiveResultSets=true;"

    sql = "SELECT id, desc FROM foo WHERE bar = 'bat' ORDER BY id DESC"
    set rs = dbCon.execute(sql)

        if not rs.eof then
            do until rs.eof

                response.Write rs("id") & " " & rs("desc") & "<br />"
            
            rs.movenext
            loop
        end if

    rs.Close
    set rs = nothing

dbCon.Close
set dbCon = nothing

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.

--- when using Sqlite
options.UseSqlite("Data Source=sweetapp.db");

--- when using SQL Server
options.UseSqlServer("Data Source=localhost,1401;Initial Catalog=SweetApp;Integrated Security=False;User Id=sa;Password=2dd73426-d37a-4fd2-89e9-92f2a8f1a526;Pooling=False")

--- when using Postges
options.UseNpgsql("Server=localhost;Port=5432;Database=demo_db;UserId=postgres;Password=42ebe52d-ddc9-47c6-b03d-b2cd1ca17393")

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
2

docker pull microsoft/mssql-server-linux:2017-latest
docker run --name=ef-core -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=2dd73426-d37a-4fd2-89e9-92f2a8f1a526' -p 1401:1433 -d 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.

SQL Server Management Studio (SSMS)

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/

dotnet new console -o SweetApp

dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

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 2	dotnet ef migrations add MeaningfulMigrationName dotnet ef database update

This will be reflected in the __EFMigrationsHistory table.

Migration Run

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

SQL Server Management Studio (SSMS)

These are simple CRUD (Create, read, update and delete) commands copied from Getting Started with EF Core.

using var db = new DatabaseContext();

// Create
Console.WriteLine("Inserting a new blog");
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
db.SaveChanges();

// Read
Console.WriteLine("Querying for a blog");
var blog = db.Blogs
    .OrderBy(b => b.BlogId)
    .First();

// Update
Console.WriteLine("Updating the blog and adding a post");
blog.Url = "https://devblogs.microsoft.com/dotnet";
blog.Posts.Add(
    new Post { Title = "Hello World", Content = "I wrote an app using EF Core!" });
db.SaveChanges();

// Delete
Console.WriteLine("Delete the blog");
db.Remove(blog);
db.SaveChanges();

If you look at the data before the delete command is run, it will be persisted to SQL.

Persisted data

Database Setup

Domain Models

Migrations

CRUD commands

Refrences