How can I add a check constraint for JSON in Entity Framework?

Question

Adding a JSON CHECK CONSTRAINTfor a table field with

ALTER TABLE [dbo].[Data]  
   ADD CONSTRAINT [JsonData must be formatted as JSON] 
   CHECK  (IsJson([JsonData]) > 0)

works fine, but I want to make it work for Code First.

I have tried the Reverse Engineering Code First, but it does not help me with this problem. Executing a Sql Command with the same code (Seed() method) works very well, but this is not one of the solutions I would like to use:

protected override void Seed(MyContext context)
{
    context
    .Database
    .ExecuteSqlCommand(
        "ALTER TABLE [dbo].[Data]  
            ADD CONSTRAINT [JsonData must be formatted as JSON] 
            CHECK  (IsJson([JsonData]) > 0)");
}

Is there any other way I can add a JSON Check Constraint from Code First?

Londalondon

You can do it with raw sql in code first migration – Buatti

Answer 1

I think that EF don't support any kind of CHECK constraints. The only thing that you can use is migration. See example in: Is it possible to add CHECK constraint with fluent API in EF7?

Dullard

Sorry for such a long time until I marked this as Accepted Answer. – Londalondon

Answer 2

As per the accepted answer, you'll need to add a migration. This is the EF Core syntax:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("ALTER TABLE dbo.Data ADD CONSTRAINT CK_Data_JsonData_MustBeJson CHECK (IsJson(JsonData) = 1);");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("ALTER TABLE dbo.Applications DROP CONSTRAINT CK_Data_JsonData_MustBeJson;");
    }

Lagrange

Answer 3

In EFCore 3+ this is now possible.

(Note: ISJSON is only available for SQL Server 2016+)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        var tableIdentifier = StoreObjectIdentifier.Create(entityType, StoreObjectType.Table);
        foreach (var entityProperty in entityType.GetProperties())
        {
            if (entityProperty.PropertyInfo != null && Attribute.IsDefined(entityProperty.PropertyInfo, typeof(JsonAttribute), inherit: true))
            {
                var tableName = entityType.GetTableName();
                var columnName = entityProperty.GetColumnName(tableIdentifier.Value);
                modelBuilder.Entity(clrType).HasCheckConstraint(
                    name: $"CK_{tableName}_{columnName}_JSON",
                    sql: $"ISNULL(ISJSON({columnName}), 1) = 1"
                );
            }
        }
    }            
}

The JsonAttribute is just a simple marker that I used for convenience:

[AttributeUsage(AttributeTargets.Property)]
public class JsonAttribute : Attribute { }

Usage:

public class MyEntity
{
    public int Id { get; set; }

    [Json]
    public string JsonData { get; set; }
}

Another possible alternative to using attributes would be using your own "convention" (e.g. property that is string and has a "Json" prefix/suffix)

Coldiron

Answer 4

I believe that now (EFCore 3+) you can also use fluent api to declare a json check constraint.

protected override void OnModelCreating (ModelBuilder builder) {  
base.OnModelCreating (builder);

// gets the configurations
builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

// adds cinstraint checks
builder.Entity<Settings>(e => e.HasCheckConstraint("CK_Set_JSONDocument_JSON","JSON_VALID(JSONDocument)" ));
}

Karlotte