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?

3ullShark · Accepted Answer · 2014-02-28 16:19:34Z

5

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 :)

edited Feb 28, 2014 at 16:19

answered Feb 26, 2014 at 22:53

3ullShark

1687 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Krumelur Over a year ago

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

3ullShark Over a year ago

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

Krumelur Over a year ago

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

3ullShark Over a year ago

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

yngling · Accepted Answer · 2017-01-23 15:10:56Z

0

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

answered Jan 23, 2017 at 15:10

yngling

1,4862 gold badges24 silver badges36 bronze badges

1 Comment

JKennedy Over a year ago

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]

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related