OrmLite field CHECK CONSTRAINT

ServiceStack.OrmLite
1

Hi,

I cannot figure out how to define field CHECKs with OrmLite.

The scenario I’m dealing with is the following:

this is the POCO

public class LRDCategoria : IHasId
{
        [Alias("IDDCATEGORIA")]
        [AutoIncrement]
        [PrimaryKey]
        public int Id { get; set; }

        [Alias("CODICE")]
        [Required]
        [Index(Unique = true)]
        [StringLength(50)]
        public string Codice { get; set; }
}

this is the requestDTO

[Route("/categorie/{id}", "PUT")]
public class DizionarioCategorieUpdate
{
        public int Id { get; set; }
        public string Codice { get; set; }
}

the service PUT method update the LRDCategoria table

the strange behaviour is about field Codice, if it’s empty (not null but ‘’) OrmLite states one record updated but the table is not updated at all, the field Codice contains the old value

what I would is OrmLite throws an exception that I can catch

how can I deal with this behaviour?

the only solution I found is to declare a CHECK for the table on the field Codice as this: “Datalength([Codice]) > 0” in sql server

but I don’t know how to specify CHECKs with OrmLite

Is this possible?

consider my project can use Oracle, SQL Server and PostgreSQL databases.

Thanks.

2

There isn’t a formal API for adding CHECK Constraints, you can use Db.ExecuteSql() to execute Custom SQL to add it. There’s also Post Create Table Attribute Hooks which will let you execute custom SQL after a table is created.

3

Ok I cannot define CHECKs using attribute.

What about the issue I described?

When the request values field Codice as blank OrmLite update method returns 1 record updated but the record has not been changed. How can I deal with this?

4

I can’t repro this issue, this test is working as expected:

public class LRDCategoria : IHasId<int>
{
    [Alias("IDDCATEGORIA")]
    [AutoIncrement]
    [PrimaryKey]
    public int Id { get; set; }

    [Alias("CODICE")]
    [Required]
    [Index(Unique = true)]
    [StringLength(50)]
    public string Codice { get; set; }
}

public class NoUpdateIssue : OrmLiteTestBase
{
    public NoUpdateIssue() : base(Dialect.SqlServer) {}

    [Test]
    public void Does_update_record()
    {
        using (var db = OpenDbConnection())
        {
            db.DropAndCreateTable<LRDCategoria>();

            db.Insert(new LRDCategoria { Codice = "A" });

            var row = db.Select<LRDCategoria>().FirstOrDefault();

            row.Codice = "";

            db.Update(row);

            row = db.Select<LRDCategoria>().FirstOrDefault();

            row.PrintDump();

            Assert.That(row.Codice, Is.EqualTo(""));
        }
    }
}

Can you provide a stand-alone repro that shows this issue?

5

I’ve added support for Check Constraints in this commit where you can now define Check constraints with the [CheckConstraint] attribute, e.g:

public class LRDCategoria : IHasId<int>
{
        [Alias("IDDCATEGORIA")]
        [AutoIncrement]
        public int Id { get; set; }

        [CheckConstraint("DATALENGTH([Codice]) > 0")]
        [Required]
        [Index(Unique = true)]
        [StringLength(50)]
        public string Codice { get; set; }
}

This change is available from v4.5.7+ that’s now available on MyGet.

FYI you don’t need to use [PrimaryKey] if property name is Id or has [AutoIncrement] attribute.