We're going to create ours with the help of the EntityFramework Core (EF Core), the well-known open-source object-relational mapper (ORM) for ADO.NET developed by Microsoft. The reasons for such a choice are many:

You might be wondering why we're choosing to adopt a SQL-based approach instead of going for a NoSQL alternative. There are many good NoSQL products such as MongoDB, RavenDB, and CouchDB that happen to have a C# connector library. What about using one of them instead?

The answer is rather simple: they are not supported yet by the EF Core 1.0.0, which, at the time of writing, happens to be the latest stable release. If we look at the EF Core team backlog we can see that non-relational database providers, such as Azure Table Storage, Redis, and others, are indeed mentioned for upcoming support, but it's unlikely that we'll be able to see any of them implemented within the EF Core's future releases as well.

In order to install the EF Core, we need to add the relevant packages to the dependencies section of our project.json file. We can easily do that using the visual GUI like we did for the Newtonsoft.json package in the following way:

Next, install the following packages:

Alternatively, we could add the relevant packages manually within the project.json file as follows (new lines highlighted):

  "dependencies": { 
    "Microsoft.AspNetCore.Mvc": "1.0.0", 
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0", 
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0", 
    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0", 
    "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0", 
    "Microsoft.Extensions.Configuration.Json": "1.0.0", 
    "Microsoft.Extensions.Logging": "1.0.0", 
    "Microsoft.Extensions.Logging.Console": "1.0.0", 
    "Microsoft.Extensions.Logging.Debug": "1.0.0", 
    "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0", 
 
    "Microsoft.AspNetCore.Diagnostics": "1.0.0", 
    "Microsoft.AspNetCore.Routing": "1.0.0", 
    "Microsoft.AspNetCore.Authentication.JwtBearer": "1.0.0", 
    "Microsoft.AspNetCore.StaticFiles": "1.0.0", 
    "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0", 
    "Newtonsoft.Json": "9.0.1", 
 
    "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.0.0",

    "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0",

    "Microsoft.EntityFrameworkCore": "1.0.0",

    "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",

    "Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final", 
  } 

As usual, the advantage of doing it manually is that we can keep the packages more organized by separating them into commented blocks.

Among the installed namespaces we can easily see the presence of the EntityFrameworkCore.SqlServer, a highly versatile connector providing an interface with the whole MSSQL server database family: SQL Server 2008-2014, as well as the Express and Compact editions for personal and development usage.

We're free to choose between using one of them and picking another DBMS engine such as MySQL, PostgreSQL, or any other EF-compatible product. Should we take this decision now? It entirely depends on the data modeling approach we want to adopt. For the time being and for the sake of simplicity, we'll be sticking to it.

Now that we have EF installed, we have to choose between one of the three available approaches to model the data structure: model-first, database-first and code-first. Each one of them comes with its fair amount of advantages and disadvantages, as the experienced readers and seasoned .NET developers will most certainly know. While we won't dig too much into these, it could be useful to briefly summarize each one of them before making the choice.

The model-first approach

If you're not familiar with the Visual Studio IDE design tools such as the XML-based DataSet Schema (XSD) and the Entity Designer Model XML (EDMX) visual interface, the model-first approach can be rather confusing. The key to understanding it is to acknowledge the fact that the word model here is meant to define a visual diagram built with the design tools. This diagram will then be used by the framework to autogenerate the database SQL script and the data model source code files.

To summarize, we can say that choosing the model-first option means working on a visual EDMX diagram and letting EF create/update the rest accordingly.

The model-first approach

Pros

• We will be able to create the database scheme and the class diagram as a whole using a visual design tool, which could be great when the data structure is quite big.
• Whenever the database changes, the model can be updated accordingly without data loss.

Cons

• The diagram-driven, autogenerated SQL scripts could lead to data loss in case of updates. An easy workaround for that would be generating the scripts on disk and manually modifying them, which would require decent SQL knowledge.
• Dealing with the diagram can be tricky, especially if we want to have precise control over our model classes. We wouldn't always be able to get what we want, as the actual source code would be auto-generated by a tool.

The database-first approach

Given the disadvantages of the model-first approach, we might think that the database-first approach might be the way to go. This could be true if we either have a database already or don't mind building it beforehand. That being the case, the database-first approach is similar to the model-first one, except it goes the other way around. Instead of designing EDMX manually and generating the SQL script to create the database, we build the latter and then generate the former using the ED Designer tool.

We can summarize it by saying that choosing the Database-first approach means building the database and letting EF create/update the rest accordingly.

The database-first approach

Pros

• If we have an already-existing database in place, this will most likely be the way as it will spare us the need to re-create it.
• The risk of data-loss will be kept to a minimum because any change or update will be always performed on the database.

Cons

• Manually updating the database can be tricky if we're dealing with clusters, multiple instances or a number of development/testing/production environments, as we will have to manually keep them in sync instead of relying upon code-driven updates/migrations or autogenerated SQL scripts.
• We would have even less control over the autogenerated model classes (and their source code) than if we were using the model-first approach. It would require an extensive knowledge of EF conventions and standards, otherwise, we'll often struggle to get what we want.

The code-first approach

Last but not least comes the EF flagship approach since EF4, which enables an elegant, highly efficient data model development workflow. The appeal of this approach can easily be found in its premise: the code-first approach allows the developer to define model objects using only standard classes, without the need for any design tool, XML mapping files or cumbersome piles of autogenerated code.

To summarize, we could say that choosing the code-first approach means writing the Data Model entity classes we'll be using within our project and letting EF generate the database accordingly.

The code-first approach

Pros

• No need for diagrams and visual tools whatsoever, which could be great for small-to-medium size projects as it would save us a lot of time.
• A fluent code API that allows the developer to follow a convention over configuration approach, and to handle the most common scenarios, while also giving him the chance to switch to custom, attribute-based implementation overrides whenever he needs to customize the database mapping.

Cons

• A good knowledge of C# and updated EF conventions is required.
• Maintaining the database could be tricky sometimes, as well as handling updates without suffering data loss. The migrations support, added in 4.3 to overcome the issue which has been continuously updated since then, greatly mitigates the problem, although it has also affected the learning curve in a negative way.