.EFCore - Is it possible to create data annotation validation based on the column max size in the database?

Question

Instead of looking at the database column definition I would like to have a maxlength validation based on the column size in my database, is that possible?

I am asking because some of my columns are varchar and some are nvarchar and it causes sometimes to confusion because for varchar if the max is 100 then you can insert 100 chars, but if it's nvarchar with max of 100 it's possible to insert only 50 chars.

I would like to stop this confusion, is that possible?

like the [`[StringLength]`](https://learn.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations.stringlengthattribute?view=netcore-3.1) attribute? — Progman, Jun 22 '20 at 14:15
Where should the number come from when not from the SQL server and not from the model in C#? Or where do you want to run the validation? What do you imagine how the validation should work in detail? — Progman, Jun 22 '20 at 14:41
What? It should come from the database without mentioning the length. e.g if there is a `Nvarchar(100)` the out put should be `maxsize(50)`, for `varchar(100)` the output should be `maxsize(100)` — Offir, Jun 22 '20 at 14:48
You want a client side (C# code) validation which checks the length of a field by asking the database what the limit of the column is? — Progman, Jun 22 '20 at 14:56
Please [edit] your question to include the code you use to run the data annotation validation as well as the entity/entities you validate (with the attribute used). — Progman, Jun 22 '20 at 16:04

score 1 · Accepted Answer · answered Jun 22 '20 at 16:37

You can use EF to run custom SQL queries to read meta information about a table. The SQL query depends on the actual database you are using. For MySQL I used EXPLAIN table to get the meta information about a table. This information will be read in an entity called ExplainInfo via the EF context you are using.

For validation you can create a new validation class/attribute which extends from ValidationAttribute and override the IsValid(object value, ValidationContext validationContext) method. The ValidationContext parameter is important as we have to inject the current open context into the validator as well as get information about the entity type and property name to validate. We use it to send a query which fetches the information about a column and validate the current value against it.

As a proof-of-concept, the solution might look like this:

(as a remainder, the result of EXPLAIN table looks like this:)

mysql> EXPLAIN Author;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int         | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

First we create an entity for the result of the EXPLAIN query:

public class ExplainInfo
{
    [Key]
    public string Field {get; set;}
    public string Type {get; set;}
}

To use it we have to add a new DbSet entry in the context as usual:

public DbSet<ExplainInfo> ExplainInfo {get; set;}

Then we write our new validation attribute:

public class LimitValidationAttribute : ValidationAttribute
{

    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        MyContext context = (MyContext)validationContext.Items["context"];
        string entityName = validationContext.ObjectType.Name;
        string propertyName = validationContext.MemberName;
        IList<ExplainInfo> metaInfo = context.ExplainInfo.FromSqlRaw($"EXPLAIN {entityName}").ToList();
        ExplainInfo singleExplainInfo = metaInfo.Single(it => it.Field == propertyName);
        string databaseType = singleExplainInfo.Type.ToLowerInvariant();
        if (databaseType.StartsWith("varchar("))
        {
            string regexStr = @"varchar\((\d+)\)";
            Regex regex = new Regex(regexStr);
            Match match = regex.Match(databaseType);
            string limitAsString = match.Groups[1].Value;
            int limit = int.Parse(limitAsString);

            string valueAsString = (string)value;
            if (valueAsString.Length > limit) {
                return new ValidationResult("The given value for the property "+propertyName+" exceed the limit of "+limit+" characters defined in the database");
            }
        }
        return ValidationResult.Success;
    }
}

We will use this validation attribute in our entity:

public class Author
{
    // [...]

    [LimitValidation]
    public string Name {get; set;}
    
    // [...]
}

And finally we will trigger the validation:

using (MyContext context = new MyContext())
{
    Author author = context.Author.First();
    ValidationContext validationContext = new ValidationContext(author, new Dictionary<object, object> {
        {"context", context}
    });
    author.Name += "dummy text which will exceed the limit in the database";
    Validator.ValidateObject(author, validationContext, true);
    context.SaveChanges();
}

This will generate the following validation exception:

The given value for the property Name exceed the limit of 30 characters defined in the database

You have to adjust the IsValid() method for the VARCHAR and NVARCHAR types as well as the query to get the table meta info.

.EFCore - Is it possible to create data annotation validation based on the column max size in the database?

1 Answers1