EF code first with MariaDb Error - the right syntax to use near 'max) NOT NULL

Question

I use Entity Code First (EF core ver.6) in a project. The migration work correctly on the sql server, but it doesn't work on the MariaDb.

I used the Pomelo.EntityFrameworkCore.MySql provider by these instructions ^,^ and also used the Devart.Data.MySql.EFCore provider by this guide So I could connect to the MariaDb, However, I got this error when the app reach to migration line( context.Database.Migrate();):

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

CREATE TABLE Users ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  Username nvarchar(450) NOT NULL,
  `Password` nvarchar(max) NOT NULL,
  DisplayName nvarchar(max) NOT NULL,
  IsActive bit NOT NULL,
  LastLoggedIn datetime NULL,      
  PRIMARY KEY (Id)
)

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'max) NOT NULL, DisplayName nvarchar(max) NOT NULL, IsActive bit NOT NUL...' at line 4

My Entities (User, Role and UserRole) are as follows:

User.cs

public class User

{
public User()
{
    UserRoles = new HashSet<UserRole>();
}

public int Id { get; set; }
[MaxLength(50, ErrorMessage = "")]
public string Username { get; set; }
[MaxLength(50, ErrorMessage = "")]
public string Password { get; set; }
[MaxLength(50, ErrorMessage = "")]
public string DisplayName { get; set; }
public bool IsActive { get; set; }
public DateTime? LastLoggedIn { get; set; }  
public ICollection<UserRole> UserRoles { get; set; }
}

Role.cs

public class Role
    {
        public Role()
        {
            UserRoles = new HashSet<UserRole>();
        }

        public int Id { get; set; }
        [MaxLength(50, ErrorMessage = "")]
        public string Name { get; set; }

        public ICollection<UserRole> UserRoles { get; set; }
    }

UserRole.cs:

public class UserRole
    {
        public int UserId { get; set; }
        public int RoleId { get; set; }

        public User User { get; set; }
        public Role Role { get; set; }
    }

Interestingly, the database is created and the role and __efmigrationshistory tables are also created, but the user table is not created and the above error occurs.

What did I do wrong?

I saw this answer and also this, However, Shouldn't the conversion of the models to the desired syntax bank be done by the providers? Also, why does this error not appear when creating the role table?

Answer 1

Probably nvarchar(max) comes form the previous existing migrations. You should delete all of the previous migrations and create a new migration based on this new provider. More info