Map "insert only" table with EF

Question

I want to save the history of all changes made to my Comment entity / table.

My approach:
- use an "insert only" table
- use a composite PK, made of a db-generated int key and a db-generated DateTime stamp
- use AsNoTracking on the entities

But, suppose it's a navigation property of this entity:

public Article { 
  public int ID { get; set; }
  public string Title { get; set; }
  public string Content { get; set; }
  public virtual Comment Comments { get; set; }
}

So if I call articleFoo.Comments it will get all entities which means all revisions. I only want to get the "latest" one. How would I achieve this?

---UPDATE---

If I use Linq filtering then how would I go about it, or
if I use the sprocs option then how would I do that?
my assumptions are incomplete- I must generate the composite key because for two revision entities, their ID must equal but their updated datetime must differ. So I can't let the db generate those automtically, or can I?

pls consider changing the title to match the problem, that is, record changes to entity or summing like that :) — Quinton Bernhardt, Jan 19 '13 at 19:56
Closing this as an exact duplicate isn't sensible. The linked-to question, and all related ones, are for general DB practices. **I asked how to do this in ENTITY FRAMEWORK**, which has nuances and gotchas that are irrelevant when doing straight-SQL. General db auditing is not what is asked here, I wanted to know specifically about using an "insert-only" table in EF5. — Bobby B, Jan 20 '13 at 16:19
The title is suitable... how do you do "insert only" with the Entity Framework version 5. Auditing is a more general concept. There are many ways to do auditing. I want to use an "insert only" table with EF5. Thanks for your comments though. — Bobby B, Jan 20 '13 at 16:20

Quinton Bernhardt · Answer 1 · 2013-01-19T19:51:01.210

You could go the Db only route by creating a shadow or audit table for Comments. This'll track all inserts, modify and deletes at a field level using a table trigger. I looked around for a tool to make it easier and i found one on CodeProject here - but you could find something better if you search around longer.

If this doesn't float your boat then...

Through EF you could check for new, updated or deleted Comment records before they are committed and add the history information before it's committed.

Using the ObjectContext.SavingChanges you could, for example, check for and record changes and then add your own history records to the context.

In my example my DbContext instance is StackOverflowEntities where i've added and event handler on the underlying ObjectContext.SaveChanges event

    // SavingChanges event handler. 
    private void context_SavingChanges(object sender, EventArgs e) {

        ObjectContext objectContext = sender as ObjectContext;

        if (objectContext != null) {
            foreach (ObjectStateEntry entry in objectContext.ObjectStateManager
                            .GetObjectStateEntries(EntityState.Added | EntityState.Modified | EntityState.Deleted)
                            .Where(et => et.Entity.GetType().Equals(typeof(Comment)))) {

                Comment comment = entry.Entity as Comment;
                Comment_Audit audit = new Comment_Audit {
                    BodyText = comment.BodyText,
                    Commentor = comment.Commentor,
                    ..
                    ..
                    ChangedAt = DateTime.Now,
                    ChangeType = entry.State.ToString()

                };

                StackOverflowEntities.Set<Comment_Audit>().Add(audit);

            }
        }
    }

Now every time a Comment is changed you add an audit record to be saved with it.

Map "insert only" table with EF

1 Answers1