xForcer

[Tutorial/Guide] Entity Framework Core + MySQL (C#)

Recommended Posts

 

Hello everyone!

 

Many of you have decided to use Entity Framework instead of raw SQL but failed somehow in the process (myself included)! This is why I am creating this quick guide with some examples on how to use Entity Framework Core with MySQL database in RAGEMP C# gamemode. It's not perfect, but it will work just fine. If you find a mistake or have a better way of doing something, please let me know!

 

Let's start!


Requirements:

Visual Studio 17
- Net Core 2.2
- Latest RageMP and C# Bridge files
- MySQL database (I use XAMPP)

 

1. First, you will need some dependencies. Open up the nuget package manager and add these dependencies to your project:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Tools
  • Pomelo.EntityFrameworkCore.MySql
  • Pomelo.EntityFrameworkCore.MySql.Design
  • Microsoft.Extensions.Configuration
  • Microsoft.Extensions.Configuration.Json

Pomelo.EntityFrameworkCore.MySql is a MySQL provider. There are many more providers, but Pomelo's is just fine.
Microsoft.Extensions.Configuration.Json is used for appsettings.json.

 

How it looks when everything's added:

pIk7v5E.png

 

2. Now we are ready to create a DbContext class. I will just copy and paste and explain needed with comments!

 

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace EFCoreTutorial
{
    public class DefaultDbContext : DbContext
    {
      	// Base constructor
        public DefaultDbContext(DbContextOptions options) : base(options)
        {

        }

        // Account model class created somewhere else
        public DbSet<Account> Accounts { get; set; }

    }

    public class ContextFactory : IDesignTimeDbContextFactory<DefaultDbContext>
    {
        private static DefaultDbContext _instance;
        private static string _connectionString;

        public DefaultDbContext CreateDbContext(string[] args)
        {
            var builder = new DbContextOptionsBuilder<DefaultDbContext>();

            // Load the connection string for the first time
            if (string.IsNullOrEmpty(_connectionString))
            {
                LoadConnectionString();
            }

            // Use it to init the connection
            builder.UseMySql(_connectionString, optionsBuilder => optionsBuilder.MigrationsAssembly(typeof(DefaultDbContext).GetTypeInfo().Assembly.GetName().Name));

            return new DefaultDbContext(builder.Options);
        }

        public static DefaultDbContext Instance
        {
            get
            {
                if (_instance != null) return _instance;

                return _instance = new ContextFactory().CreateDbContext(new string[] { });
            }
            private set { }
        }

        private static void LoadConnectionString()
        {
            var builder = new ConfigurationBuilder();
            builder.AddJsonFile("appsettings.json", optional: false);

            var configuration = builder.Build();
            // Get the connection string located inside the appsettings.json file under the name "DefaultConnection"
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

    }
}

 

2a. And now the appsettings.json file which is used for the connection string. You actually don't have to do it this way, but it's considered better practice and it will help you in the long run. You can place the file everywhere you want. For the simplicity I created it inside the project root.

Write this short code inside:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=efcoretutorial;Uid=root;Pwd="
  }

}

 

Server = the address of the server, in this case localhost
Database = name of the database
Uid = user accessing the database
Pwd = database password, leave empty if none


Important! When you have created the file, right click on it and select properties. Change "Copy To Output Directory" to "Copy always".

O5NeDDs.png

 

3. Create a model class, in this case it's called Account

 

	    
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace EFCoreTutorial
{    
    public class Account
    {
        [Key]
        public int Id { get; set; }

        public string Username { get; set; }
        public string Password { get; set; }
    }
}

4. Let's make a simple registration command.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GTANetworkAPI;

namespace EFCoreTutorial
{
    public class Commands : Script
    {
        [Command("register")]
        public void AccountCmdRegister(Client player, string username, string password)
        {

            RegisterAccount(player, username, password);
            NAPI.Chat.SendChatMessageToPlayer(player, "~g~Registration successful!");

        }

        public static void RegisterAccount(Client client, string username, string password)
        {
            
            // create a new Account object
            var account = new Account
            {
                Username = username,
                Password = password
            };
            
            // Add this account data to the current context
            ContextFactory.Instance.Accounts.Add(account);

            // And finally insert the data into the database
            ContextFactory.Instance.SaveChanges();
            
        }

        
    }
}

 

4a. To check if you are properly connected to the database without going into the game, make a query when a resource starts, for example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GTANetworkAPI;

namespace EFCoreTutorial
{
    public class Main : Script
    {
        [ServerEvent(Event.ResourceStart)]
        public void OnResourceStart()
        {
            var playerCount = ContextFactory.Instance.Accounts.Count();
            NAPI.Util.ConsoleOutput("Total players in the database: " + playerCount);
        }
        
    }
}

 

5. Before we can test the command or the above example, we need to make a migration. Manual migrations are the only way as of EF Core. To use them in our gamemodes which are most often only libraries (.dlls), we need to "trick the system" into thinking our gamemode is executable. The easiest way is to "create" a console application. 


First, open your project properties, ALT + F7.

Change output type to "Console Application"

JvXE5oy.png

 

Save with CTRL + S!

 

Create a new class called Program.cs with the code below:

using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreTutorial
{
    public class Program
    {
        public static void Main(string[] args)
        {
            
        }
    }
}


Yes, that's right. You only need the Main method. It's because the console app approach looks for "Main" as a starting point.

Save and build the project!

 

Now let's make the first migration. Open up the Package Manager Console and type "add-migration FirstMigration" (FirstMigration is only the name). After the migration class has been created, type once again into the console "update-database". The very first migration is now added and inside your database you will find Accounts table:

r7rXxZh.png

 

Note: If there are any errors by this stage, then you are most likely not connected to your Database. This guide will not cover that!

 

6. We are almost done. For server to properly work, it will need runtime dlls.

When you first start RAGEMP client, you will download some runtime files. Those are not enough and you have to take some extra steps.
Go to the "RAGEMP" root folder, then "dotnet" folder and copy everything. Paste it inside the "runtime" folder (RAGEMP\server-files\bridge\runtime). 

When you build your project, it will also give you runtime files. Copy everything from bin/debug/netcoreapp2.2 (default build path) except Bootstrapper.dll , Newtonsoft.Json.dll and everything that starts with YourProjectName (EFCoreTutorial in my case).

YLwjQ4l.png

Paste it once again inside the "runtime" folder (RAGEMP\server-files\bridge\runtime).

 

Finally, open YourProjectName.csproj with notepad and add this line

<CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>

like so:

7phI6ht.png

 

Save and close.

 

7. You are all set up!

From now on you don't have to worry about missing runtimes, errors and whatnot. This is everything Entity Framework Core requires to work properly in RAGEMP.

 

Changelog:

- Added appsettings.json, a better way of handling connection strings. Thanks @horseyhorsey!
- Some clarification

 

 

This was my first tutorial/guide.
Leave feedback and opinions.

Thank you for reading!

 

xForcer

Edited by xForcer
Clarification
  • Like 6
  • Mask 2

Share this post


Link to post
Share on other sites

Worked really well for me, appreciate the tutorial so much!

I expect SQLite to be better and easier for something like a RageMP server (https://www.sqlite.org/whentouse.html), so I mixed your tutorial with this one:
https://docs.microsoft.com/en-us/ef/core/get-started/netcore/new-db-sqlite

which also had some better looking syntax for queries and the like which will probably work with MySQL as well. Make sure to mark your x.db as copy if newer, and it should just work!

  • Like 1

Share this post


Link to post
Share on other sites
On 3/12/2019 at 1:50 AM, hayden392 said:

Worked really well for me, appreciate the tutorial so much!

I expect SQLite to be better and easier for something like a RageMP server (https://www.sqlite.org/whentouse.html), so I mixed your tutorial with this one:
https://docs.microsoft.com/en-us/ef/core/get-started/netcore/new-db-sqlite

which also had some better looking syntax for queries and the like which will probably work with MySQL as well. Make sure to mark your x.db as copy if newer, and it should just work!

Thank you very much!

SQLite could be a better solution, I agree. I haven't looked but you could create an SQLite tutorial so we can have it all here on RageMP.
That would be great! :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.