EfSchemaCompare: Error with default values on string properties

[CK159]

• Created a simple dbcontext
• Use code first migrations to create a sql script
• Run that script in a new empty database (Sql Server)
• Run the example EfTestSchemaCompare test
• Get this error

DIFFERENT: Message->Property 'MessageContent', default value sql. Expected = , found = N''

Basically any property on an entity which is configured with some variation of .IsRequired().HasDefaultValue("") is giving this error.

Using .IsRequired().HasDefaultValue("").HasDefaultValueSql("N''") does not result in any error, but I don't think that should be necessary?

I believe that there shouldn't be any reported errors if EF Core is in full control of the database structure, but I could just be misunderstanding what the expected behavior is.

Using Sql Server Latest version of Entity Framework packages (2.2.3) Latest EfCore.TestSupport (1.8.0)

Context:

public class EfTestContext : DbContext
{
    public EfTestContext(DbContextOptions<EfTestContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Message>()
            .ToTable("Message")
            .Property(b => b.MessageContent).IsRequired().HasDefaultValue("");
    }

    public DbSet<Message> Messages { get; set; }
}

public class Message
{
    public int MessageId { get; set; }
    public string MessageContent { get; set; }
}

Unit Test:

[Fact]
public void EfTestSchemaCompare()
{
    //SETUP
    DbContextOptionsBuilder<EfTestContext> builder = new DbContextOptionsBuilder<EfTestContext>();
    builder.UseSqlServer("Server=.\\SQLEXPRESS;Database=EfTest;Integrated Security=true;");

    using (EfTestContext context = new EfTestContext(builder.Options))
    {
        CompareEfSql comparer = new CompareEfSql();

        //ATTEMPT
        //This will compare EF Core model of the database with the database that the context's connection points to
        bool hasErrors = comparer.CompareEfWithDb(context); 

        //VERIFY
        //The CompareEfWithDb method returns true if there were errors. 
        //The comparer.GetAllErrors property returns a string, with each error on a separate line
        hasErrors.ShouldBeFalse(comparer.GetAllErrors);
    }
}

Sql:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190407183740_initial')
BEGIN
    CREATE TABLE [Message] (
        [MessageId] int NOT NULL IDENTITY,
        [MessageContent] nvarchar(max) NOT NULL DEFAULT N'',
        CONSTRAINT [PK_Message] PRIMARY KEY ([MessageId])
    );
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190407183740_initial')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20190407183740_initial', N'2.2.3-servicing-35854');
END;

GO

[JonPSmith]

Hi @CK159,

Thanks for a very detailed issue report. I believe there is a bug in my EfSchemaCompare around string defaults. I will look at that but I am very busy so it won't be for a few weeks.

In the meantime you can use EfSchemaCompare's configuration method IgnoreTheseErrors to let the test pass for now.

[CK159]

I have played around with this a bit more and found some strange behaviors.

It seems as though if you specify the 'default' default value for a property, you get an error, meaning "" for strings, 0 for ints and int-based enums, false for bools. Specifying something else mostly works. Specifying no default value at all works.

Test context class

Error messages:

DIFFERENT: Message->Property 'BoolRequiredDefaultFalse', default value sql. Expected = False, found = <null>
DIFFERENT: Message->Property 'BoolRequiredDefaultFalse', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'BoolRequiredDefaultTrue', default value sql. Expected = True, found = 1
DIFFERENT: Message->Property 'EnumRequiredDefaultOne', default value sql. Expected = One, found = 1
DIFFERENT: Message->Property 'EnumRequiredDefaultZero', default value sql. Expected = Zero, found = <null>
DIFFERENT: Message->Property 'EnumRequiredDefaultZero', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'IntRequiredDefault0', default value sql. Expected = 0, found = <null>
DIFFERENT: Message->Property 'IntRequiredDefault0', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'StringRequiredDefaultEmpty', default value sql. Expected = , found = N''
DIFFERENT: Message->Property 'StringRequiredDefaultSomething', default value sql. Expected = something, found = N'something'
DIFFERENT: Message->Property 'XmlRequiredDefaultEmpty', default value sql. Expected = , found = N''
DIFFERENT: Message->Property 'XmlRequiredDefaultSomething', default value sql. Expected = <something />, found = N'<something />'

[JonPSmith]

Hi @CK159,

I spent quite a bit of time trying to get the default value handling better. I have just released version 2.0.0, which is better but it certainly isn't perfect. The problems are a mixture of EF Core and the fact that the database formats are different, and not the same in all databases.

Have a look at the EfSchemaCompare Limitations page and the Issue015Tests unit tests for more info.

This is the best I can get so I am closing this issue.

[JonPSmith]

Hi @CK159,

I'm working on the EfCore.TestSupport library (previous in my EfCore.TestSupport library) to work with EF Core 8 and it turns out that that Enums and int properties set via to HasDefaultValueSql contains a value that the EfCore.TestSupport expects. I don't quite know what has changed in EF Core, but I suspect the Scaffolder code has been updated.

Version 8 of the EfCore.TestSupport library isn't out yet as there is more to do, but I updated this issue now so that I don't forget. NET 8 isn't out for a while anyway.