I have a database where we are doing "soft deletes". So instead of a row being deleted, and IsDeleted flag is set to true. That way foreign key lookups still work, reporting on historical data still works, etc.

 

The problem comes when I try to look up related data, especially binding.

 

For example, let's say I have a book table and an author table. If hava reference to an author, I can bind to BookList.Count and see how many books are out there. Butr if one of the entries was a mistake, and has been marked as IsDeleted, how do I get the count straight? I know I can do another linq query, but can anyone figure out a better way? Would it be better to add properties on each partial class (ie UndeletedBooks on Author? Is there something EnttyListManager could add?

 

I know how to btrute force this, I'm jsut hoping there's a more elegant solution.

I did a google search on Soft Delete. I would say the top 3 results are worth reading depending on your application's requirement. 

But I would say read this site first since it explores the reasons why we do soft deletes. It also attempts to provide a more elegant solution for each reason.

http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/ - http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/

http://www.udidahan.com/2009/09/01/dont-delete-just-dont/ - http://www.udidahan.com/2009/09/01/dont-delete-just-dont/

I think it makes more sense if you add another state to your Book object that marks it as a "mistake" and you can do a count query that excludes for this "mistake" state.

I prefer either of the following:

• 1.      Two properties: one for all and one for active books
• 2.      A property that returns only active books; a method (“GetAllBooks()”) that returns everything. 

#2 is cleanest but you can’t bind to a method … which is why I usually end up with #1.  

Think about what Author.Books should return in the usual case.  Should it (a) return all books or (b) return active books. 

DevForce by default generates a “Books” property for the (a) interpretation.  Most people want the “Books” property to behave like (b). Therefore, I would: 

1. ·        Rename (in the EDM) the “Books” navigation property to “AllBooks” (or some such convention)
2. ·        Add a custom “Books” property to the Author partial class that returns only active books. 

One possible implementation of that custom property is: 

 public partial class // extends the generated Author class
 {
     /// <summary>Gets the Author’s active Books (books that are not deleted).</summary>
     [Bindable(false)]
     [Display(Name = "Books", AutoGenerateField = false)]
     public RelatedEntityList<Book> Books
     {
         get { return new RelatedEntityList<Book>(
                  this.Books.Where(b=>b.Active == true),
                  PropertyMetadata.Books.GetEntityReference(this)
                  ); }
     }
 }

 

It is no accident that this looks very similar to the code that DevForce generated for Author.AllBooks. 

It is not the only possible implementation. RelatedEntityList<T> is a DevForce internal class … one that, in general, you should not create yourself. The advantage is that this is a “live” list; if you toggle a Book between active and deleted, this list updates automatically (adding or removing a Book automatically). 

Please remember that sub-queries and INCLUDEs must be composed in terms of the AllBooks property.  Neither EF nor DevForce will recognize the Books property.

 

So if Author has Books, and I use the queryinterceptor filter method, querying books will not return the soft deleted rows, but accessing the Author.Books collection will.

 

I thought I remembered the old pre-ef DevForce giving you a way to add a filter clause to a relationship. Wish that was still there. Guess I'll go with a) above, but that is a lot of relationships to go update :(