Entity Framework Core – Probing Your Databases

November 9, 2016Jamie Taylor2 comments

Today’s header image was created by Matt Briney, the original source for the image is available here

Just a quick heads up: I’m going to be breezing through a bunch of Computer Science topics in this post. I’m not going to go into a huge amount of detail, but I am going to be providing links to other places for more information.

If you want to go straight to the Entity Framework goodness, then you can click here

What Is Entity Framework?

Entity Framework is an Object Relational Mapping (ORM) framework. Wikipedia gives the definition of an ORM as:

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.

Taken from the Wikipedia entry for ORMs

Entity Framework was originally written to be part of ADO.NET, for communicating with relational and non-relational databases. It handles connecting to, querying of, and updating databases.

One of the fantastic things that Entity Framework can do it read tables (and their relationships) into C# objects and types. This simplifies communicating with the database by abstracting things like DataReaders and DataTables away from your application.

Why Would You Use An ORM?

Using an ORM simplifies the code required to communicate with and create databases.

Instead of having to write reams of code just to read rows from specific tables; parse those rows into specific objects; and handle exceptions, you can use an ORM to do all of that for you.

It does this through the idea of a Database Context. Instead of having to:

Parameterise a database query

You should ALWAYS parameterise SQL queries, when you’re having to issue direct SQL.

Otherwise you leave yourself open to SQL Injection

Open up a Database Connection
Send the query
Read the Results
Close the Database Connection
Parse the results
Send results back to the calling method

You would issue something like:

var user = context.Users.GetOrCreate(userId);

view raw example.cs hosted with ❤ by GitHub

Which would return the User record with the id of “userId”, mapped to a C# object called User.

You have to agree that one line of code is better than having to write multiple classes, just to do the same thing.

Did I mention that Entity Framework has many different Database provider interfaces? Well it does, including (but not limited to):

SQL
SQLite
MongoDB
MySQL
Oracle DB

Pretty cool, huh?

So How Do I Use Entity Framework Core?

By this point, you should be able to guess what Entity Framework Core is, what with this blog being about .NET Core

Adding Entity Framework Core to an existing project is as simple as adding a reference to it in your project.json:

	"dependencies": {
	"Microsoft.EntityFrameworkCore": "1.0.0",
	"Microsoft.EntityFrameworkCore.Sqlite": "1.0.0",
	"Microsoft.EntityFrameworkCore.Sqlite.Design": {
	"version": "1.0.0",
	"type": "build"
	},
	"Microsoft.EntityFrameworkCore.Tools": {
	"version": "1.0.0-preview2-final",
	"type": "build"
	}
	},
	"tools": {
	"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
	}

view raw project.json hosted with ❤ by GitHub

I’ve explicitly included the Sqlite provider here, but you can use any provider you wish.

Then save it and run the following terminal command in the directory with your code:

dotnet restore

view raw bash.sh hosted with ❤ by GitHub

And all of the relevant packages will be downloaded and referenced in your project.lock.json file.

All you need to do then is include:

using Microsoft.EntityFrameworkCore;

view raw example.cs hosted with ❤ by GitHub

At the top of the class where you’ll be adding your Entity Framework DbContext code.

That’s all the set up that you need. However, you need to do some other things for best practises.

Here comes the Computer Science stuff.

Separation of Concerns

In computer science, separation of concerns (SoC) is a design principle for separating a computer program into distinct sections, such that each section addresses a separate concern. A concern is a set of information that affects the code of a computer program.

Taken from the Wikipedia entry for ORMs

In our program the “concern” is access to the Database (or the Entity Framework Context), and we’re separating it out for one simple reason: What if you decide that you don’t want to use Entity Framework Core?

You might decide to swap Entity Framework Core for something else, and if you do (but don’t use SoC) then you’d have to re-write all of your code which communicates with the database.

Programmers are lazy, we all know this is true.

And SoC is commonly done in .NET Core via Dependency Injection.

It’s not the only way to facilitate SoC, though. Just one of the most commonly used ways

Dependency Injection

Again, with Wikipedia?!

In software engineering, dependency injection is a software design pattern that implements inversion of control for resolving dependencies. A dependency is an object that can be used (a service). An injection is the passing of a dependency to a dependent object (a client) that would use it. The service is made part of the client’s state. Passing the service to the client, rather than allowing a client to build or find the service, is the fundamental requirement of the pattern.

Taken from the Wikipedia entry on Dependency Injection

Instead of having a dependency on Entity Framework throughout the entire code base, you would only want it to be injected into relevant places (maybe a single class which performs the database queries for you). You do this by injecting the dependency (in this case Entity Framework’s Database Context) into the relevant classes by passing Interfaces into their constructors.

Again, rather than having every class in your system be dependant on Entity Framework being present. Which goes hand in hand with SoC

Interfaces

The extremely simple description of an Interface is that it’s a type of abstract class which only defines method signatures.

Which makes them a little like header files in C/C++

So an example of an Interface would be:

	public interface IShape
	{
	void Draw();
	void Rotate(int degrees);
	void Scale(int scaleMultiplier);
	}

view raw IShape.cs hosted with ❤ by GitHub

And you would implement the Interface in a Circle class like this:

	// Circle implements IShape
	public interface Circle : IShape
	{
	public void Draw()
	{
	// code to draw the shape here
	}

	public void Rotate(int degrees)
	{
	// rotating circles is hard
	return;
	}

	public void Scale(int scaleMultiplier)
	{
	// code to scale here
	}
	}

view raw Circle.cs hosted with ❤ by GitHub

By creating an Interface, you can define all of the methods that a class which implements the interface MUST contain. It can contain more classes and properties, but it must contain the methods that are defined in the Interface as a bare minimum.

Using Entity Framework Core In Your Project

We’re going to take a few specific steps to enable us to use Entity Framework with best practises:

Add a Connection String
Add a Database Context
Create a Database Model
Add a Database Service with the Connection String and the Database Context
Create a Database Repository
Inject the Repository into .NET Core’s available Services
Create The Database
Use the Repository in a Controller

You can use Entity Framework without some of these best practises, but your experience with Entity Framework will be better for using them

Add a Connection String

The connection string is what Entity Framework will use to connect to a either local or remote database.

For the purposes of our example, we’re going to add a local database. So, head over to your applicationsettings.json and add this key:

	"ConnectionStrings": {
	"DefaultConnection": "Data Source=myDatabase.db"
	}

view raw project.json hosted with ❤ by GitHub

This tells Entity Framework Core to create a file called “myDatabase.db” in the build folder, but we’re not going to create it yet.

Add a Database Context

In order to be able to query database sets (which are the database tables, which in turn are based on our models), we need to add a database context to our project. So let’s create a new folder called “Contexts” and add a file in their called “DbContexts.cs”.

	// the namespace with our data models - more on those in a moment
	using CustomModels;

	// the Entity Framework namespace
	using Microsoft.EntityFrameworkCore;

	namespace DatabaseContexts
	{
	public class DatabaseContext : DbContext
	{
	public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }
	public DatabaseContext() { }

	// DbSet of our Product class
	public DbSet<Product> Products { get; set; }
	}
	}

view raw DbContexts.cs hosted with ❤ by GitHub

This tells .NET Core that we want a class called DatabaseContext which extends DbContext (which comes from the Microsoft.EntityFrameworkCore namespace), and that it will contain a DbSet of Products. This DbSet will be used to query our database – if it’s not listed here as a DbSet, then we wont be able to query the table.

Create a Database Model

Before we do any DI, we’re going to create a database model. This will form the basis of our database table, so we’ll need to figure out the layout of our table then convert it to an object. Luckily Entity Framework will do this, but in reverse. All we need to do is let it know which property is our primary key. Let’s create a database model:

	namespace CustomModels
	{
	public class Product
	{
	// our primary key
	public int ProductId { get; set; }
	public string ProductName { get; set; }
	public string ProductDescription { get; set; }
	// audit data
	public DateTime Created { get; set; }
	public DateTime Modified { get; set; }
	}
	}

view raw product.cs hosted with ❤ by GitHub

In this extremely simple example, Entity Framework will interpret “ProductId” as the primary key for the database table that it will create from this model. Usually Entity Framework will infer the primary key by looking for a property with the same name as the class and the word “Id” or just the word Id. “Class name + Id” is the safer option.

In our example, “ProductId” fits this pattern

Adding the Database Service with the Connection String and the Database Context

Head on over to your Startup class (Startup.cs) and find the ConfigureServices method and add the following lines:

	// need to reference Entity Framework
	using Microsoft.EntityFrameworkCore;

	// also need to reference our contexts namespace
	using DatabaseContexts;
	namespace MyApp
	{
	public class Startup
	{
	public void ConfigureServices(IServiceCollection services)
	{
	// Add a reference to our DatabaseContext class as a DbContext.
	// Use our DefaultConnection string from the appsettings.json
	services.AddDbContext<DatabaseContext>(options =>
	options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));
	}
	}
	}

view raw startup.cs hosted with ❤ by GitHub

I’ve removed a lot of the code that you don’t need to see (to make the code block smaller), so don’t just copy this block, do a visual diff check on it.

This tells .NET Core to add a DbContext of the type DatabaseContext (our custom context class) and for it to use the Database Connection string that can be found in the application settings json with the name “DefaultConnection”.

Create a Database Repository

Here’s were we’re going to use an Interface. We’re going to do this because we’ll be dependency injecting our Repository class into all of the relevant controllers.

Pro tip: .NET Core does Dependency Injection for you, and it’s amazingly fast

We’re going to create a Repositories folder, add an “IProductRepository.cs” file there, and copy this into the file:

	using CustomModels;

	// for IEnumerable<T>
	using System.Collections.Generic;

	namespace MyRepositories
	{
	public interface IProductRepository
	{
	void AddOrUpdate(Product item);
	IEnumerable<Product> GetAll();
	Product Find(int key);
	void Remove(int key);
	void Update(Product item);
	}
	}

view raw IProductRepository.cs hosted with ❤ by GitHub

This Interface defines the methods that our Repository class MUST contain, and they are all related to CRUD operations.

Now we need to create a “ProductRespository.cs” file that will implement those methods:

	using DatabaseContexts;
	using CustomModels;
	using Microsoft.EntityFrameworkCore;

	// for IEnumerable<T>
	using System.Collections.Generic;

	// for LINQ things
	using System.Linq;

	namespace MyRepositories
	{
	public class ProductRespository : IProductRepository
	{
	// Dependency injection will happen here. The DatabaseContext
	// class will be injected into the constructor from the
	// IServicesCollection - more on that later
	private DatabaseContext _context;
	public ProductRespository(DatabaseContext context)
	{
	_context = context;
	}

	public void AddOrUpdate(Product item)
	{
	var databaseEntity = _context.Find(Product.ProductId);

	// If we can't find an instance of the product
	// in the database, then we need to add it to
	// the DbSet.
	// Assumption: a Product with an empty name is a new instance
	if (_string.IsNullOrEmpty(databaseEntity.ProductName))
	{
	_context.Products.Add(item);
	}
	// Otherwise we want to update it
	else
	{
	Update(item);
	}
	_context.SaveChanges();
	}

	public Product Find(int key)
	{
	return _context.Products
	.Where(prod => prod.ProductId == key)
	.SingleOrDefault();
	}

	public IEnumerable<Product> GetAll()
	{
	return _context.Products.AsNoTracking();
	}

	public void Remove(int key)
	{
	var itemToRemove = _context.Products
	.SingleOrDefault(prod => prod.ProductId == key);
	if (itemToRemove != null)
	{
	_context.Products.Remove(itemToRemove);
	_context.SaveChanges();
	}
	}

	public void Update(Product item)
	{
	var itemToUpdate = _context.Product
	.SingleOrDefault(prod => prod.ProductId == item.ProductId);
	if (itemToUpdate != null)
	{
	// TODO: add code to update entity fields here
	_context.SaveChanges();
	}
	}
	}
	}

view raw ProductRepository.cs hosted with ❤ by GitHub

A few comments on some of the above code:

	public Product Find(int key)
	{
	return _context.Products
	.Where(prod => prod.ProductId == key)
	.SingleOrDefault();
	}

view raw ProductRepository.cs hosted with ❤ by GitHub

This LINQ statement (in the Find method) roughly translates to the following SQL

SELECT TOP 1 * FROM PRODUCTS WHERE ProductId = key

view raw exampleSelect.sql hosted with ❤ by GitHub

And the SingleOrDefault() tells LINQ to grab the first result or return a new instance if there are no results.

	public IEnumerable<Product> GetAll()
	{
	return _context.Products.AsNoTracking();
	}

view raw ProductRepository.cs hosted with ❤ by GitHub

Firstly, AsNoTracking() tells Entity Framework to disable change tracking, essentially putting the query into read-only mode.

Usually Entity Framework will track any changes that you make to objects once you have gotten them from the database. The Change Tracker is how it knows which things need to be persisted in the SaveChanges transaction, rather than persisting every entity that your queried for back to the database.

Oh yeah it’s transactional, too.

Secondly, IEnumerable<T> and IQueryable<T> are what Entity Framework use to support Lazy Loading. Essentially this means that the results of the query are enumerated into new instances of their classes at the last possible second. This helps to keep memory usage down, especially in systems that have low resources or for queries that could have hundreds of results.

An example of where you would use IEnumberable<T> or IQueryable<T> over List<T> (which would pull all results into memory) would be querying a a Users table with millions of entries. IEnumberable<T> and IQueryable<T> allow further filtering to be performed before the entities are loaded into memory (via List<T>).

These methods provide us with the basic CRUD operations. You’ll eventually need to add more, but for the purposes of this tutorial it’ll be enough.

Inject the Repository into the Available Services

Now that we have our Repository class, we need to head back to our Startup class (Startup.cs) and make the following additions:

Again, I’ve removed the unchanged code. So don’t just paste this block in

	// we need to reference our Respositories
	using MyRepositories;

	namespace MyApp
	{
	public class Startup
	{
	public void ConfigureServices(IServiceCollection services)
	{
	// DI our interfaces into out controllers
	services.AddTransient<IProductRepository, ProductRespository>();
	}
	}
	}

view raw startup.cs hosted with ❤ by GitHub

This tells .NET Core that we want to add a transient service. The first argument here is commonly an Interface and represents the type that will be requested by the consumer, and the second argument is the class type to be instantiated by the consumer.

We’re creating it as a Transient here, which is fine for our example. But for future reference, we’ll need to know the different types of services:

Transient – Transient lifetime services are created each time they are requested. This lifetime works best for lightweight, stateless services.
Scoped – Scoped lifetime services are created once per request.
Singleton – Singleton lifetime services are created the first time they are requested (or when ConfigureServices is run if you specify an instance there) and then every subsequent request will use the same instance.

Taken from the .NET Core documentation on Dependency Injection

Create The Database

Before you can query the database, we need to create the database. To do this you’ll need to go back to the terminal, browse to the root directory of the project and issue the following command:

dotnet ef migrations add InitialMigration

view raw bash.sh hosted with ❤ by GitHub

This will add a database Migration which describes the changes made to your data models (and as an extension to that, the database tables) called InitialMigration.

If you browse to the Migrations folder you should see a file with “InitialMigration” in it’s name (most of the name will be the date time when you issued the above terminal command), opening that will show you the changes that the initial migration will perform on the database.

	using System;
	using System.Collections.Generic;
	using Microsoft.EntityFrameworkCore.Migrations;

	namespace MyApp.Migrations
	{
	public partial class InitialMigration : Migration
	{
	protected override void Up(MigrationBuilder migrationBuilder)
	{

	}

	protected override void Down(MigrationBuilder migrationBuilder)
	{

	}
	}
	}

view raw InitialMigration.cs hosted with ❤ by GitHub

The Up method will fire when we issue the next command and is used to update the database, and the Down method will fire if we need to downgrade the database. The above is an example migrations file and yours will be different.

This doesn’t create the database or apply the migration, however. To do that, you need to issue the following command:

dotnet ef database update

view raw bash.sh hosted with ❤ by GitHub

This will take the changes in the InitialMigration file and performs them on the target database.

As you add more migrations, it will probe your database to find the latest migration that was applied then apply all subsequent migrations, in creation date order, to the database.

Use the Repository In A Controller

Since we’ve added our Repository class (via the IRepository interface) as a Transient service via Dependency Injection, we can get access to it in any Controller as the IServiceCollection is available in the constructor for all Controller classes. The way we do this is to tell .NET Core we’d like our IRepository to be passed into our Controller’s constructor:

	using CustomModels;
	using MyRepositories;
	using System;
	using Microsoft.AspNetCore.Mvc;

	namespace MyApp.Controllers
	{
	[RouteAttribute("/")]
	public class MyController : Controller
	{
	private IRepository _repository { get; set; }

	public MyController(IRepository repository)
	{
	_repository = repository;
	}
	}
	}

view raw MyController.cs hosted with ❤ by GitHub

The constructor for MyController takes an IRepository, and .NET Core knows that this is listed as a service which can be passed in to it. So .NT Core will pass the instance of IRepository to it, whenever a new instance of the MyController class is instanciated.

Here is how you can use the IRepository instance to add a record to the database:

	using System;
	using System.Collections.Generic;
	using Microsoft.EntityFrameworkCore.Migrations;

	namespace MyApp.Migrations
	{
	public partial class InitialMigration : Migration
	{
	protected override void Up(MigrationBuilder migrationBuilder)
	{

	}

	protected override void Down(MigrationBuilder migrationBuilder)
	{

	}
	}
	}

view raw InitialMigration.cs hosted with ❤ by GitHub

We don’t need to set the ProductId field here, because Entity Framework will do that for us when we call the SaveChanges method (which we have included in our AddOrUpdate method earlier). We don’t need to set the values for any keys (primary or foreign) for any of our data objects either, as Entity Framework will calculate the keys based on any relationships between the objects when the data is persisted back to the database.

Further Information

https://docs.efproject.net/en/latest/

I would start here, as it’s the documentation for Entity Framework Core. Similarly to the .NET Core documentation, it is build from a GitHub base (which can be found here https://github.com/aspnet/EntityFramework.Docs). It has everything you need for learning more about Entity Framework Core.

http://thedatafarm.com/

Julie Lerman is recognised as the foremost expert outside of Microsoft on Entity Framework, and her blog contains all sorts of insights into how Entity Framework’s internals work.

If this post has helped you out, please consider