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

Ryan Sparks · Answer 1 · 2023-07-27T09:47:09.953

21

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

edited Jul 27 '23 at 09:47

answered Jan 24 '20 at 10:58

Ryan Sparks

1,347
14
16

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
1

This has become obsolete. New syntax is ```modelBuilder.Entity(entity => entity.ToTable(tbl => tbl.HasCheckConstraint(...)));```. – H. de Jonge Jul 19 '23 at 07:46
@H.deJonge thanks for the tip, updated :) – Ryan Sparks Jul 27 '23 at 09:48

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

19

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

6,219
3
39
63

answered Dec 13 '15 at 18:03

Lukas Kabrt

5,441
4
43
58

score 1 · 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