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

[xForcer]

 

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 or better
- Net Core 2.2
- RageMP C# 0.3.7 
- 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 - Version 2.2.0
• Microsoft.EntityFrameworkCore.Tools - Version 2.2.0
• Pomelo.EntityFrameworkCore.MySql - Version 2.1.4
• Pomelo.EntityFrameworkCore.MySql.Design - Version 1.1.2

Pomelo.EntityFrameworkCore.MySql is a MySQL provider. There are many more providers, but Pomelo's is just fine.

 

How it looks when everything's added:

 

NOTE: As of writing this, you have to use exactly those versions I had screenshot above!

 

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
        {
            // Connection string, more details below 
            private const string connectionString = "Server=localhost;Database=efcoretutorial;Uid=root;Pwd=";

            // Initialize a new MySQL connection with the given connection parameters 
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseMySql(connectionString);
            }

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

 

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

 

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
            };
            
            // When created like this, the context will be immediately deleted AKA disposed. 
            // This will make sure you don't have slowdowns with database calls if one day your server becomes popular
            using (var dbContext = new DefaultDbContext())
            {
              	// Add this account data to the current context
                dbContext.Accounts.Add(account);
                // And finally insert the data into the database
                dbContext.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()
        {
            using (var dbContext = new DefaultDbContext())
            {
                var playerCount = dbContext.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"

 

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:

 

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).

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:

 

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
- Cleaned and updated the code per @Adam's suggestion

 

 

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

Thank you for reading!

 

xForcer

Edited November 7, 2019 by xForcer
Edit

[stonehaens]

Worked for me. Was looking for exactely that. Thanks for the effort!

[hayden392]

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!

[xForcer]

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!

[Loggybuff]

Images are outdated. Some people may fail at the part where they type.

<CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>

Great work. Keep it up!

[xForcer]

On 5/2/2019 at 4:00 PM, Loggybuff said:

Images are outdated. Some people may fail at the part where they type.

<CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>

Great work. Keep it up!

Can you tell me which images exactly so I can update them? Has 

CopyLocalLockFileAssemblies

changed in any way? I haven't been up-to-date recently.

 

Thanks!

[Olliston]

Awesome tutorial, thank you!

Just FLY for anyone trying this, if you are getting this exception or a similar one:

System.IO.FileNotFoundException: Could not load file or assembly "System.Data.Common"

Despite surely having that file in the runtime folder, it is because you are using the wrong runtime DLLs. A week ago, I setup a server using the bridge DLLs from the wiki guide, apparently, they are out of date and cause such an Error. The bride ZIP contained a System.Data.Common.dll that was 2MB, while the one you need is around than 0.9MB.

Don't follow the setup guide on the wiki. Use the DLLs the client generates in the dotnet client folder, described in step 6.

And thank you once again @xForcer for helping me out with this at 3 in the morning. :) 

[Loggybuff]

On 5/8/2019 at 12:37 AM, xForcer said:

Can you tell me which images exactly so I can update them? Has 

CopyLocalLockFileAssemblies

changed in any way? I haven't been up-to-date recently.

 

Thanks!

copylocallock solved the problem for me
but i cant see the png files. the examples you meant to show where to put the code. that is why i said they are outdated

[Zow]

What I did wrong? I can't build

var builder = new ConfigurationBuilder();

I did everything as you said until "save and build"

[poirock055]

3 hours ago, Zow said:

What I did wrong? I can't build

var builder = new ConfigurationBuilder();

I did everything as you said until "save and build"

just put

using Microsoft.Extensions.Configuration;

in your DbContext.cs