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).

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... — Nyegaard, Feb 17 '16 at 06:49

score 19 · Answer 1 · answered Jan 24 '20 at 10:58

19

As of EF Core 3.0, you can use

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

answered Jan 24 '20 at 10:58

Ryan Sparks

1,297
11
12

5

Just in case anyone is wondering you need to install the `Microsoft.EntityFrameworkCore.Relational` NuGet package to use this extension method. – aligray Apr 08 '20 at 09:38
I installed that package but I can't find the right `using` path... – qu1j0t3 Jan 26 '21 at 19:09

score 18 · Accepted Answer · edited Nov 04 '19 at 17:29

18

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);");

edited Nov 04 '19 at 17:29

ANeves

5,951
3
36
63

answered Dec 13 '15 at 18:03

Lukas Kabrt

5,333
4
42
58

score 0 · Answer 3 · answered Jun 02 '19 at 09:57

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");
        }

I don't see how this adds anything beyond what that the accepted answer already explains. Can you make that clearer? — ANeves, Nov 04 '19 at 17:34
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 — Daniel Leiszen, Apr 12 '20 at 23:21

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

3 Answers3

Linked