Stop empty strings at the database level with EF code first

Question

Consider the following POCO entity for Entity Framework Code First:

public class Foo
{
    public int Id { get; set; }

    [Required, StringLength(100)]
    public string Name { get; set; }
}

Which will generate the following table:

CREATE TABLE [dbo].[Foo] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [Name]      NVARCHAR (100) NOT NULL,
    CONSTRAINT [PK_dbo.Foo] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Now, I understand that the default behavior of EF is to convert empty strings to null. So even if I explicitly feed it an empty string I will get a validation exception, which is perfect. The following code will throw a DbEntityValidationException:

var f = new Foo { Name = "" };
context.Foos.Add(f);
context.SaveChanges();

But, the problem is if I have an external application which accesses the database directly, I can perform the following query and it succeeds:

insert into dbo.Foo(Name)
values ('')

The best solution is arguably to not allow anyone to connect directly to the database and force them through a business layer. In reality however this may not always be possible. Especially if, say, I myself am importing external data via an SSIS package.

My best understanding says that applications should be set up to reject as much bad data at the lowest level possible. In this case this would mean the at database level. So if were creating the database the old fashioned way, I would add a constraint to check (Name <> '') and stop dirty data from ever being inserted in the first place.

Is there a way to get EF Code First to generate this constraint for me, or some other way to get it to enforce a non-empty-string (minimum length 1) at the database level - preferably using an attribute? Or is my only recourse to add the constraint manually in a migration?

what is "a migration" ? does it have any specific meaning in your context ? — joedotnot, Apr 17 '19 at 09:04
@joedotnot I mean an EF Migration. https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/ — lc., Apr 17 '19 at 09:05

Taher Rahgooy · Accepted Answer · 2015-08-25T14:10:14.840

There is MinLength attribute but it does not enforce the constraint on database level, you should add this constraint using migration I think.

public partial class test : DbMigration
{
    public override void Up()
    {
        Sql("ALTER TABLE [dbo].[YOUR_TABLE] ADD CONSTRAINT " + 
            "[MinLengthConstraint] CHECK (DATALENGTH([your_column]) > 0)");
    }

    public override void Down()
    {
        Sql("ALTER TABLE [dbo].[YOUR_TABLE] DROP CONSTRAINT [MinLengthConstraint]");
    }
}

You can add sql code generators for EF to generate these codes for MinLength attribute, I'll give you a simplified hint here:

First mark properties with MinLength

public class Test
{
    public int Id { get; set; }
    [MinLength(1)]
    public string Name { get; set; }
}

Add MinLenghtAttribute to conventions and provide the value, which is the Length :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Add(
        new AttributeToColumnAnnotationConvention<MinLengthAttribute, int>(
            "MinLength",
            (property, attributes) => attributes.Single().Length));
}

the generated code for migration will be:

CreateTable(
    "dbo.Tests",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Name = c.String(
                 annotations: new Dictionary<string, AnnotationValues>
                 {
                     { 
                        "MinLength",
                         new AnnotationValues(oldValue: null, newValue: "1")
                     },
                 }),
         })
     .PrimaryKey(t => t.Id);

Override the SqlServerMigrationSqlGenerator to use this convention in order to generate the constraint sql code:

public class ExtendedSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddColumnOperation addColumnOperation)
    {
        base.Generate(addColumnOperation);
        AddConstraint(addColumnOperation.Column, addColumnOperation.Table);
    }

    protected override void Generate(CreateTableOperation createTableOperation)
    {
        base.Generate(createTableOperation);
        foreach (var col in createTableOperation.Columns)
             AddConstraint(col, createTableOperation.Name);
    }
    private void AddConstraint(ColumnModel column, string tableName)
    {
        AnnotationValues values;
        if (column.Annotations.TryGetValue("MinLength", out values))
        {
            var sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT " +
                "[MinLengthConstraint] CHECK (DATALENGTH([{1}]) >= {2})"
                ,tableName, column.Name, values.NewValue);
            Generate(new SqlOperation(sql));
        }
   }
}

the code above contains generation for AddColumn and CreateTable operations you must add codes for AlterColumn, DropTable and DropColumns as well.

Register the new code generator:

internal sealed class Configuration : DbMigrationsConfiguration<TestContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        SetSqlGenerator("System.Data.SqlClient", new ExtendedSqlGenerator());
    }
}

So that's how custom column annotations are supposed to work. This sounds pretty promising, and a whole lot better than having to manually add code to `Up()` and `Down()` - and worse yet remember I put it there. — lc., Aug 25 '15 at 14:29

Stop empty strings at the database level with EF code first

1 Answers1