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

[Tonytza]

Hello!

Very useful guide! Thank you for this.

I managed to set this up and everything works.

Never used entity framework before but I want to learn it. I extended on your guide, with a Login and SaveAccount:

public static void LoginAccount(Client client, string username, string password)
{
  Account loginAccount = ContextFactory.Instance.Accounts.FirstOrDefault(u => u.Username.Equals(username) && u.Password.Equals(password));

  if (loginAccount != null)
  {
  	// Logged in
    // Here we set player position, health, armor, variables etc
  }
  else
  {
  	client.SendChatMessage("~r~Account doesn't exist");
  }
}

public static void SaveAccount(Client client)
{
  Account account = ContextFactory.Instance.Accounts.FirstOrDefault(u => u.Username.Equals(client.Name));

  if (account != null)
  {
      // Update model data with player's variables

      // Update Data
      ContextFactory.Instance.Accounts.Update(account);

      ContextFactory.Instance.SaveChanges();
  }
  else
  {
      Console.WriteLine("Account doesn't exist, we CANNOT SAVE it!");
  }
}

(The above code is simplified for its scope - I do use BCrypt hash for passwords, if anyone's interested, I can include that part as well)

I'm curios if I'm on the right track? I know the above works but I'm not sure if this is how it's supposed to be done?

Also, do I have to SaveChanges every time I update an entry?

Thanks again!

 

LATER EDIT:

I've encountered some issues with my current system. Every now and then, the server stops (without any errors or anything) and from what I've observed, it has something to do with saving an account. I'm not exactly sure what's going on. (Issue does not occur all the time. There are times when I cannot reproduce it for several tries and there are times when it suddenly occurs out of nowhere)

Considering the large amount of data I'm trying to save at once, could it be that it takes too long and the server freezes? How should I proceed?

Thanks!

Edited May 19, 2019 by Tonytza

[xForcer]

Hello!

I am glad you like my guide!

From a quick look, I can't see a problem in your code. Freezing can be due to your host. If you are saving every now and then and not every second, you should be fine.

You should look elsewhere in your script and try to eliminate one thing by another. 

[Tonytza]

Thanks! 

Another question, regarding entity framework, if you don't mind:

1. The 'ContextFactory.Instance.SaveChanges();' is NOT needed after each Update, correct?

For example, in my above code (SaveAccount), I don't have to savechanges of the context, after each save account, right?

Can I just update data in the context and save the changes at a later point, after I changed several accounts?

Thanks!

[xForcer]

Sorry, I wasn't active here lately.

As far as I know,  calling .Update method begins tracking some entity in the modified state (marks the entity as modified) so when you use .SaveChanges method, it will update that entity.
From little googling myself, you can call .SaveChanges at a later point and the modified entity will be saved because it is in the current context. 

I am currently saving as soon as the entity changes, but I will probably change it to use .Update method.

Beware though, don't save a lot of data all at once because if one fails, all fail, right? So save piece by piece, do logging and retry saving if it fails

If I am wrong, please tell me.

Edited May 29, 2019 by xForcer

[Loggybuff]

On 5/19/2019 at 7:46 PM, Tonytza said:

LATER EDIT:

I've encountered some issues with my current system. Every now and then, the server stops (without any errors or anything) and from what I've observed, it has something to do with saving an account. I'm not exactly sure what's going on. (Issue does not occur all the time. There are times when I cannot reproduce it for several tries and there are times when it suddenly occurs out of nowhere)

Considering the large amount of data I'm trying to save at once, could it be that it takes too long and the server freezes? How should I proceed?

Thanks!

I have also encountered the same problem and it only happens when something is wrong with the database structure. For example,  if I have added new variables to my UserModel like birthday, vehicleCount etc. and havent updated at the database with Migrations/NPM. So basically, if I try to interact with this database in this condition then it closes itself without giving any errors.

Btw, I started implementing using try catch forms, since I have started, It didn't crash once and threw all the exceptions. Maybe this helps

[xForcer]

6 hours ago, Loggybuff said:

I have also encountered the same problem and it only happens when something is wrong with the database structure. For example,  if I have added new variables to my UserModel like birthday, vehicleCount etc. and havent updated at the database with Migrations/NPM. So basically, if I try to interact with this database in this condition then it closes itself without giving any errors.

Btw, I started implementing using try catch forms, since I have started, It didn't crash once and threw all the exceptions. Maybe this helps

Exactly that. Check for migrations, update your database and use TRY/CATCH block. I use them a lot

[Tonytza]

On 6/6/2019 at 6:38 PM, Loggybuff said:

I have also encountered the same problem and it only happens when something is wrong with the database structure. For example,  if I have added new variables to my UserModel like birthday, vehicleCount etc. and havent updated at the database with Migrations/NPM. So basically, if I try to interact with this database in this condition then it closes itself without giving any errors.

Btw, I started implementing using try catch forms, since I have started, It didn't crash once and threw all the exceptions. Maybe this helps

 

Hmm, it's been 2 weeks since I haven't worked on the gamemode. Will try it later, when I get the chance to get back to coding. 

Now that you mentioned, that could be the case, maybe I forgot to migrate or didn't update something properly. Thank you!

 

21 hours ago, xForcer said:

Exactly that. Check for migrations, update your database and use TRY/CATCH block. I use them a lot

Will do from now on. Thanks!

[Adam]

Great tutorial but I see a couple of issues in using a single context to access the database especially in a very competitive multi-threading environment which would make everything run slow and synchronously even if the calls are asynchronous. So, In order to take advantage of connection pooling (and you should), database connections should be as short lived as possible. Create, use and then immediately destroy. here's an example based on the original examples:

 

using System;
// Add the missing usings
  
namespace EFCoreTutorial
{
    public class Account
    {
        [Key]
        public int Id { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }
  
    public class DBCtx : DbContext
    {
        // Account model class created somewhere else
        public DbSet<Account> Accounts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("Server=localhost;Database=mydatabsename;Uid=root;Pwd=mypassword");
        }
    }

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

        [Command("register")]
        public void AccountCmdRegister(Client player, string username, string password)
        {
            // create a new Account object
            var account = new Account
            {
                Username = username,
                Password = password
            };

            using (var ctx = new DBCtx())
            {
                // Add this account data to the current context
                ctx.Accounts.Add(account);

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

            player.SendChatMessage("~g~Registration successful!");
        }
    }
}

 

[xForcer]

12 hours ago, Adam said:

Great tutorial but I see a couple of issues in using a single context to access the database especially in a very competitive multi-threading environment which would make everything run slow and synchronously even if the calls are asynchronous. So, In order to take advantage of connection pooling (and you should), database connections should be as short lived as possible. Create, use and then immediately destroy. here's an example based on the original examples:

 

using System;
// Add the missing usings
  
namespace EFCoreTutorial
{
    public class Account
    {
        [Key]
        public int Id { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }
  
    public class DBCtx : DbContext
    {
        // Account model class created somewhere else
        public DbSet<Account> Accounts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("Server=localhost;Database=mydatabsename;Uid=root;Pwd=mypassword");
        }
    }

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

        [Command("register")]
        public void AccountCmdRegister(Client player, string username, string password)
        {
            // create a new Account object
            var account = new Account
            {
                Username = username,
                Password = password
            };

            using (var ctx = new DBCtx())
            {
                // Add this account data to the current context
                ctx.Accounts.Add(account);

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

            player.SendChatMessage("~g~Registration successful!");
        }
    }
}

 

I was on the edge to use it or not. Some say it's not needed but as you stated in a multi threaded environment with many db calls, immediately disposing is the way to go.
Since this was targeted at beginners you can see why I did the way I did, BUT I guess no harm can be done if I update my tutorial.

Thank you for the feedback, I appreciate it!

[Astroo]

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeLoadException: Could not load type 'System.Runtime.CompilerServices.IAsyncStateMachine' from assembly 'System.Runtime, Version=4.2.0.0, Culture=neutral,

anyone have same?