Is it possible to add CHECK constraint with fluent API in EF7?

Question

Is it possible to add CHECK constraint with fluent API in Entity Framework 7?

I need to acheive something like this:

...
ADD CONSTRAINT CK_SomeTable_SomeColumn CHECK (SomeColumn >= X);

It is ok if solution is provider-specific - I am targeting MsSqlServer only (at least now).

Quietly

It is not check constraint, but I have used the following to enforce only blessed values: public sealed class ProfileStatus { public string Name { get; } public static readonly ProfileStatus Public = new ProfileStatus("Public"); public static readonly ProfileStatus Private = new ProfileStatus("Private"); public static readonly ProfileStatus VerifiedOnly = new ProfileStatus("VerifiedOnly"); private ProfileStatus(string name) { Name = name; } } And I can not get it the format correctly, sorry... – Oshaughnessy

Answer 1

As of EF 7.0.0-rc1, it isn't possible with the fluent API.

You can define the constraint manually in the migration

migrationBuilder.Sql("ALTER TABLE SomeTable ADD CONSTRAINT CK_SomeTable_SomeColumn CHECK (SomeColumn >= X);");

Aubry

Answer 2

EF Core 7.0 +

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

EF Core 3.0 - 6.0

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

Howenstein

Just in case anyone is wondering you need to install the Microsoft.EntityFrameworkCore.Relational NuGet package to use this extension method. – Outfitter

I installed that package but I can't find the right using path... – Loblolly

This has become obsolete. New syntax is modelBuilder.Entity<SomeTable>(entity => entity.ToTable(tbl => tbl.HasCheckConstraint(...)));. – Hoodlum

@H.deJonge thanks for the tip, updated :) – Howenstein

Answer 3

Lukas Kabrt is still valid in 2019 so I had to create a special migration that I will include constraint and its drop:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
alter table AdminObjectReview add CONSTRAINT CK_AdminObjectReview_OnlyOneType CHECK 
( 
      (CASE WHEN AnswerId IS NULL THEN 0 ELSE 1 END)
    + (CASE WHEN QuestionId IS NULL THEN 0 ELSE 1 END)
    + (CASE WHEN CommentId IS NULL THEN 0 ELSE 1 END)
    + (CASE WHEN UserId IS NULL THEN 0 ELSE 1 END)
    + (CASE WHEN TagId IS NULL THEN 0 ELSE 1 END)
     = 1 )
");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("alter table AdminObjectReview  drop CONSTRAINT CK_AdminObjectReview_OnlyOneType");
        }

Gupton

I don't see how this adds anything beyond what that the accepted answer already explains. Can you make that clearer? – Deduct

It does. Extends the accepted answer with a suggestion, that the constraint can / should be dropped in the migrations Down method. +1 because the -1 was unfair – Belcher