[Tutorial] Using Entity Framework Core with MySQL

[Machiavelli]

This tutorial will only work if you already have your MySQL + RAGE project set up.

Step 1

• Right-click on your project and select 'Manage NuGet Packages...'.
• Browse for the following packages and make sure you install the same version for all! (this example uses version 2.2.0)
  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Tools
  • Pomelo.EntityFrameworkCore.MySql

Step 2

• Create a Player.cs class with the following code:

Spoiler

using GTANetworkAPI;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFCoreTutorial //change this to your own namespace
{
    public class Player
    {
        [Key] //EF Core will know that Id is the unique identifier
        public int Id { get; set; }

        public string Name { get; set; }

        [NotMapped] //This means that SpawnLocation will not be put into the database. We do this because Vector3 cannot be translated to MySQL
        public Vector3 SpawnLocation { get; set; }
    }
}

 

Step 3

• Create a new class called '<myProject>Context.cs', in this example we will use TutorialContext.cs
• Paste the following code:

Spoiler

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

namespace EFCoreTutorial //change this to your own namespace
{
    public class TutorialContext : DbContext
    {
        private string v;

        public TutorialContext() : this("server=localhost;database=tutorial;user=root;password=test")
        {

        }

        public TutorialContext(string v)
        {
            this.v = v;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("server=localhost;database=tutorial;user=root;password=test", ob => ob.MigrationsAssembly(typeof(TutorialContext).GetTypeInfo().Assembly.GetName().Name));
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Player> Players { get; set; } //Without this line, the Player class will not be translated to MySQL and the context will not contain a list of all players from the database.

    }
}

 

Step 4

• To make things easier for future classes, we will make an interface called IRepository.cs. This is not part of EF Core but it's a nice programming pattern to use with it. Copy-paste the code below into your interface:

Spoiler

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

namespace EFCoreTutorial //change to your own namespace
{
    public interface IRepository<T>
    {
        void Add(T entity);
        void Update(T entity);
        IEnumerable<T> GetAll();
        void Remove(T entity);
        T GetById(int id);
    }
}

 

Step 5

• Create a class called PlayerRepository.cs 
• Now we will implement the interface we just created in our PlayerRepository.cs 

Spoiler

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EFCoreTutorial //change this to your own namespace
{
    public class PlayerRepository : IRepository<Player> //implement the IRepository interface for the Player class
    {
        private readonly TutorialContext _context = new TutorialContext();

        public void Add(Player entity)
        {
            _context.Players.Add(entity);
            _context.SaveChanges();
        }

        public IEnumerable<Player> GetAll()
        {
            return _context.Players;
        }

        public Player GetById(int id)
        {
            return _context.Players.Find(id);
        }

        public void Remove(Player entity)
        {
            _context.Players.Remove(entity);
            _context.SaveChanges();
        }

        public void Update(Player entity)
        {
            _context.Entry(entity).State = EntityState.Modified;
            _context.SaveChanges();
        }

        /* //If for example you want all players from a certain faction, you could do this:
        public IEnumerable<Player> GetByFactionId(int id)
        {
            return _context.Players.Where(x => x.FactionId == id);
        }
        */
    }
}

 

Step 6

• Create a class called Main.cs so we can test our code on ResourceStart event.
• Paste the following code

Spoiler

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

namespace EFCoreTutorial //change ..
{
    public class Main : Script
    {
        [ServerEvent(Event.ResourceStart)]
        public void OnResourceStart()
        {
            using (var context = new TutorialContext())
            {
                // Creates the database if not exists
                context.Database.EnsureCreated();
            }

            //Testdata for tutorial purpose:
            var repo = new PlayerRepository(); // repo can now be used to talk to the database

            if (repo.GetAll().Count() < 1) //if the players table is empty, we insert 3 players
            {

                Player player = new Player()
                {
                    Name = "George"
                };
                Player player2 = new Player()
                {
                    Name = "John"
                };
                Player player3 = new Player()
                {
                    Name = "Donald",
                    SpawnLocation = new Vector3(0, 0, 0)
                };

              //Adding players to the database
                repo.Add(player);
                repo.Add(player2);
                repo.Add(player3);

                NAPI.Util.ConsoleOutput("======== Current players: ========");
                foreach (Player p in repo.GetAll())
                {
                    NAPI.Util.ConsoleOutput("(" + p.Id + ") " + p.Name);
                }

                NAPI.Util.ConsoleOutput("======== Updating George to Michael: ========");
                player.Name = "Michael";
                repo.Update(player);

                foreach (Player p in repo.GetAll())
                {
                    NAPI.Util.ConsoleOutput("(" + p.Id + ") " + p.Name);
                }

                NAPI.Util.ConsoleOutput("======== Deleting John: ========");
                repo.Remove(player2);

                foreach (Player p in repo.GetAll())
                {
                    NAPI.Util.ConsoleOutput("(" + p.Id + ") " + p.Name);
                }

                NAPI.Util.ConsoleOutput("======== End ========");
            }

        }
    }
}

 

Step 7

• Build and run your server
• You may get an error like:

  Quote

  Exception message: Unable to load one or more of the requested types.
  Could not load file or assembly 'Microsoft.EntityFrameworkCore, Version=2.2.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'. The system cannot find the file specified.
  System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EntityFrameworkCore, Version=2.2.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'. The system cannot find the file specified.
  File name: 'Microsoft.EntityFrameworkCore, Version=2.2.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'

  
  This means that in your RAGE runtime folder (D:\RAGEMP\server-files\bridge\runtime) you are missing the necessary dlls.
• Head over to (for example) A:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.entityframeworkcore\2.2.0\lib\netstandard2.0 and copy the needed .dll to the runtime folder of RAGE.
• You might have to do this for a couple of dlls. Just go back to the NuGetFallbackFolder and find the right one.
• If you fixed these errors, this should be the result:

 

 

Step 8 (Extra)

• If you're adding more and more classes and you want to add changes to your database, use the following method:

1. Create your new class (House.cs for example) and go back to your TutorialContext.cs and add public DbSet<House> Houses { get; set; } under Players (which we did in this tutorial).
2. On the top left of your Visual Studio you will see a tab called 'View'. Click on it, go to 'Other Windows' and select 'Package Manager Console'.
3. In the Package Manager Console, type: Add-Migration addedHouse
4. Wait for the console to finish and type: Update-Database
5. Your house class has now been added to the database. Enjoy.

Edited April 8, 2019 by Machiavelli

[Machiavelli]

I just realized there's another EF Core + MySQL tutorial very similar to this one. You might want to check it out:

 

[xForcer]

On 4/8/2019 at 10:10 AM, Machiavelli said:

I just realized there's another EF Core + MySQL tutorial very similar to this one. You might want to check it out:

 

A great tutorial. A bit similiar to my tutorial indeed, but different in some ways though. Keep it up!