3

I'd like to log all changes made an SQL Azure database using Entity Framework 4. However, I failed to find a proper solution so far..

So far I can track all entities themself by overriding SaveChanges() and using the ObjectStateManager to retrieve all added, modified and deleted entities. This works fine. Unfortunately I don't seem to be able to retrieve any useful information out of RelationshipEntries. In our database model we some many-to-many relationships, where I want to log new / modified / deleted entries too.

I want to store all changes in an Azure Storage, to be able to follow changes made by a user and perhaps roll back to a previous version of an entity.

Is there any good way to accomplish this?

Edit: Our scenario is that we're hosting a RESTful WebService that contains all business logic and stores the data in the Azure SQL Database. A client must be authenticated as a user with the WebService, and I'd need to store the information which user changed the data.

user1165068
  • 79
  • 6
  • Does SQL Azure have change data capture? – TGnat Feb 28 '12 at 16:01
  • @TGnat According to [this page](http://msdn.microsoft.com/en-us/library/windowsazure/ff394115.aspx) it does not. – user1165068 Feb 28 '12 at 16:08
  • @user1165068: You certainly can get new/modified relationships from the OSM. – Craig Stuntz Feb 28 '12 at 18:34
  • @Craig Stuntz How? I could not find any method to see what foregin keys belong to the relationship. – user1165068 Feb 29 '12 at 07:18
  • @user1165068 They are in the `ObjectStateManager.GetObjectStateEntries()` list, just like any other entities. Relationships are first-class in EF, meaning *the relationships themselves* are entities. – Craig Stuntz Feb 29 '12 at 13:50
  • @Craig Stuntz I know I can retrieve the relationships using that method, but then what? How do I figure out which columns were edited, or what the column values are? The Entity and EntityKey properties are null. CurrentValues just gives me the entities it relates to. – user1165068 Feb 29 '12 at 13:55
  • Relationships can't be edited. They can only be created or removed. Based on the type, you know which column was edited to create or remove the relationship. – Craig Stuntz Feb 29 '12 at 14:04
  • So I'd have to create a manually mapping for all many-to-many relationships, because I don't see a way to figure out what columns the foreign keys are in the relationship table. – user1165068 Feb 29 '12 at 14:06

3 Answers3

3

See FrameLog, an Entity Framework logging library that I wrote for this purpose. It is open-source, including for commercial use.

Even if you don't want to use the library, you can look at the code to see one way of handling logging relationships. It handles all relationship multiplicities.

Particularly, see the code for the private methods logRelationshipChange, and logForeignKeyChange in the ChangeLogger class.

Martin Eden
  • 6,143
  • 3
  • 30
  • 33
2

You can do it with a tracing provider.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • Unfortunately the tracing provider does not work together with HibernatingRhinos Profiler, which is essential for our work. It can't cast _HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledConnection_ into _EFProviderWrapperToolkit.DbConnectionWrapper_... – user1165068 Feb 29 '12 at 11:48
  • [Related StackOverflow question](http://stackoverflow.com/questions/4846743/how-to-get-efprof-and-efproviderwrapper-working-together-in-on-entity-framework) – user1165068 Feb 29 '12 at 11:53
0

You may want to consider just using a database trigger for this. Whenever a value in a table is changed, copy the row to another Archive table. It has worked pretty well for me.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • How would I get the information WHO changed the database using this method? I better extend my initial post by the scenario. :) – user1165068 Feb 28 '12 at 15:51
  • @user1165068: Good question. I usually include an `ActorId` property on the tables that I want to track like this. This means I have to have my data-access code require that the consumer provide the current person's ID on update. It's not a perfect solution because if I forget to do this I could end up recording each change as having been done by the same person who created the item in the first place. – StriplingWarrior Feb 28 '12 at 16:25