views:

9

answers:

0

I am using a model-first development approach with Entity Framework, meaning I make schema changes in a designer in Visual Studio, and use the Generate Database from Model command to generate a a SQL script for creating all of the appropriate database tables which match the model.

I am trying to figure out the best (any) approach to automatically filtering on IsDeleted = false with my various entities. I don't want to have to constantly be adding an IsDeleted = false condition to all of my queries, and lazy loading, etc... So for example, ideally I would like to be able to write the following code, and know that it is only return entities which are not logically deleted:

var people = context.People;

So I have attempted to implement a few different approaches to no avail:

1.) Within the designer, add a condition to each entity with IsDeleted = 0. The problem with that is that EF only supports having one mapping per property. If I have a condition that refers to IsDeleted, I can't have an IsDeleted property on my entity any more, which is fine, I thought well why not just map a database stored procedure to the Deleting function to handle the logical delete, and then I wouldn't need an IsDeleted property on the entity. The problem is that Entity Framework deletes all of your conditions and stored procedure mappings in a Model-First scenario anytime you rerun Generate Database from Model.

2.) I tried changing my EF t4 template to filter entities on the context (and if that worked on the nav properties as well). So for example I could update the t4 template from:

public IObjectSet<Person> People
{
    get { return _people ?? (_people = CreateObjectSet<Person>("People")); }
}
private ObjectSet<Person> _people;

to:

public IObjectSet<Person> People
{
    get { return _people ?? (_people = CreateObjectSet<Person>("People").Where("it.IsDeleted = 0")); }
}
private ObjectSet<Person> _people;

The problem is that as soon as you add a WHERE clause to ObjectSet, it becomes an instance of ObjectQuery. Those properties need to return ObjectSet, so that you have access to all of the additional methods that ObjectSet offers in addition to ObjectQuery, such as AddObject, Attach, DeleteObject, etc, etc...

3.) I looked into adding triggers to the DB. This generally goes against model-first development, and adds unnecessary complexity and potential performance issues...