6

So here's my situation:

I'm soft-deleting some rows in a table, using a IsDeleted flag, so that a can keep a trace of my archived data. I do so by overriding the SaveChanges statement in my ObjectContext.

The question is: how can I select only the row(s) that have IsDeleted == false, without having to specify && !IsDeleted in every queries?

Is there a way I can specify this on my context directly?

tkx!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
iPeo
  • 399
  • 2
  • 7
  • 16
  • EF Code-first or Model-first? – StriplingWarrior Jan 12 '12 at 19:43
  • Instead of Flag, use a table. For the deleted table the PK can be the same PK as the main table, with the deleted table's PK a FK to the main table. If you got a record in deleted, then its deleted, if not active. Then its just a simple join. Query performance will be better than using a bit flag. – Jon Raynor Jan 12 '12 at 20:28
  • @JonRaynor: Databases aren't my specialty, but it seems to me that checking a bit flag has got to be a lot less expensive than doing a join, especially since you're usually going to be more interested in knowing which items *aren't* deleted (and therefore aren't present in the deleted table). If the bit flag is hurting query performance, I'd prefer an index over creating a completely separate table. – StriplingWarrior Jan 13 '12 at 16:08
  • @Stripling - On the surface, that looks to be correct. I've asked a followup, here. http://stackoverflow.com/questions/8856684/soft-delete-use-isdeleted-flag-or-separate-joiner-table – Jon Raynor Jan 13 '12 at 20:27

3 Answers3

8

You could define a view over your table and query that view instead:

CREATE VIEW dbo.ActiveData
AS
  SELECT (list of columns)
  FROM dbo.YourTable
  WHERE IsDeleted = 0

And then in your EDMX model, read your data from the ActiveData view instead of the base table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
7

If you right-click an EntitySet in the model viewer, and click "Table Mapping", there is an area where you can "Add a Condition". This should do what you're asking, although you might be better off using a View instead, as per marc_s's suggestion.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • Conditional mapping is correct way to go in EF. Using view as @marc suggested works but it will require additional changes. – Ladislav Mrnka Jan 13 '12 at 08:26
  • How would you do conditional mapping with code-first, without a physical edmx? – danludwig Jan 13 '12 at 18:48
  • @olivehour: http://stackoverflow.com/questions/8161689/entity-framework-conditional-mapping-with-code-first – StriplingWarrior Jan 13 '12 at 19:39
  • @StriplingWarrior thanks, that's basically what we do, although via IQueryable / IEnumerable extension methods rather than directly on the context. – danludwig Jan 13 '12 at 19:46
  • @StriplingWarrior: When you add the condition IsDeleted = false to the entity, the entity can no longer contain IsDeleted in the edmx file. So how do you delete entities if IsDeleted can't be part of the entity? – Bumper May 27 '14 at 01:52
  • @Bumper: I'd personally put a trigger on the table to capture deletion, and then let Entity Framework actually issue a "delete from ..." instruction. – StriplingWarrior May 27 '14 at 04:30
1

This is an old question now, but for anyone new coming here. As of EF 6 you really should be using interceptors for this type of query. It places a query inside of the SQL query as it runs and filters records based on the flag.

See the following for more info:

Soft Deleting Entities using Interceptors

Robert Perry
  • 1,906
  • 1
  • 14
  • 14