StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFilter all navigation properties before they are loaded (lazy or eager) into memory
text
Body
copied!<p>For future visitors: for EF6 you are probably better off using filters, for example via this project: <a href="https://github.com/jbogard/EntityFramework.Filters" rel="nofollow noreferrer">https://github.com/jbogard/EntityFramework.Filters</a></p> <p>In the application we're building we apply the "soft delete" pattern where every class has a 'Deleted' bool. In practice, every class simply inherits from this base class:</p> <pre><code>public abstract class Entity { public virtual int Id { get; set; } public virtual bool Deleted { get; set; } } </code></pre> <p>To give a brief example, suppose I have the classes <code>GymMember</code> and <code>Workout</code>:</p> <pre><code>public class GymMember: Entity { public string Name { get; set; } public virtual ICollection<Workout> Workouts { get; set; } } public class Workout: Entity { public virtual DateTime Date { get; set; } } </code></pre> <p>When I fetch the list of gym members from the database, I can make sure that none of the 'deleted' gym members are fetched, like this:</p> <pre><code>var gymMembers = context.GymMembers.Where(g => !g.Deleted); </code></pre> <p>However, when I iterate through these gym members, their <code>Workouts</code> are loaded from the database without any regard for their <code>Deleted</code> flag. While I cannot blame Entity Framework for not picking up on this, I would like to configure or intercept lazy property loading somehow so that deleted navigational properties are never loaded. </p> <p>I've been going through my options, but they seem scarce:</p> <ul> <li><a href="https://stackoverflow.com/questions/7044940/navigation-property-to-soft-deleted-entity">Going to <code>Database First</code> and use conditional mapping for every object for every one-to-many property</a>. </li> </ul> <p>This is simply not an option, since it would be too much manual work. (Our application is huge and getting huger every day). We also do not want to give up the advantages of using Code First (of which there are many)</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/data/jj574232.aspx" rel="nofollow noreferrer">Always eagerly loading navigation properties</a>. </li> </ul> <p>Again, not an option. This configuration is only available per entity. Always eagerly loading entities would also impose a serious performance penalty.</p> <ul> <li>Applying the Expression Visitor pattern that automatically injects <code>.Where(e => !e.Deleted)</code> anywhere it finds an <code>IQueryable<Entity></code>, as described <a href="https://stackoverflow.com/questions/17532393/use-expressionvisitor-to-exclude-soft-deleted-records-in-joins">here</a> and <a href="https://stackoverflow.com/questions/12760933/expressionvisitor-soft-delete">here</a>. </li> </ul> <p>I actually tested this in a proof of concept application, and it worked wonderfully. This was a very interesting option, but alas, it fails to apply filtering to lazily loaded navigation properties. This is obvious, as those lazy properties would not appear in the expression/query and as such cannot be replaced. I wonder if Entity Framework would allow for an injection point somewhere in their <code>DynamicProxy</code> class that loads the lazy properties. I also fear for for other consequences, such as the possibility of breaking the <code>Include</code> mechanism in EF. </p> <ul> <li>Writing a custom class that implements ICollection but filters the <code>Deleted</code> entities automatically.</li> </ul> <p>This was actually my first approach. The idea would be to use a backing property for every collection property that internally uses a custom Collection class:</p> <pre><code>public class GymMember: Entity { public string Name { get; set; } private ICollection<Workout> _workouts; public virtual ICollection<Workout> Workouts { get { return _workouts ?? (_workouts = new CustomCollection()); } set { _workouts = new CustomCollection(value); } } } </code></pre> <p>While this approach is actually not bad, I still have some issues with it:</p> <ul> <li><p>It still loads all the <code>Workout</code>s into memory and filters the <code>Deleted</code> ones when the property setter is hit. In my humble opinion, this is much too late.</p></li> <li><p>There is a logical mismatch between executed queries and the data that is loaded. </p></li> </ul> <p>Image a scenario where I want a list of the gym members that did a workout since last week:</p> <pre><code>var gymMembers = context.GymMembers.Where(g => g.Workouts.Any(w => w.Date >= DateTime.Now.AddDays(-7).Date)); </code></pre> <p>This query might return a gym member that only has workouts that are deleted but also satisfy the predicate. Once they are loaded into memory, it appears as if this gym member has no workouts at all! You could say that the developer should be aware of the <code>Deleted</code> and always include it in his queries, but that's something I would really like to avoid. Maybe the ExpressionVisitor could offer the answer here again.</p> <ul> <li>It's actually impossible to mark a navigation property as <code>Deleted</code> when using the CustomCollection. </li> </ul> <p>Imagine this scenario:</p> <pre><code>var gymMember = context.GymMembers.First(); gymMember.Workouts.First().Deleted = true; context.SaveChanges();` </code></pre> <p>You would expect that the appropriate <code>Workout</code> record is updated in the database, and you would be wrong! Since the <code>gymMember</code> is being inspected by the <code>ChangeTracker</code> for any changes, the property <code>gymMember.Workouts</code> will suddenly return 1 fewer workout. That's because CustomCollection automatically filters deleted instances, remember? So now Entity Framework thinks the workout needs to be deleted, and EF will try to set the FK to null, or actually delete the record. (depending on how your DB is configured). This is what we were trying to avoid with the soft delete pattern to begin with!!!</p> <p>I stumbled upon an <a href="http://blog.jorgef.net/2010/12/ef-soft-delete.html" rel="nofollow noreferrer">interesting blog</a> post that overrides the default <code>SaveChanges</code> method of the <code>DbContext</code> so that any entries with an <code>EntityState.Deleted</code> are changed back to <code>EntityState.Modified</code> but this again feels 'hacky' and rather unsafe. However, I'm willing to try it out if it solves problems without any unintended side effects. </p> <hr> <p>So here I am StackOverflow. I've researched my options quite extensively, if I may say so myself, and I'm at my wits end. So now I turn to you. How have you implemented soft deletes in your enterprise application?</p> <p>To reiterate, these are the requirements I'm looking for:</p> <ul> <li>Queries should automatically exclude the <code>Deleted</code> entities on the DB level</li> <li>Deleting an entity and calling 'SaveChanges' should simply update the appropriate record and have no other side effects.</li> <li>When navigational properties are loaded, whether lazy or eager, the <code>Deleted</code> ones should be automatically excluded.</li> </ul> <p>I am looking forward to any and all suggestions, thank you in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload