check constraint entity framework

Question

IN EF can I add a check constraint so if my CustomerNotes entity has a boolean "FollowUpRequired" property I force user to enter a valid future date in the "FollowUpDate" property?

Kenelm

Answer 1

EF does not support Check Constraints prior to EF Core 3.

That is, you cannot apply an attribute to an entity, and expect it to generate the appropriate SQL to create the Check constrint in the underlying database.

One way around this when using Code First Data Migrations is to generate a migration and add an Sql() line in the Up() function override that is generated.

e.g.

public override void Up()
{
    // Auto generated code here
    // ...

    // YOUR CUSTOM SQL GOES HERE
    Sql("ALTER TABLE foo ADD CONSTRAINT CK_Some_Check CHECK (SomeDate > '20121110 00:00:00.000'");**
}

Also see this question and answer for some further information: Is it possible to express a check constraint?

Exclave

I've updated the answer to indicate where the code goes. For info, I've found an archived version of the original blog post here: archive.li/pmkxH#selection-4371.2-4391.10 – Exclave

So, this answer is not actually correct nowadays :) – Deprived

Answer 2

EF Core 3

To take Ryan's example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeTable>(entity =>
        entity.HasCheckConstraint("CK_SomeTable_SomeColumn", "[SomeColumn] >= X");
}

Docs.

---

EF Core 7+

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Blog>()
        .ToTable(b => b.HasCheckConstraint("CK_Blog_TooFewBits", "Id > 1023"));
}

See this for more details and changes in 7. Docs.

Specific

I may be slow, but it is taking me a while to determine which using directives give access to this method. I have already installed the nuget package Microsoft.EntityFrameworkCore.Relational – Hanger

@Hanger - The docs indicate that it is in this package: Microsoft.EntityFrameworkCore.Relational v3.0.0 and in this namespace: Microsoft.EntityFrameworkCore (and therefore you want the following using Microsoft.EntityFrameworkCore;. If after doing the usual dance of restarting, restoring packages, and git clean -fdx, I'd try clearing out your local nuget cache in %USERPROFILE%\.nuget\packages. If none of that works I have no idea. – Specific

Or this is possible to do via migrations: migrationBuilder.AddCheckConstraint( name: "CK_Name", table: "Table", sql: "([Column] > 1)"); – Seventeen

Since EF 7, this is deprecated in favour of modelBuilder.Entity<SomeTable>(entity => entity.ToTable(t => t.HasCheckConstraint("CK_SomeTable_SomeColumn", "[SomeColumn] >= X"))); – Musclebound