Implement a Relational Database With ASP.NET Core

Entity Framework Core

You may not have realized it until now, but you’ve been using an object-relational mapping (ORM) tool through many of your courses in the .NET path here at OpenClassrooms. In the MVC course, for example, you used Entity Framework (EF) to construct a database using code-first migrations. EF is the standard ORM for .NET, and it’s included with most of the projects you might build in Visual Studio.

With .NET Core, Microsoft has expanded the accessibility of Entity Framework with Entity Framework Core (EF Core). Microsoft defines EF Core as:

a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology.

One of the keywords here is lightweight. Historically, EF has sometimes been derided by developers due to its bloated size. That’s all changed with EF Core, which functions more like a micro-ORM than its predecessor. 

I cannot overstate the importance of EF Core being cross-platform. Developers have long complained about .NET applications being reliant upon the Windows operating system, but with .NET Core and EF Core, that’s no longer a limitation.

Perhaps even more important is the fact that EF Core is now open source. This is also what makes EF Core extensible. If you need it to provide additional functionality or alter its functionality to suit your needs, you can write code to extend the functionality yourself. 

With EF Core, like other ORMs, .NET developers can work with their databases using C# objects. This is great news for developers, who often have to write all the required data access code from the ground up when working in other languages.

Writing that data access yourself can be a difficult task. Relational databases represent all of their data in the form of tables, like a series of spreadsheets. Object-oriented languages like C# represent their data more like an intricately connected series of graphs. This makes getting the two to play nice together quite a challenge.

Hence, the need for ORMs. Tools like EF Core take a data model consisting of C# objects and translates it into a relational form that mirrors the relational database schema.

It sounds simple, and it is if you’re using an ORM. If you’re going to build one yourself, it’s anything but simple and not something I want to teach! I want to show you how to use the tools that will benefit you the most - like EF Core.

Alternative ORMs

There are other ORMs out there you could consider using. The list is far too large to mention here. But I do want to briefly discuss just a few of them that you might encounter.

NHibernate

NHibernate is one of the most widely used ORM choices for .NET outside of Entity Framework and makes common data persistence-related programming tasks as simple as possible. It’s not the best solution for applications that only use stored-procedures to implement the business logic in the database. However, it's most useful with object-oriented domain models and business logic in the .NET-based middle-tier. NHibernate can help you to remove or encapsulate vendor-specific SQL code, as well as the task of result set translation from a tabular representation to a graph of objects.

Dapper

This micro-ORM has gained a lot of popularity recently and is even included as an ORM option in Visual Studio when creating a new project. One of its perks is its mapper functionality, which works a lot like LINQ in EF Core but also provides extension methods that simplify submitting T-SQL statements to the database.

LINQ to DB

In the science of human cognition, the micro-levels are very small units of thought or communication, and macro-levels are larger ones. Meso-levels lie somewhere between. Sort of like Goldilocks finding that “just right” bowl of porridge in Goldilocks and the Three Bears.

If Dapper is a micro-ORM and EF is a macro-ORM, then architecturally, LINQ to DB is a meso-ORM. It lets you work with LINQ expressions as opposed to SQL or T-SQL strings while maintaining a thin layer of abstraction between your code and the database. Queries are object-based and therefore checked by the C# compiler. But it’s not nearly as heavy as LINQ to SQL or Entity Framework. For example, there’s no change-tracking, so you have to manage that yourself, but it does allow for greater control and much faster access to your data. It is the fastest of all the LINQ-based ORMs.

Insight.Database

This is a lesser-known, but excellent micro-ORM designed by developer Jon Wagner specifically for .NET and available as a NuGet Package. It is extremely fast, lightweight, and simple to implement and use. It works with all common databases and can use either stored procedures or inline SQL.

One of its best features is its auto interface implementation. I'm not going into it this in the course, but it's an excellent micro-ORM.

What About the Repository Pattern?

The repository pattern has become quite popular since its introduction to the IT community as part of domain-driven design in 2004.

In a nutshell, the repository pattern separates the logic that maps database data to an entity model from the business logic that acts on that model. Creating, updating, deleting, and selecting entities from the data collection is done through the creation and use of simple methods, without the need to worry about all those pesky database connectivity issues, cursors, or readers.

Consider the following example of a generic repository pattern.

public interface IRepository<T> where T : MyBaseEntity
{
    IEnumerable<T> List();
    IEnumerable<T> List(Expression<Func<T, bool>> predicate);
    T GetEntityById(int id);
    void Add(T entity);
    void Delete(T entity);
    void Edit(T entity);
}
 
public abstract class MyBaseEntity
{
    public int Id { get; protected set; }
}
 
public class MyRepository<T> : IRepository<T> where T : MyBaseEntity
{
    private readonly ApplicationDbContext _context;
 
    public Repository(ApplicationDbContext context)
    {
        _context = context;
    }
 
    public virtual IEnumerable<T> List()
    {
        return _context.Set<T>().AsEnumerable();
    }
 
    public virtual IEnumerable<T> List(
       System.Linq.Expressions.Expression<Func<T, bool>> predicate)
    {
        return _context.Set<T>()
            .Where(predicate)
            .AsEnumerable();
    }
 
    public virtual T GetById(int id)
    {
        return _context.Set<T>().Find(id);
    }
 
    public void Insert(T entity)
    {
        _context.Set<T>().Add(entity);
        _context.SaveChanges();
    }
 
    public void Update(T entity)
    {
        _context.Entry(entity).State = EntityState.Modified;
        _context.SaveChanges();
    }
 
    public void Delete(T entity)
    {
        _context.Set<T>().Remove(entity);
        _context.SaveChanges();
    }
}

This example demonstrates a generic repository pattern that uses a single set of methods to perform database operations regardless of entity type. This can be a useful addition to your application!

Well, that depends on several factors, such as which ORM you are using, the needs of your application, client specifications, or just personal preference.

If you’re using a micro-ORM like Insight.Database, part of your implementation is going to be setting up a repository pattern such as this. However, notice that in the example above, we’re still using Entity Framework to handle the database connectivity and perform the actual database operations. The code is simply a mediator between logic layers.

The correct answer is no-yes-or maybe.

Like many other things in software development, there is a lot of debate on the subject, and you’ll find convincing arguments for and against the use of the repository pattern with Entity Framework Core. The reason is that the pattern is already implemented in EF Core, so why redo it?

In EF Core, the repository pattern is implemented at the entity level. That is, each entity has its own set of database operations associated with it. If I want to insert a new instance of a  Student  into my  Students  table in the database, I simply call:

_context.Students.Add(student);

My personal preference is to use the tools at hand and not waste time redoing work someone else has already done. This is not to say the generic pattern I showed you earlier is a bad idea. On the contrary, it could simplify your development efforts (and application maintenance) if you feel inclined to use it, but you should never feel obligated to implement an additional repository pattern over what is already in EF Core.

As in so many other aspects of software development, the choice is yours.

Let's Recap!

In this chapter, you learned the concept of object-relational mapping (ORM), how to map a series of related objects, such as C# classes, to the schema of a relational database. You also learned about:

• Entity Framework (EF) Core, a lightweight, open source, cross-platform version of the EF technology, which is the built-in ORM tool for .NET Core.

• Examples of popular alternative ORM and micro-ORM choices for .NET.

• The repository pattern and its place (or lack thereof) in EF.

Now it’s time to learn how to use EF to design and code a data model for a .NET web application, and build a usable relational database from that model using code-first migrations.