Imagine having a class Department( ID, Name) and Employee ( ID , Name, IDDepartment) . You want to return in the WebAPI the Departments with the Employee . It is simple to wrote this code:
But the problem is with circular references when serializing : The Department has a list of Employees that have a Department that have a list of Employees that …
Solution 1 : Delete/Comment from the EF generated code what you do not need ( in this case , the Department reference that the Employee has)
I tend to quote Fowler: “Usually when we see properties on a class, they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now, we also want to ask these questions about some point in the past when things may have changed. “
For example , we want to track every change that occurs at an Employee ( changing name, salary) and see the previous versions( what was the salary 2 years ago ?)
So what are the software solutions to do an audit log ? First, we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory
Solution 1 : Coding / Event Sourcing
That means code should maintain history himself – i.e. adding to the history table every modifications.
For Entity Framework this can be done easy, by overiding SaveChanges.
I have show to you how easy you can record and play again Entity Framework 6 actions. Also, some possible uses as testing, making demos and record user Sql.
In this video tutorial I will show how to see the sql when a database bug occurs.
Let’s suppose that a department should have the name not null – and , by mistake, we have not prevented the registering of a null in the name of the department
record = newInterceptionRecordOrPlay(@"DemoEmployeeAndDepartment.zip", ModeInterception.Record);
DbInterception.Add(record);
#endregion
We register the null name department and in the DemoEmployeeAndDepartment.zip we see the error
<?xml version="1.0"?> <InterceptionData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <CommandText>INSERT [Department]([NameDepartment]) VALUES (NULL) ; SELECT [Id] FROM [Department] WHERE [Id] = CAST (@@IDENTITY AS int)</CommandText> <NonQuery>0</NonQuery> <ThrownExc> <ExceptionType>System.Data.SqlServerCe.SqlCeException</ExceptionType> <ExceptionMessage>The column cannot contain null values. [ Column name = NameDepartment,Table name = Department ]</ExceptionMessage> </ThrownExc> </InterceptionData>
Source code is available at https://github.com/ignatandrei/EFRecordAndPlay/wiki/
There is also a NuGet package at https://www.nuget.org/packages/EFRecordAndPlay/
Let’s assume you want to register a demo for a application ( Desktop or Web).
You do arrange the database and you make some repetition before. However, the database must be re-arranged the next time you will make the demo.
For this case you can use EF 6 Record and play – it will make a .zip file with containing resultsets and you can make indefinitely the repetition of the demo – without the real database.
I have wrote in an older post ( http://msprogrammer.serviciipeweb.ro/2010/06/28/ef-automatic-history-of-table-and-t4-files-tt-files/ ) how to generate history code for tables . The easy solution was to create a tt file that track for the ObjectContext the SaveChanges for each table that has a “history” in name. the limitation is that , when you raise an sql command such as “update table ” you must load from database a lot of rows for this….
Now I want to show the same thing, but generating triggers in database for that ! I start also from edmx file and with a template stealed from http://forums.asp.net/p/1599616/4083198.aspx ( to have type of fields in the database ) and another stealed and modified from StackOverflow(to generate trigger for after insert , update, delete) I manage to have a solution to generate sql tables and trigger code.
When is that good ?At the beginning stages of a project when the table structure changes by adding a new parameter.
Sample code generated for table Notes(ID, TextNote, PersonID)
The drawback of the code : the user that executes is not always the logged sql server user…
Anyway, what you have to do to use this automatically generated history/audit for tables ?
One of the biggest challenges in programming was write once- GUI everywhere ( Ok, ORM impedance mismatch is another story)
I mean by that re-using the logic from an application in another application. ASP.NET MVC , with the commitment to strongly viewmodels, make me think that it will be now easier to transfer the viewmodels to an console application.
Let’s see the usual Employee-Department and creation.
First the Database :
Then the ViewModel for creating an employeeand for list of employees
Usually the data of the tables should be tracking for who modified it.
Think about inserting/updating/deleting an employee : you must know who did those actions and when. So you create another table, identically as structure, and you add another 3 fields , such as [ModifiedDate](when), [ModifiedBy](who), [ModifiedType] (what : insert, update, delete).
There are several methods to do it :
from database :
you can use triggers and insert data into new table
from programming code – every time you modify an object, you remember to modify the history object with appropiate data.
The drawback with the database approach is that you can not retrieve who done the modifications ( usually the applications connect under a single account and have a roles table)
The drawback with the programming approach is that the programmer must REMEMBER doing so…If he does not(and does not wrote tests for history), you are stuck…
In the following I propose an automatically history – that maps convention over configuration in my template, but it is easy for you to modify.
The solution works with Entity Framework 4.0 and, for more easily spearation of concerns , with POCO generators.
Let’s say you have the following tables :
As you see we have a Employee and a employee_history, an Department and Department_history
The conventions are:
the history table name = “object” table name + “_history” suffix
the history table fields = “object” table name fields +[ModifiedDate], [ModifiedBy], [ModifiedType]
(if you change those conventions , please change the modelhistory.tt file)
If you want to see in action , please download code history and do the following
1. create database tests
2. run history.sql
3. run project
4. if necessay, re-create the model1.edmx with the same name and replace the console application app.config with the new connection string
After works, please add any fields to department table and to department_history table(same field names/type) . Re-compile the application and modify the new field in department. You will see the modifications in the department_history table.
Ok,now how we do the magic :
We create two new tt file that points to the model.edmx .
The first one ModelHistory.tt , takes care of creating the constructor for history entities by taking a parameter from the original entity :
Ok, and then how to create the history entity ? I wish that the POCO template has had an event “Database saving” – but the only thing I can have is SaveChanges from the ObjectContext – so I create a new ObjectContext , derived from the default one that comes with the project, and creates a new history object :
