根据“已删除”标志过滤LINQ结果(Filtering LINQ results based on “deleted” flag)

根据“已删除”标志过滤LINQ结果(Filtering LINQ results based on “deleted” flag)

我有两个表，如下例所示，在MSSQL数据库中。

PROJECT id startDate endDate STORY id project_id description deleted_flag

在我的应用程序的逻辑中，我不希望用户能够真正“删除”一个对象，但我想给他们这种幻觉。 因此，一旦删除故事，“deleted_flag”将设置为true。

这一切都很好，但是当使用LINQ to SQL时，我遇到了与设计模式相关的问题。

我使用存储库模式，在我的存储库中，我的“FindAll（）”方法包含过滤器Where（s =>！s.deleted_flag），这样当用户检索到Story的列表时，它只显示未删除的。

我遇到的问题是在整个系统中强制执行这个问题=问题是由于LINQ中父对象的可达性，可以通过简单地使用来获取未过滤的集合

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

这将允许调用者甚至查看已删除的对象。

处理这个问题的推荐方法是什么？

AK

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

最满意答案

创建一个与表几乎相同的行模式，但包括Where子句：

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

更改故事类的[Table]属性，以便它使用该视图而不是表。

将INSTEAD OF触发器添加到视图中，以便插入和更新传递到表中（您甚至可以使删除设置标志而不是删除行）。

Linq将其视为与表相同，具有适当的select，update，insert和delete查询，但数据库将根据此视图的定义执行它们。

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.