Working with SubSonic 'deleted' rows

Question

When loading data with SubSonic (either using ActiveRecord or a collection), only records with IsDeleted set to false will load. How can I show those rows that have been deleted?

For example, deleting an Employee with:

Employee.Delete(1)

Now employee 1 is marked as deleted. Now I want the option to undo the delete and / or show a list of deleted employees, how can I do that? Either it will be undone if the user accidentally deleted the employee, or they want to go to a 'trash' list with previously deleted employees (i.e. only those with IsDeleted set to true).

Edit: Using SubSonic 2.2

Answer 1

ActiveRecord doesn't have this built in. You'll need to set up additional queries for this. You didn't specify 2.2 or 3.0. This is 2.2 syntax.

public EmployeeCollection FetchAll(bool isDeleted)
{
    return new SubSonic.Select().From(Employee.Schema).Where(IsDeletedColumn).IsEqualTo(isDeleted).ExecuteAsCollection<EmployeeCollection>();
}

public EmployeeCollection GetTrashList()
{
    return FetchAll(true);
}

Answer 2

I'm running into the same issue.

I'm working in a project that's using the ActiveRecord scheme. I can retrieve logically deleted records just fine by querying for them specifically.

The problem is that the ActiveRecord generated classes do not have any properties or methods to modify the deleted status of the record.

It should be as simple as setting "IsDeleted = false" but this functionality doesn't seem to exist.

-- Nevermind on this. I regenerated my ActiveRecord class, and now my Deleted column is accessible by calling code. Must've gotten stuck somewhere.

Answer 3

it is easy to show these rows simply by creating a query by hand instead of using the collection loaders

ie.

ProductsCollection col = new ProductsCollection().Load();

becomes

ProductsCollection col = new Select()
                         .From(Tables.Products)
                         .ExecuteAsCollection<ProductsCollection>();

This should load everything for you. Futhermore you can set the options yourself:

ProductsCollection col = new Select()
                         .From(Tables.Products)
                         .Where(Products.Columns.IsDeleted).IsEqualTo(false)
                         .And(Products.Columns.IsDeleted).IsEqualTo(null)
                         .ExecuteAsCollection<ProductsCollection>();

This would load all the nulls (if you forgot to set your default value on your column to false) AND it will also load the falses

Hope this helps

Answer 4

I was running into this problem yesterday with subsonic 3 and decided to alter the T4 templates to "fix" it. I added these definitions for a new function LogicalAll. As an alternative you could change the definitions of All to this but then you would have no way of getting at the deleted records.

public static IQueryable<<#=tbl.ClassName#>> LogicalAll(string connectionString, string providerName) {
            <#if(tbl.HasLogicalDelete()){#>
      var results = GetRepo(connectionString,providerName).GetAll();
      if(results == null)
      {
       return new List<<#=tbl.ClassName#>>().AsQueryable();
      }
      return results.Where(x=> x.<#=tbl.DeleteColumn.CleanName#> == false);
      <#} else {#>
      return GetRepo(connectionString,providerName).GetAll();
      <# } #>
        }

 public static IQueryable<<#=tbl.ClassName#>> LogicalAll() {
        <#if(tbl.HasLogicalDelete()){#>
  var results = GetRepo().GetAll();
  if(results == null)
  {
   return new List<<#=tbl.ClassName#>>().AsQueryable();
  }
  return results.Where(x=> x.<#=tbl.DeleteColumn.CleanName#> == false);
  <#} else {#>
  return GetRepo().GetAll();
  <# } #>
    }