Efcore: MinLengthAttribute and StringLength.MinimumLength data annotations should create CHECK constraint

[SetTrend]

Currently, EF Core ignores the MinimumLength property of the StringLengthAttribute data annotation and the MinLengthAttribute data annotation.

Using empty strings in NOT NULL data columns often is a cause for confusion, moreover, a strict string length provides for a more robust database for items like SSNs, SEPA IBANs etc.

So, I request for EF Core to support both, the MinimumLength property of StringLengthAttribute and MinLengthAttribute, and create a corresponding CHECK(Len(ColumnName) >= MinimumLength) constraint if MinimumLength is greater than 0 (same for MinLengthAttribute, resp.).

[roji]

Possible candidate for https://github.com/efcore/EFCore.CheckConstraints

[SetTrend]

If you're already having a solution at the ready that would be marvellous!

What does your code do? Would you mind to elaborate?

I forked EF Core two days ago, too, and drilled down to probably adding a synthesized CheckConstraint using AddCheckConstraintOperation to the migration operations collection when either StringLengthAttribute or the MinLengthAttribute are provided.

I also added a MinLength property to column definitions, PropertyBuilder, IConventionPropertyBuilder, ProviderConventionSetBuilder etc. pp.

So far, CSharpMigrationOperationGenerator::Generate takes a IReadOnlyList<MigrationOperation> which keeps me from adding a synthesized CheckConstraint.

Today, my plan was up to discuss a reasonable approach here. But if you've already have something at hand that'd be great.

[roji]

@SetTrend EFCore.CheckConstraints is a planned EF plugin, which would add various check constraints; since not everyone wants these constraints (and they have a slight perf impact in the database), it feels better to have this as an optional plugin. The plugin as it currently is adds check constraints for enums, but there are ideas to add other types as well (e.g. https://github.com/efcore/EFCore.CheckConstraints/issues/6). It seems that your proposal could be a good fit there.

I don't have any code doing this yet. Implementation-wise, I don't think there's any need to add MinLength to anything; you'd simply add a convention inheriting from PropertyAttributeConventionBase (see MaxLengthAttributeConvention for an example), and call AddCheckConstraint on the property's entity type.

One difficulty is that the function for getting the string length varies across databases (e.g. LEN in SQL Server, LENGTH in PostgreSQL). This convention would have to generate the right SQL for the constraint.

@AndriySvyryd @ajcvickers any objections to moving this issue to https://github.com/efcore/EFCore.CheckConstraints?

[SetTrend]

Sounds great!

If that's all that's necessary to support minimum string length, migrations and fluent API, that'd be even better.

I just checked in my current state of investigation (it's not compiling, it's WIP), just in case it may be of any help: https://github.com/dotnet/efcore/compare/release/5.0...SetTrend:min-length

[bricelam]

(Duplicate of https://github.com/dotnet/efcore/issues/15410)

[SetTrend]

Just to make sure, let me pose this question:

So, a column's minimum length is _always_ some sort of CHECK constraint, even with NoSQL databases, like Cosmos DB?

I'm asking this because the concept of utilizing a CHECK constraint turns the abstract notion of a column's minimum length declaration (StringLengthAttribute/MinLengthAttribute) into an implementation specific detail.

So, is it safe to assume that EFCore.CheckConstraints will support
StringLength(max, MinimumLength = min) / MinLength(min) and turn them into a corresponding CHECK constraint?

[ajcvickers]

@SetTrend That's the idea, yes. Closing this here as we're tracking it at https://github.com/efcore/EFCore.CheckConstraints/issues/5

[roji]

@SetTrend

So, a column's minimum length is always some sort of CHECK constraint, even with NoSQL databases, like Cosmos DB?

Note that within EF Core, CHECK constraints are a relational concept, so they don't exist for NoSQL databases. This doesn't mean that something similar couldn't be implemented for Cosmos, if it supports this kind of validation.