EFCore.CheckConstraints

KO | EN

gitlite — search

#hacktoberfest #python #python3 #typescript #graphql #java #golang #matlab #machine-learning #gui #react #docker

EFCore.CheckConstraints

★ 382 Open GitHub ↗

An Entity Framework Core plugin to automatically add check constraints in various situations

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Check constraints for Entity Framework Core

[![Nuget](https://img.shields.io/nuget/v/EFCore.CheckConstraints)](https://www.nuget.org/packages/EFCore.CheckConstraints)

Many databases support something called "check constraints", which allow you to define arbitrary validation for the rows of a table. Think about it like a boring column unique constraint, but on steroids - you can specify that every customer in your table must be either over 18, or have the "parents' permission" bit on. Or whatever makes sense. Just run with it.

Entity Framework Core allows you to specify check constraints in SQL - this helps tighten your data model and ensure that no inconsistent or invalid ever makes it into your precious tables. However, EF does not implicitly generate check constraints for you, even though in some cases it could; this is because check constraints do have a performance cost, and they're not for everyone. This plugin allows you to opt into some constraints - just activate it and they'll automatically get created for you.

The first step is to install the [EFCore.CheckConstraints nuget package](https://www.nuget.org/packages/EFCore.CheckConstraints). Then, choose the constraints you want from the below.

## Validation constraints

.NET comes with some [built-in validation attributes](https://docs.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations), which you can use to declaratively enforce certain constraints on properties. These are typically used by web frameworks such as ASP.NET to validate data provided by users - but we can enforce them in the database as well:

```c#
public class Blog
{
    public int Id { get; set; }
    [Range(1, 5)]
    public int Rating { get; set; }
    [MinLength(4)]
    public string Name { get; set; }
    [StringLength(100, MinimumLength = 1)]
    public string Required { get; set; }
    [Phone]
    public string PhoneNumber { get; set; }
    [CreditCard]
    public string CreditCard { get; set; }
    [EmailAddress]
    public string Email { get; set; }
    [Url]
    public string Address { get; set; }
    [RegularExpression("^A")]
    public string StartsWithA { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(...)
            .UseValidationCheckConstraints();
}
```

This will cause the following table to be created in PostgreSQL:

```sql
CREATE TABLE "Blogs" (
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "Rating" integer NOT NULL,
    "Name" text NULL,
    "PhoneNumber" text NULL,
    "CreditCard" text NULL,
    "Email" text NULL,
    "Address" text NULL,
    "StartsWithA" text NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id"),
    CONSTRAINT "CK_Blogs_Address_Url" CHECK ("Address" ~ '^(http://|https://|ftp://)'),
    CONSTRAINT "CK_Blogs_CreditCard_CreditCard" CHECK ("CreditCard" ~ '^[\d- ]*$'),
    CONSTRAINT "CK_Blogs_Email_EmailAddress" CHECK ("Email" ~ '^[^@]+@[^@]+$'),
    CONSTRAINT "CK_Blogs_Name_MinLength" CHECK (LENGTH("Name") >= 4),
    CONSTRAINT "CK_Blogs_Required_MinLength" CHECK (LENGTH("Required") >= 1),
    CONSTRAINT "CK_Blogs_PhoneNumber_Phone" CHECK ("PhoneNumber" ~ '^[\d\s+-.()]*\d[\d\s+-.()]*((ext\.|ext|x)\s*\d+)?\s*$'),
    CONSTRAINT "CK_Blogs_Rating_Range" CHECK ("Rating" >= 1 AND "Rating" <= 5),
    CONSTRAINT "CK_Blogs_StartsWithA_RegularExpression" CHECK ("StartsWithA" ~ '^A')
);
```

Most of the attributes make use of database regular expressions.

For SQL Server databases with compatibility level 160 or lower, this requires some initial setup - [follow these docs](https://www.red-gate.com/simple-talk/sql/t-sql-programming/tsql-regular-expression-workbench).

For SQL Server databases with compatibility level 170 or higher, configure EF Core to use this compatibility level:

```c#
builder.Services.AddDbContext<MyDbContext>(options =>
    options.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(170)));
```

> Note that processing complex regular expressions does have a cost, so consider performance before turning these constraints on for write-intensive applications.

To disable generating regular expression constraints from the corresponding data annotation attributes, set `UseRegex` validation check constraint option to `false`:

```c#

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .UseValidationCheckConstraints(options => options.UseRegex(false));
}
```

## Enum constraints

When you map a .NET enum to the database, by default that's done by storing the enum's underlying int in a plain old database int column (another common strategy is to map the string representation instead). Although the .NET enum has a constrained set of values which you've defined, on the database side there's nothing stopping anyone from inserting any value, including ones that are out of range.

Activate enum check constraints as follows:

```c#
public class Order
{
    public int Id { get; set; }
    public OrderStatus OrderStatus { get; set; }
}

public enum OrderStatus
{
    Active,
    Completed
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .UseEnumCheckConstraints();
}
```

This will cause the following table to be created:

```sql
CREATE TABLE [Order] (
    [Id] int NOT NULL IDENTITY,
    [OrderStatus] int NOT NULL,
    CONSTRAINT [PK_Order] PRIMARY KEY ([Id]),
    CONSTRAINT [CK_Order_OrderStatus_Enum_Constraint] CHECK ([OrderStatus] IN (0, 1))
);
```

The added CHECK constraint allows only 0 and 1 to be stored in the column, ensuring better data integrity.

## Discriminator constraints

EF Core allows you to map a .NET type hierarchy to a single database table; this pattern is called Table-Per-Hierarchy, or TPH. When using this mapping pattern, a *discriminator* column is added to your table, which determines which entity type is represented by the particular row; when reading query results from the database, EF will materialize different .NET types in the hierarchy based on this value. You can read more about TPH and discriminators in the [EF docs](https://docs.microsoft.com/ef/core/modeling/inheritance).

In the typical case, your hierarchy will have a closed set of .NET types; but as with enums, the database discriminator column can contain anything. If EF encounters an unknown discriminator value when reading query results, the query will fail. You can instruct the plugin to create check constraints to make sure this doesn't happen:

```c#
public class Parent
{
    // ...
}

public class Sibling1 : Parent
{
    // ...
}

public class Sibling2 : Parent
{
    // ...
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .UseDiscriminatorCheckConstraints();
}
```

This will cause the following table to be created:

```sql
CREATE TABLE [Parent] (
    [Id] int NOT NULL IDENTITY,
    [Discriminator] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Parent] PRIMARY KEY ([Id]),
    CONSTRAINT [CK_Parent_Discriminator_Constraint] CHECK ([Discriminator] IN (N'Parent', N'Sibling1', N'Sibling2'))
);
```

## I want them all!

In love with check constraints? Simply specify `UseAllCheckConstraints` to set everything up.

## Important note

This is a community-maintained plugin: it isn't an official part of Entity Framework Core and isn't supported by Microsoft in any way.

EFCore.CheckConstraints

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README