ServiceStack's Ormlite Delete not working

Question

I've made up a generic repository to make CRUD operations in a MVC project.

When i try to delete a row from a table that has an identity on SQLServer, the code generated by the Ormlite Delete method and inspected with the profiler doesn't not affect any rows.

This is the Crud operation for the deletion (pretty simple):

    public void Destroy<T>(T entity)
    {
        using (var db = dbFactory.Open())
        {
            db.Delete<T>(entity);
        }
    }

The Type T in my test is represented by the following class:

[Alias("FT_TEST_DEVELOPMENT")]
public class TestTable
{
    [AutoIncrement]
    [PrimaryKey]
    public int ID { get; set; }
    public string DESCR { get; set; }
    public DateTime? TIMESTAMP { get; set; }
    public DateTime DATE { get; set; }
    public decimal PRICE { get; set; }
    public int? QTY { get; set; }
}

And the inspected code corresponds to the following:

exec sp_executesql N'DELETE FROM "FT_TEST_DEVELOPMENT" WHERE "ID"=@ID AND "DESCR"=@DESCR AND "TIMESTAMP"=@TIMESTAMP AND "DATE"=@DATE AND "PRICE"=@PRICE AND "QTY"=@QTY ',
               N'@ID int,@DESCR nvarchar(6),@TIMESTAMP datetime,@DATE datetime,@PRICE decimal(2,0),@QTY int',
               @ID=4,@DESCR=N'SECOND',@TIMESTAMP=NULL,@DATE='2015-06-01 00:00:00',@PRICE=15,@QTY=NULL

When I execute this perfectly sensed statement the server tells me that no row

Disclaimer: as some names where in my native language, I translated them so there may be little grammar error, if it's so, let me note and I'll edit.

UPDATE

The matching row actually EXISTS in the database

SELECT * FROM FT_TEST_DEVELOPMENT WHERE ID= 4 ID DESCR TIMESTAMP DATE PRICE QTY 4 SECOND NULL 2015-06-01 15 NULL

I mean that actually the OrmLite generated code appears to be bugged.

And yes, the ID column is the table's key.

SECOND UPDATE I think I've found the cause:

actually in the WHERE clause the NULL fields are assigned in the way

@TIMESTAMP=NULL

but actually the SQL server will not match this statement, because it expects to receive

WHERE [...] AND "TIMESTAMP" IS NULL [...]

Have you defined ID as a primary key in the table or is it just an IDENTITY? IDENTITY doesn't mark a field as primary key — Panagiotis Kanavos
Kalispera! I've also tried with the [PrimaryKey] attribute, but nothing changed, anyway I'll edit the question, THX — pizzaboy
You need to put [PrimaryKey] there and make sure ID is a primary key on the table. Also, I suspect db.Delete<T>() is a typo and you meant db.Delete<T>(entity). All this should change the statement's WHERE clause to check only the ID field. Also make sure there are rows that match the criteria — Panagiotis Kanavos

mythz mythz · Accepted Answer · 2015-01-15T18:09:51

The way db.Delete() API works has been updated so that NULL fields are moved out of the parameterized queries and appended to the SQL filter so this should now work from v4.0.37+ that's now available on MyGet.

You can also delete rows in OrmLite by PrimaryKey with:

Db.DeleteById<TestTable>(entity.Id);

For generic methods you can use the T.GetId() extension method to get the value of the Id field, i.e:

Db.DeleteById<TestTable>(entity.GetId());

Or to delete using every non null property in the DELETE WHERE criteria, you can use:

Db.DeleteNonDefaults(entity);

ServiceStack's Ormlite Delete not working

2 Answers