CODEWITHSUNDEEP
c#sqllinq
KingCronus
KingCronus

Reputation: 4519

Filtering LINQ results based on "deleted" flag

I have two tables, as per following example, in an MSSQL db.

PROJECT
id
startDate
endDate

STORY
id
project_id
description
deleted_flag

In the logic of my application, I don't want users to be able to truly "delete" an object, but I want to give them that illusion. As a result the "deleted_flag" is set to true once the story is deleted.

This is all well and good, but when using LINQ to SQL I am having a design pattern related issue.

I use the repository pattern, and in my repository for Story objects my "FindAll()" method contains the filter Where(s=>!s.deleted_flag) so that when a list of Story's are retrieved by the user, it only shows the non-deleted ones.

The problem I have, is enforcing this across the entire system = The problem is that due to reachability from the parent object in LINQ it is possible to get the unfiltered collection by simply using

Event e = EventsRepository.FindWithID(1);
var stories = e.Storys;

This would allow the caller to view even the deleted objects.

What would be the recommended way to deal with this?

aK

Upvotes: 3

Views: 495

Answers (2)

Jan
Jan

Reputation: 16038

You have basically two options to enforce that constraint:

  1. Don't query the tables directly but use views insted which filter the deleted rows on the database side. That has the drawback that your application has no chance to retrieve marked records (i.e. from an admin interface).

  2. Don't use the L2S generated navigation properties by removing the associations in your L2Smodel. That method has the drawback, that you have to query related object explicitly through your repository.

Upvotes: 1

Jon Hanna
Jon Hanna

Reputation: 113322

Create a View pretty much the same row schema as the table, but including the Where clause:

Create View vwStory
AS
SELECT id, project_id, description, deleted_flag
WHERE deleted_flag = 0

Change the [Table] attribute on the story class so that it uses that view rather than the table.

Add INSTEAD OF triggers to the view, so that inserts and updates get passed through to the table (you could even make deletes set the flag instead of deleting rows).

Linq will treat it the same as a table, with appropriate select, update, insert and delete queries, but the database will execute them according to this view's definition.

Upvotes: 3

Related Questions