Ormlite support for Soft Delete (record status flags)

ServiceStack.OrmLite
1

Sorry if this has been answered, but I didnt get any hits on stackoverflow or this forum. I am looking at a system where I need to implement soft deletes. In the past, in dapper, i wrote some extension crud methods to cater for this (e.g. if Table Implements ISoftDeleteable, by default select only where RecordState = ACTIVE). This “works” , but you still end up writing a lot of code to explicitly handle the softdelete states in various places as more complex queries are required.

Is there any support or recommended pattern for soft deletion when using ServiceStack.ormlite? I have seen (and once, implemented) this logic in EntityFramework, whereby logic would (behind the scenes) append filters to queries to limit out the deleted items, but I havent come across anyone using this same idea in SS?

2

OrmLite doesn’t include any notion of Soft deletes and there’s no filter you can use to you modify every SELECT query performed.

I’d probably do something like add an extension method to SqlExpression<T> which appends the condition to an SqlExpression, e.g:

public static class SqlExpressionExtensions
{
    public static SqlExpression<T> OnlyActive<T>(this SqlExpression<T> q)
        where T : ISoftDelete
    {
        return q.Where(x => x.IsDeleted != true);
    }
}

Then just stick to APIs that accept an SqlExpression which you can use to chain it at the end, e.g:

var results = db.Select(db.From<Table>().OnlyActive());
var result = db.Single(db.From<Table>().Where(x => x.Name == "foo").OnlyActive());

I’ve just added an example of this in SoftDeleteUseCase.cs.

3

I’ve added some filters to SqlExpression<T> to provide a couple of options that makes this a bit easier.

You can add a static filter on SqlExpression<T> to append a filter on a typed SqlExpression<T> delegate, e.g:

SqlExpression<Table1>.SelectFilter = q => q.Where(x => x.IsDeleted != true);
SqlExpression<Table2>.SelectFilter = q => q.Where(x => x.IsDeleted != true);

You’ll need to specify it for each table that you want to add this filter to, but then it will be executed for each SqlExpression query so you don’t need to add it via an extension method, e.g:

var results = db.Select(db.From<Table>());
var result = db.Single(db.From<Table>().Where(x => x.Name == "foo"));

This also applies to APIs which take a lambda expression since they use an SqlExpression<T> under the hood, e.g:

var result = db.Single(x => x.Name == "foo");

I’ve also added a generic delegate that applies to all SqlExpressions, but you’ll get access to a IUntypedSqlExpression instead of a SqlExpression<T> but it lets you execute a custom filter for all SqlExpression<T> which you can use to add a filter for all tables implementing ISoftDelete, e.g:

OrmLiteConfig.SqlExpressionSelectFilter = q =>
{
    if (q.ModelDef.ModelType.HasInterface(typeof(ISoftDelete)))
    {
        q.Where<ISoftDelete>(x => x.IsDeleted != true);
    }
};

This works as above where it will apply the filter to all SqlExpression based queries, e.g:

var results = db.Select(db.From<Table>());
var result = db.Single(db.From<Table>().Where(x => x.Name == "foo"));
var result = db.Single(x => x.Name == "foo");

These new filters are available from v4.5.7 that’s now available on MyGet and tests for these new filters have been added to SoftDeleteUseCase.cs

2 Likes
4

I’m trying to implement this pattern, but I’ve been having some difficulty.

It seems like every time I do a factory.Open(connectionName) the expression specified in SqlExpressionSelectFilter is ignored.

I was wondering if there’s any way to set the SqlExpressionSelectFilter in the OrmLiteConnectionFactory.

Many thanks!

-Z

5

Please provide a stand-alone example of what doesn’t work, preferably a runnable example on Gistlyn:
http://gistlyn.com/?gist=0cd558e817f28f77b974c44c3e12ff6f&collection=991db51e44674ad01d3d318b24cf0934

6

So in testing it doesn’t seem to be related to context.

https://gistlyn.com/?gist=a38883f817c50f7d82296954cacce34a&collection=2cc6b5db6afd3ccb0d0149e55fdb3a6a

In this Gistlyn I store three objects in a database then run a couple of different queries.

Only the third one seems to append the QueryFilter.

Not sure if I’m doing something wrong, or if this is the expected behavior.

Thanks!

-Z

7

That’s because the SqlExpressionSelectFilter only applies when you’re using an SqlExpression and OrmLite doesn’t use the overhead of a Typed SqlExpression unless it has to, e.g. it has an expression it needs to query.

API’s that need to filter using a Typed Expression like:

"ToDo with Order 1: {0}".Print(db.Select<Todo>(td => td.Id == 1).Dump());

Will use a SqlExpression behind the scenes, but if there’s no filter it most cases wont, you can force one by passing in the SqlExpression in the Query, e.g:

"ToDo Item 1: {0}".Print(db.Single(db.From<Todo>().Where(x => x.Id == 1)).Dump());
"All Todos: {0}".Print(db.Select(db.From<Todo>()).Dump());
8

Thanks for the explanation!

I guess if I’m going to use this methodology I just have to consider how I retrieve the data I’m looking for.

Thanks!

-Z

9

The unit test for filtering joined tables doesn’t look like it will properly test the join because the main table being selected from has IsDeleted true. It appears to me that the filter is not actually applied on joined tables, only on the main table.

If you change the ModelWithSoftDelete to not be deleted, and leave the ModelWithSoftDeleteJoin as deleted, the expected result for the select should still be null, but the test fails.