Don't strip spaces from fixed-width columns in Entity Framework

Question

I'm using Entity Framework 6 with a MySQL database, and it contains a number of fixed-width fields that map to NCHAR columns. In all cases, when I set the value of a fixed-width field, I use a string whose length equals the width of that field. Some of these strings contain trailing spaces. However, when I retrieve these values from the database, the trailing spaces are gone and the strings are that much shorter than they should be. Is there any way to prevent this behavior?

Look at this, it's just the reverse case but maybe can lead you to the solution: http://stackoverflow.com/questions/1879422/linq-puts-unwanted-trailing-spaces-on-strings — Gusman, Jan 08 '16 at 17:09
http://stackoverflow.com/questions/16724574/mysql-select-fields-containing-leading-or-trailing-whitespace — MethodMan, Jan 08 '16 at 17:11

score 0 · Accepted Answer · answered Jan 12 '16 at 20:25

Turns out there's a SQL mode for that. To make it work in Entity Framework, I had to do the following:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using MySql.Data.Entity;

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class MyModel : DbContext
{
    protected internal MyModel() : base("name=MyModel") { }

    public static MyModel Create()
    {
        var model = new MyModel();
        try
        {
            model.Database.ExecuteSqlCommand(
                "SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';");
        }
        catch
        {
            model.Dispose();
            throw;
        }
        return model;
    }

    // other MyModel members
}

public sealed class MyContextFactory : IDbContextFactory<MyModel>
{
    MyModel IDbContextFactory<MyModel>.Create()
    {
        return new MyModel();
    }
}

I then replaced all calls in my codebase to the MyModel() constructor (which had previously been public rather than protected internal) with calls to MyModel.Create().

The reason I had to make a separate Create() static method, instead of just putting the call to ExecuteSqlCommand in the constructor, is that ExecuteSqlCommand breaks Code First Migrations. Since MyModel no longer has a public default constructor, Code First Migrations will instantiate MyContextFactory and call its Create() method, which doesn't call ExecuteSqlCommand. This is okay because, unlike the actual application code, migrations work fine without changing the SQL mode.

One alternative way to do this would be to execute the SQL statement SET GLOBAL sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; at database setup time, perhaps by using the Sql method in a migration. I didn't do this because it affects the entire database server, not just one particular database, and I'm sharing a MySQL server with other people whose code I didn't want to risk breaking.

Don't strip spaces from fixed-width columns in Entity Framework

1 Answers1