This article is the first of a series that is aimed to explain how to use familiar technologies with .NET CORE.

The aim of this post is to create a Database from scratch using Entity Framework. We will configure SqlServer to run with our core app and finally touch upon table relationship in Entity Framework.

Disclaimer: This is the first time I use entity Framework and .NET Core, so I just trying to share my “findings” and it is by no mean the “best” solution available. I am more than open to suggestion if I can make the code below faster and cleaner.

Getting Started

To be able to follow along with this article, you will need to have a project set up and ready. If you do not have one, I would suggest you to create a new project (Web Api preferred) by using the newly released Templates available cross platform. To enable this you will need to install the newest core package for visual studio.

In our case we are going to use a newly created Web API build on Core 2.0.

Tables

One of the hardest task to complete, before you can create a full set of tables in a relationship database ( like Sql and MySql) it is to create a full map of the tables. It may seem clean in your head what your app needs, but I greatly suggest you to create a complete map of all the tables, columns and its relationship on paper to make sure that you have thought at everything.

You will be able to change things later on, but database architecture it is very important for the performance of your application.

In our case we are going to create a player table and an inventory table.

The tables will have the following columns

Player

• (int) PlayerId
• (string) Name
• (decimal) health

Inventory

• (int) InventoryId
• (int) PlayerId
• (string) Name

The first step is to create a Model class for each of the above table. It is good practice to have all models together within a folder placed in the Root of our application called Models.

Now that the folder is in place, we can two files, and call them Player.cs and Inventory.cs.

namespace My_game
{
    public class Player
    {
        public long PlayerId { get; set; }
        public string Name { get; set; }
        public decimal health { get; set; }
    }
}

The above model is quite simple. If you are familiar with c# at all, you have surely created a file that looked like this in the past. The power Entity Framework will use the above class to create the table and support us in mapping our future database queries.

Now we need to create another file that will include the Inventory class. This is going to be slightly different than the one above, as we this class is expecting to have a “relationship” with the player class, because each player will be able to have many pieces of inventory.

namespace My_game
{
    public class Inventory
    {
        public long InventoryId { get; set; }
        public decimal Name { get; set; }
        virtual Player Player { get; set; }
    }
}

As shown by the above code, to add a relationship you just need to use a “virtual” entry with the use of the recently created “player” model.

As you may have noticed, we have not specified any unique identified when creating the models, and not any specification on where the relationship of the tables lies.

The magic is in the names. Entity Framework, if not specified, will expect the Unique Identifier to be either called Id, or the class name appended by Id (eg. InventoryId, PlayerId).

Relationship are handled a very similar way, it assume the tables are connected by using its unique identifier, and as with the above case, we are free to change the default ( this will not be covered in this article).

Context

Now that we have a couple of tables in action, it is time to fit them together we need to use System.Data.Entity.DbContext class (often refereed as context). This class is responsible of creating a complete picture of the database.

For this example we are going to create a context file called dbContext.cs. This file will include the models created above and will look like the following snippets:

namespace My_game.Models
{
    public class dbContext : DbContext
    {
        public dbContext(DbContextOptions<dbContext> options)
            : base(options)
        {
        }
        public DbSet<Player> Player { get; set; }
        public DbSet<Inventory> Inventory { get; set; }
    }
}

The above class is going to be used in the next few chapter with the use of Entity Framework tools to create a migrations script that will eventually create our database tables.

Configure SQL server

Now that all our models and the context have been fully developed, we are ready to configure SQL server. This article is not going to explain how to set up an SQL server and create a Database, and it is out of scope, but plenty or resources can easily be found on this topic.

Assuming that you have a server and a database setup, we will need to add a connection string in our appsettings.json file. This connection string will provide our application with the correct credential to connect to our database.

To add a local server called mssqllocaldb and a database called my_app_db,  a connection string would look something like this:

"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=my_app_db;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

The connection string vary depending from the setting, authentication and location of the server, so the one above has just been shared to give you an idea. You can have multiple connection strings ( for development, and live environment). In our case the connection string is going to be called DefaultConnection.

Now that we have added the above entry in our appsettings.json file, we are ready to link the database to our app.

This time we are going to insert some code in the Startup.cs file. This file includes all the services and configuration that are going to be made available within the app.

Our database connection is going to be a service, and as such, our code is going to be inserted within the ConfigureServices method.

            services.AddDbContext<dbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Adding the service is quite straight forward, you just need to add the context name that we have previously defined dbContext, and specify the connection string that we want to use to connect to our DB DefaultConnection. This is all we need to be able to then access the DB within our application.

The example above will connect to SQL server, but there are already options within the .net framework to connect to the most used database servers.

Query the database

Now that everything is linked, query the database is going to be very simple. For example the file below shows the code required to do a select statement (get) and an insert statement (add).

Conclusion

In the next few articles we are going to explain how to create a migration script and how to use them to create your tables, we will also explain how to set you database to seed our tables with static data on start up.

This article has covered the basic set up required to get you up an running with SQL server on a .NET Core appto enable you to get started. I really hope you find any of the information shared above useful and I am happy to get any comments to support me in tailor the above information for future readers.