entity framework - Stop empty strings at the database level with EF code first -

consider following poco entity entity framework code first:

public class foo {     public int id { get; set; }      [required, stringlength(100)]     public string name { get; set; } } 

which generate 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, understand default behavior of ef convert empty strings null. if explicitly feed empty string validation exception, which perfect. following code throw dbentityvalidationexception:

var f = new foo { name = "" }; context.foos.add(f); context.savechanges(); 

but, problem if have external application accesses database directly, can perform following query and succeeds:

insert dbo.foo(name) values ('') 

the best solution arguably not allow connect directly database , force them through business layer. in reality may not possible. if, say, myself importing external data via ssis package.

my best understanding says applications should set reject bad data at lowest level possible. in case mean @ database level. if creating database old fashioned way, add constraint check (name <> '') , stop dirty data ever being inserted in first place.

is there way ef code first generate constraint me, or other way enforce non-empty-string (minimum length 1) @ database level - preferably using attribute? or recourse add constraint manually in migration?

there minlength attribute not enforce constraint on database level, should add constraint using migration 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 ef generate these codes minlength attribute, i'll give simplified hint here:

1. first mark properties minlength

   public class test {     public int id { get; set; }     [minlength(1)]     public string name { get; set; } } 

2. add minlenghtattribute conventions , provide value, 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 migration 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); 

3. override sqlservermigrationsqlgenerator use convention in order generate 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 addcolumn , createtable operations must add codes altercolumn, droptable , dropcolumns well.

4. register new code generator:

   internal sealed class configuration : dbmigrationsconfiguration<testcontext> {     public configuration()     {         automaticmigrationsenabled = true;         setsqlgenerator("system.data.sqlclient", new extendedsqlgenerator());     } }