Is there a way to delete in Sqlite-Net by using a predicate?

Question

I'm using Sqlite-Net ORM and would like to delete all rows from a table that match a specific condition, like:

conn.Table<MyEntity>().Delete(t => t.someProperty == someValue);

There are deletion methods that take either an entity, or a primary key. I can now of course get all entities that match my condition and then call delete for each entity individually but that feels wrong.

Currently I'm using

conn.Execute("DELETE FROM MyEntitiy...")

But I don't like this version. If I rename my class, the hardcoded SQL won't work anymore.

Are there other options I miss out on?

Reagan

Answer 1

I have had a look through the source and do not see anywhere where you can use a predicate to delete. However the following extension method should provide the functionality that you are looking for:

using System.Linq.Expressions;
using SQLite;
using System.Collections.Generic;

public static class DeleteExtensions
{
    public static int Delete<T>(this TableQuery<T> tableQuery, Expression<Func<T, bool>> predExpr)
    {
        BindingFlags flags = BindingFlags.Instance | BindingFlags.NonPublic;
        Type type = tableQuery.GetType();
        MethodInfo method = type.GetMethod("CompileExpr", flags);

        if (predExpr.NodeType == ExpressionType.Lambda) {
            var lambda = (LambdaExpression)predExpr;
            var pred = lambda.Body;

            var args = new List<object> ();

            var w = method.Invoke(tableQuery, new object[] {pred, args});
            var compileResultType = w.GetType();
            var prop = compileResultType.GetProperty("CommandText");
            string commandText = prop.GetValue(w, null).ToString();

            var cmdText = "delete from \"" + tableQuery.Table.TableName + "\"";
            cmdText += " where " + commandText;
            var command = tableQuery.Connection.CreateCommand (cmdText, args.ToArray ());

            int result = command.ExecuteNonQuery();
            return result;
        } else {
            throw new NotSupportedException ("Must be a predicate");
        }
    }
}

However, I am not sure how predictable this would be by using the TableQuery class and I assume thats why you are currently forced to do the delete and updates manually. So test and use at your own risk :)

Nought

That's a partial solution. It still leaves the problem with WHERE SomeHardcodedId=5. – Reagan

I have updated my answer with an extension method which should add the required functionality. – Nought

You should contribute this to the Github repo where Sqlite.Net is hosted! – Reagan

I will make a pull request because it will be faster than using the extension method by not using reflection. – Nought

Answer 2

If you just want to avoid hard-coding in the class name you can use conn.Execute("DELETE FROM " + typeof(T).Name + "...").

Lovable

This wont work unless your class name is also the same name as your tablename For example what if my table is called [Schema.TableName] – Dianthus