Auditing Inserts with Identity columns
Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2717
Printed Date: 15-Oct-2025 at 6:04am
Topic: Auditing Inserts with Identity columns
Posted By: Wokket
Subject: Auditing Inserts with Identity columns
Date Posted: 25-May-2011 at 5:41pm
G'day again guys, I'm chasing some advice about auditing inserts of data where we are using Identity columns in the database for ID's. I've created an EntityServerSaveInterceptor derived class, overrriden ExecuteSave() and am successfully auditing updates to existing data. When it comes to inserts however, we (obviously) cannot get the ID value of the freshly inserted row until after the call to base.ExecuteSave() as we're using Identity columns. I've attempted to call base.ExecuteSave() a second time to insert the Audit records, however they don't seem to be detected as pending entities, the system attempts to insert the original data again. Variants of EntityManager.SaveChanges() fail due to recursive saves not being supported. Can anyone shed some light on whether this is possible? It seems like a pretty common requirement, I'm coming up short atm. Cheers,
|
Replies:
Posted By: robertg
Date Posted: 26-May-2011 at 4:56pm
Wokket,
Is tue audit table in your entity model, with a FK relationship defined back to your data table? It seems that if it was, you should be able to execute the save in a single transaction, since EF will create populate the correct information into the navigation properly.
If that's not the case, could you post your save interceptor here so we can take a look at it?
Thanks,
-- Robert
|
Posted By: Wokket
Date Posted: 26-May-2011 at 5:02pm
G'day Robert, The Audit table is defined as an Entity in our EDMX, but does not have a FKey back to the source table... we have a single audit table for the entire schema, storing the related entity name (GetType().Name) and ID as a non-related int. Our save interceptor currently looks as follows:
/// <summary>
/// This class is hooked into the Save pipeline to allow us to audit changes to data.
/// </summary>
public class AuditingSaveInterceptor : EntityServerSaveInterceptor
{
protected override bool ExecuteSave()
{
var entitiesBeingAdded = EntityManager.FindEntities(EntityState.Added).OfType<Entity>().ToList(); //get a concrete list, not a queryable.
var entitiesBeingUpdated = EntityManager.FindEntities(EntityState.Modified).OfType<Entity>().ToList();
AuditUpdates(entitiesBeingUpdated);
var entitiesBeingDeleted = EntityManager.FindEntities(EntityState.Deleted).OfType<Entity>().ToList();
AuditDeletes(entitiesBeingDeleted);
if (!base.ExecuteSave())
{
return false; //don't go any further if that bit of work failed.
}
//TODO: Audit inserts later on. See http://www.ideablade.com/forum/forum_posts.asp?TID=2717&PID=10825#10825
//AuditInserts(entitiesBeingAdded);
// return base.ExecuteSave(); //doesn't save the new Added audit records, attempts to re-save the items saved above.\
return true;
}
private void AuditDeletes(IEnumerable<Entity> entitiesBeingUpdated)
{
AuditChanges(entitiesBeingUpdated, "Delete");
}
private void AuditUpdates(IEnumerable<Entity> entitiesBeingUpdated)
{
AuditChanges(entitiesBeingUpdated, "Edit");
}
private void AuditInserts(IEnumerable<Entity> entitiesBeingAdded)
{
AuditChanges(entitiesBeingAdded, "Add");
}
private void AuditChanges(IEnumerable<Entity> entitiesBeingUpdated, string auditType)
{
if (Principal == null || Principal.Identity.AuthenticationType == "Anonymous")
{
// we can't audit this without a proper ID to record it against
Debug.Assert(false, "DANGER: Not recording audit information as there's no authentication information available.");
return;
}
var userId = ((HCareIdentity) Principal.Identity).UserID;
foreach (var entity in entitiesBeingUpdated)
{
var audit = EntityManager.CreateEntity<Audit>();
audit.EntityAspect.AddToManager();
audit.AuditTime = DateTime.Now;
audit.AuditType = auditType;
audit.EntityName = entity.GetType().Name;
audit.EntityID = ( long)entity.GetType().GetProperty("ID").GetValue(entity, null); // All our tables have a "ID" column storing the identity value.
audit.ChangeByID = userId;
}
}
}
|
|
Posted By: Wokket
Date Posted: 26-May-2011 at 5:03pm
Apologies for the poor formatting of the code above, the forum seems to eat whitespace.
|
Posted By: Wokket
Date Posted: 02-Jun-2011 at 12:07am
G'day guys, Any further ideas with this? It's temporarily been placed on hold due to us running into the "random null-ref when getting properties" issue noted in http://www.ideablade.com/forum/forum_posts.asp?TID=2714&title=strange-null-reference - http://www.ideablade.com/forum/forum_posts.asp?TID=2714&title=strange-null-reference , which stop occurring as soon as we stop using a custom (UserBase derived) Principal and revert to a purely anonymous connection. I'll be looking to hit it pretty hard early next week, and any further suggestion by then would be very welcome! Cheers
|
Posted By: sbelini
Date Posted: 09-Jun-2011 at 12:06pm
Hi Wokket, Unfortunatelly, this not possible within IdeaBlade. I am adding this to our feature request list. As for a workaround, I'd suggest doing the auditing via a trigger in the DB. Another possibility is using http://msdn.microsoft.com/en-us/library/system.messaging.messagequeue.aspx - MessageQueue . In this case you'd save (i.e. base.ExecuteSave) and add to the queue with the resolved IDs. Then you'd periodically check the queue and perform the saves on the queued audit entities. Regards, Silvio.
|
Posted By: Wokket
Date Posted: 09-Jun-2011 at 1:56pm
G'day Silvio, Thanks for getting back to me on this... I ended up with a variant of your Message Queue approach. We're now creating our audit records client-side as part of our repositories Save method, prior to calling EntityManager.SaveChanges() for updates and deletes, as this avoids the issues with running a custom IPrincipal. We then create Audit records for the inserted entities, and save them with a second call to SaveChanges(). This is obviously non-atomic, and can potentially result in inserted records with no audit information, but that seems unavoidable at this point. Cheers
|
Posted By: katit
Date Posted: 18-Oct-2011 at 5:44pm
Another vote for adding some way to retreive identity values for newly inserted entities
|
Posted By: jradxl
Date Posted: 19-Feb-2012 at 8:46am
Posted By: KitKat
Date Posted: 14-Mar-2013 at 1:09pm
I am slightly confused here. I ended up hear reading the suggested features on the website that linked to this thread as support. Honestly the idea of saving while saving has always rubbed me the wrong way as there has to be a better way. To point out, I use an AuditEntityBase that derived from my EntityBase and has fields for your normal basic audit, CreateUser, CreateDate, ModifyUser and ModifyDate. This offers some basic metrics to show in the program of the create information as well as the last modified information. I go further in an SaveInterceptor to create auditlog records for any entity that has been Modified or Deleted into a shared audit information table with before/after values, keys, and user/date info. This method also updates any entity deriving from AuditEntityBase with the latest modified information. This is all done BEFORE ExecuteSave so it includes the entire operation in one database operation.
"WAIT!, What about the create information for that record." is the answer I normally get from this. But look back and read carefully. EVERY Entity that derives from AuditEntityBase has CreateUser and CreateDate on the record itself. "What about the values?" I then normally get asked. Well the Values are simply the first Before value in any modified auditLog record.
I know this feature has many votes, but it will not be getting mine, and I hope that without more evidence that this is not added. I cannot think and have not seen presented any reason I would want to perform a second save in the middle of the operation of a first save.
As a note, I am far from perfect, and if a use case is presented that would warrant such a behavior, I would support such a feature. My post is here to address this one issue and address the feature in general.
Thank you for your time, Katerina
|
Posted By: stephenmcd1
Date Posted: 14-Mar-2013 at 5:58pm
Sorry to take over this thread with my feature request....but the forums have a much richer editor so it's easier to compose a large response here rather than on User Voice.Hi Katerina, thanks for the thorough response. I'll try to comment on why I think this is,
in fact, a very useful and architecturally sound feature request.
As for auditing/logging, I agree with much of what you
say. An 'AuditEntityBase' base class
seems like a wonderful idea. However, we
are dealing with a legacy database as well as other client applications that we need to
peacefully coexist with. So adding 2
extra columns to hundreds of tables is not something that would really go over
well. Side note: we do have an
interface that contains ModifiedDate and ModifiedUser and do use that in the same way you suggest - but we are only able to do this because those fields already existed in our database so it was an easy one for us to
'do right'. Unfortunately, sometimes a legacy system
forces you to do things that aren't how you would do it on a brand new project
I've already digressed a bit because, as it turns out, our application uses database triggers for
most of our auditing and logging. It's
very straightforward logic that I have no problems doing in a trigger with a
bunch of boilerplate and unchanging SQL. There's not a lot of
complicated business logic and, as a bonus, all our client applications get that
auditing/logging behavior 'for free' without having to 'reinvent the wheel'. Also, updates to our database through
'not-so-official-methods' (e.g. ad-hoc SQL scripts) end up triggering the same
audit/logging logic so there is no way to bypass it.
As you might have guessed, I'm not requesting this feature in order
to perform logging or auditing. Nearly
all of my use cases involve times where I need to know the auto-generated
primary key of an entity and use it to update the database in the same
transaction as the original save. The specific cases are a
bit domain-specific, but I’ll try to give some simplified examples. Imagine this kind of scenario:
Imagine we have a table that contains 'email delivery
requests'. The table functions as a
queue where our application can add requests and some other system picks up
those requests and services them by sending emails. So maybe after a new Customer is saved, we
want add a new entity such as the following: new EmailDeliveryRequest{Subject="New
Customer Created", To="Bob", Body="There is a new customer with ID 123 that you
should do something with"}. How would we do this with DevForce...let's look at the existing options: - We add the new EmailDeliveryRequest entity before the save because we don’t know
what the customer's ID will be.
- This isn't the kind of thing that we'd want to put in a trigger because the logic can get
complicated (some new customers go to Bob, other ones go to Sally, etc.). And I very much dislike having non-trivial logic hidden away in SQL where I can't see it, test it, etc.
- We most certainly want the request to be
created within the same transaction so we don’t have to worry about a Customer
getting saved correctly but then something going wrong when we try to save the
EmailDeliveryRequest. So trying to make two SaveChanges calls from the client is not an option.
- We can't just make an association in our model and have the temporary-id fixup logic take care of it since the ID ends up stuffed in a string so DevForce/EF would have no chance at figuring it out.
- Write a stored procedure where we pass Subject/To/Body and it adds a row for us. Then we can call this procedure as part of the save process within the transaction. This ends up being the only viable solution so we go with it. But then we have a bunch of stored procedures that exist solely to do something embarrassingly simple like add 3 values to a table somewhere. Not very fun. A related solution would be to drop down to low-level ADO.NET, raw SQL or something like that to do the INSERTs. Again, not a very fun thing to do.
I'll give another example because I just ran across it in our code. We have the option of using pessimistic locking in our application (for customers that want to use it, that is - I'll happily admit that I'm not a big fan of the feature). So we have a table in our database that holds all the items that are locked. When a new item is saved for the first time, we want to immediately add a row in that table so that we have the item locked (it would be weird for the user to find out that the brand new item that they just saved for the first time is getting forced to read-only because somebody else grabbed a lock on it right after it got saved - which is not that uncommon, in fact, because we have a lot of background tasks that are looking for newly saved items). In this case, we have pretty much the same limitations as before. We need to know the primary key, it's not something I'd want in a trigger, we need it to be in the same transaction to avoid a confusing user experience, making associations in the model are non-trivial since the lock table has columns like "item_type" and "item_id" - DevForce/EF have a hard time understanding that item_id could refer to any possible entity type in our system (which number in the hundreds). As it turns out, we ended up going with a stored procedure in this case as well - even though it might have been nice to just be able to insert a row in that table using a few lines of C# code if we could call SaveChanges.
Hopefully I've explained my use cases. I'm obviously biased, but I think these are legitimate business cases that are hard, if not impossible, to solve in a clean way without support for recursive saves.
Thanks, -Stephen
|
Posted By: KitKat
Date Posted: 15-Mar-2013 at 5:53am
Good morning Stephan, I have not seen either of these use cases before. Thank you for the information into the reasoning. I can almost get behind this feature request now, as I cannot outright come up with a good clean answer to these.
I do see however that these issues are both created by previous design not following some of the pricipal best practices that DevForce is based upon.
While writing this, I did have one idea. It would be nice if we could create a TablePerHierarchy structure where each derived time implemented the same column. Obviously SQL would not be able to enforce the foreign keys, but maybe DevForce could? I will have to test this, maybe even write up my own feature request.
I am sure you have thought about this much more than I and you use case hear seems sound. I do think that this evidence supports the review of this feature request. Maybe a better way will come form the developers or maybe they will ad this. A save within a save still just seems wrong to me, but hey, there have been things that seemed wrong that were required to get software out all the time too.
Thank you, Katerina
|
Posted By: KitKat
Date Posted: 15-Mar-2013 at 5:58am
I realized that I have not specified before. My examples are in the context of a Code First approach. I just cannot give up the flexibility that it provides.
|
Posted By: mgood
Date Posted: 15-Mar-2013 at 7:48am
Stephen, Perhaps something to consider. Even though you don't have FKs in the database for your audit tables, you can still define navigation properties in your model so DevForce can fix up the keys. If you do that, then you can simply add the audit entities the to the save list in the SaveInterceptor and all gets saved in one transaction.
The constraints in your model do not have to exactly match the constraints in the database.
|
Posted By: stephenmcd1
Date Posted: 15-Mar-2013 at 9:21am
Katerina,
I'm glad I was able to explain my use cases. I do agree that some of what I'm doing is because of a legacy system we are forced to work with. But even if I had the power to completely restructure our tables in whatever way I wanted, it still seems a bit extreme to say that I need to go with some sort of TablePerHierarchy structure and change hundreds of tables just because it ends up with better C# code to implement a simple change like adding locking. I'm all for solid design, but it's not an exact science and there always has to be trade-offs and compromises.
And yes, I'm very willing to hear alternate suggestions. I see Marcel has offered some which I'll respond to in a separate post.
One last thing to mention.....you've said that a save within a save sounds weird. But that is exactly the kind of thing that makes Entity Framework so amazing. I can make a single call to "Save" and Entity Framework will end up issuing a bunch of INSERT/UPDATE/DELETEs on my behalf. The power that EF has when it comes to ordering the individual saves, coordinating things when there are relationships, and performing in a single transaction are part of what makes EF so powerful. So even a 'vanilla' SaveChanges call with DevForce ends up triggering multiple individual saves from within the bigger umbrellas Save. In my case, I'm just trying to extend the behavior a bit to include some additional saves apart from what EF does to take care of the cases where EF can't figure it out on its own. Of course, maybe you'll disagree with this....and it is a bit borderline whether this is really a save within a save....but just thought I'd mention it.
Thanks for the lively discourse!
-Stephen
|
Posted By: stephenmcd1
Date Posted: 15-Mar-2013 at 9:32am
Hi Marcel,
In most cases, we do what you suggest and it works fine for us. Talking about the joy of legacy systems, we actually don't have any 'real' FKs in our databases. The Referential Integrity gets enforced by triggers. So even for basic things like Order <-- --> Order Detail, we need to manually add Navigation Properties / Associations so that DevForce/EF knows the two entities are related.
There is one major caveat to that approach for us though. If Entity Framework doesn't think there is a FK in the database, it won't worry about trying to perform INSERT/UPDATE/DELETEs in any particular order. So in the case of something like Order and OrderDetail, Entity Framework might decide to add the OrderDetail row ahead of the Order row. But since we do have triggers enforcing relationships, that will break since obviously an OrderDetail without an Order is not valid.
The cases where I need the recursive save behavior is not in these simple cases. It's in more complicated scenarios like the one I gave where I need to use the auto-generated primary key values to build up a string. In that case, there isn't really a way I can make an association to express that. And in the case of locking, it would get very annoying to have 400 associations coming out of our lock table - our entity model is already hard enough to read! :-)
Thanks for the suggestions,
-Stephen
|
Posted By: katit
Date Posted: 18-Mar-2013 at 6:38pm
I just looked at what I do again and remember why this feature important to us. Putting CreatedOn timestamp on table is OK, but CreatedBy which FK's to user table is kind of overkill. I started designing this way but when you have hundreds of tables and all of them reference User table it becomes ugly. I'd rather keep it in separate table.
With current model I can catch updates and audit that but no luck with "creates"
|
Posted By: KitKat
Date Posted: 19-Mar-2013 at 6:31am
Unless you never plan to delete users, having an actual FK relationship is bad, as no matter what table its in, you now either cannot delete users, or have incomplete history. I simply record the username and link at run-time based on that. That way I can delete users and still maintain 100% complete Audit Logging which is very important for the financial applications we write.
-Katerina
|
Posted By: katit
Date Posted: 19-Mar-2013 at 9:29am
Agree, but I prefer to record user names and keep it "loose" in specific places - like Audit table. I really don't like an idea of keeping user name in each table. I think this is bad design.
|
|