Comment strings contains “\r\n”

[BenLocal]

DbContext entity property:

entity.Property(e => e.ClassNum)
  .HasMaxLength(50)
  .HasComment("1,2,3\r\n            2,3\r\n            5,6")

then use dotnet ef dbcontext script cmd, the following sql will be generated:

CREATE TABLE `classroomhoststate` (
  `ObjectID` bigint(20) unsigned NOT NULL,
  `ClassNum` varchar(50) NOT NULL COMMENT CONCAT('1,2,3', CHAR(13, 10), '            2,3', CHAR(13, 10), '            5,6'),
  CONSTRAINT `PRIMARY` PRIMARY KEY (`ObjectID`)
) CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;

Execute sql, there will be an error :

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('1,2,3', CHAR(13, 10), ' 2,3', CHAR(13, 10), ' 5,6'' at line 3

[mguinness]

Could be related to PR #893. See MySQL Table Comment Spanning Multiple Lines for explanation.

Also it's worth noting that max comment length is 1024 chars for columns and 2048 chars for tables.

[lauxjpn]

@BenLocal You can use the .DisableLineBreakToCharSubstition() option in your .UseMySql() method, until this is fixed.

[BenLocal]

In database first, I use dotnet ef dbcontext scaffold to create a DBContext, but i don’t know how to pass IMySqlOptions in this command, So temporarily used the following solution:

public class MySqlDesignTimeServicesCustom : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
    {
        serviceCollection.AddEntityFrameworkMySql();
        serviceCollection.AddSingleton<IProviderConfigurationCodeGenerator, MySqlCodeGenerator4Custom>();
        new EntityFrameworkRelationalDesignServicesBuilder(serviceCollection)
            .TryAdd<IAnnotationCodeGenerator, MySqlAnnotationCodeGenerator>()
            .TryAdd<IDatabaseModelFactory, MySqlDatabaseModelFactory>()
            //.TryAdd<IProviderConfigurationCodeGenerator, MySqlCodeGenerator4Custom>()
            .TryAddCoreServices();
    }
}

public class MySqlCodeGenerator4Custom : MySqlCodeGenerator
{
    private readonly MethodInfo _options_func = typeof(MySqlDbContextOptionsBuilder)
        .GetRuntimeMethod("DisableLineBreakToCharSubstition", new Type[] {});

    public MySqlCodeGenerator4Custom([NotNullAttribute] ProviderCodeGeneratorDependencies dependencies, 
        IMySqlOptions options) : base(dependencies, options)
    {
    }

    public override MethodCallCodeFragment GenerateUseProvider(string connectionString, 
        MethodCallCodeFragment providerOptions)
    {
        if (providerOptions == null)
        {
            providerOptions = new MethodCallCodeFragment(_options_func);
        }

        return base.GenerateUseProvider(connectionString, providerOptions);
    }
}

[lauxjpn]

@BenLocal Take a look at Design-time DbContext Creation.

Here is a sample for a console project:

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }

    public class ContextFactory : IDesignTimeDbContextFactory<Context>
    {
        public Context CreateDbContext(string[] args)
            => new Context(new DbContextOptionsBuilder<Context>().Options);
    }
    
    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        public Context(DbContextOptions options)
            : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1567";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(
                    connectionString,
                    serverVersion,
                    options => options.DisableLineBreakToCharSubstition())
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    entity.HasComment(
                        @"This is a multi-line commment.
Works currently only with the DisableLineBreakToCharSubstition option.");
                    
                    entity.HasData(
                        new IceCream { IceCreamId = 1, Name = "Vanilla" },
                        new IceCream { IceCreamId = 2, Name = "Chocolate" },
                        new IceCream { IceCreamId = 3, Name = "Matcha" });
                });
        }
    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
            var contextFactory = new ContextFactory();
            using var context = contextFactory.CreateDbContext(args);

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var iceCreams = context.IceCreams
                .OrderBy(i => i.IceCreamId)
                .ToList();

            Debug.Assert(iceCreams.Count == 3);
            Debug.Assert(iceCreams[0].Name == "Vanilla");
        }
    }
}

After adding a migration and generating a script...

dotnet ef migrations add Initial
dotnet ef migrations script

... the following SQL scripts are generated, depending on whether DisableLineBreakToCharSubstition() was specified or not:

SQL Script without DisableLineBreakToCharSubstition

CREATE TABLE IF NOT EXISTS `__EFMigrationsHistory` (
    `MigrationId` varchar(150) CHARACTER SET utf8mb4 NOT NULL,
    `ProductVersion` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
    CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
) CHARACTER SET utf8mb4;

START TRANSACTION;

ALTER DATABASE CHARACTER SET utf8mb4;

CREATE TABLE `IceCreams` (
    `IceCreamId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
) CHARACTER SET utf8mb4 COMMENT CONCAT('This is a multi-line commment.', CHAR(13, 10), 'Works currently only with the DisableLineBreakToCharSubstition option.');

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (1, 'Vanilla');

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (2, 'Chocolate');

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (3, 'Matcha');

INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
VALUES ('20211123025024_Initial', '5.0.10');

COMMIT;

SQL Script with DisableLineBreakToCharSubstition

CREATE TABLE IF NOT EXISTS `__EFMigrationsHistory` (
    `MigrationId` varchar(150) CHARACTER SET utf8mb4 NOT NULL,
    `ProductVersion` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
    CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
) CHARACTER SET utf8mb4;

START TRANSACTION;

ALTER DATABASE CHARACTER SET utf8mb4;

CREATE TABLE `IceCreams` (
    `IceCreamId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
) CHARACTER SET utf8mb4 COMMENT 'This is a multi-line commment.
Works currently only with the DisableLineBreakToCharSubstition option.';

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (1, 'Vanilla');

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (2, 'Chocolate');

INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (3, 'Matcha');

INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
VALUES ('20211123025024_Initial', '5.0.10');

COMMIT;

[luantranminh]

Hey @lauxjpn, which MySQL version did you run your SQL script on? I ran it on MySQL 8.4.0.

CREATE TABLE `IceCreams` (
    `IceCreamId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
) CHARACTER SET utf8mb4 COMMENT CONCAT('This is a multi-line comment.', CHAR(13, 10), 'Works currently only with the DisableLineBreakToCharSubstitution option.');

but I received the following error:

Query 1 ERROR at Line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('This is a multi-line comment.', CHAR(13, 10), 'Works currently only wit' at line 5

I searched for the use of CONCAT with COMMENT but found none. Could you provide more information on which version supports COMMENT CONCAT('')? Thank you.