History in database using entity framework

Question

I want to store history in my table.

I have table Employee.

Employee
{
    [Id],
    [name],    
    [phone],    
    [isActive],    
    [createdDate],     
    [closedDate](default '23:59:59 9999-12-31'),     
    [DepartmentId] References Department(id)    
}

When Employee is changed, I retrieve original values by Id and do isActive=False, closedDate=DateTime.Now and then I save modified value as new Employee with modified original values.

void UpdateEmployee(Employee employee)
{
    ContextDB db=new ContextDB();
    var employeeToUpdate=db.Employees.Find(it=>it.Id==employee.Id);
    employeeToUpdate.isActive=false;
    employeeToUpdate.closeDate=DateTime.Now;
    var newEmployee=new Employee
    {
        Name=employee.Name,
        Phone=employee.Phone,
        .... 
    }

    db.Employees.AddObject(newEmployee);

    // How I can do this with EF
    db.Employees.Modify(employeeToUpdate);

    db.SaveChanges();
}

How can I do this? And another question, what I need do if I have reference to another table Department and also want store history in this table. How should I do if changes Department in Employee object.

P.S. I use Self-Tracking Entities.

And what is wrong with your code? – Ladislav Mrnka Jul 14 '11 at 07:51 — Ladislav Mrnka, Jul 14 '11 at 07:51

score 2 · Answer 1 · answered Jul 14 '11 at 07:55

2

It should simply work without calling any Modify. You loaded entity from the database and you modified its fields while it is attached to the context so the context should know about changes and persist them to the database.

What I find totally bad about your architecture is that each change to your employee will result in active record with another Id. So if you are using and relation with employee table, foreign keys will not point to active record any more. If you want to do it this way you should not create a new record for active record but you should instead create a new record for deactivated record.

answered Jul 14 '11 at 07:55

Ladislav Mrnka

360,892
59
660
670

so how i can deactivate record, using Detach from ObjectContext? What does it mean _'if you are using and relation with employee table, foreign keys will not point to active record any more'_ ? thank you for answer. – Konst Fom Jul 14 '11 at 09:46
Do you understand basics of database design? If you have other table which has relation with your Employee table and points foreign key to any record in your Employee table, that foreign key will point to deactivated record after your change. – Ladislav Mrnka Jul 14 '11 at 10:32
I`m confused. If i change employee record by changing field department in this entity (or another field, i don`t see any reference) i want mark `old` entity as isActive=false and create new entity employee with isActive=true. I think so I can keep a history changing employee. Maybe there is a better solution. You can give a simple example, if not difficult. Thank you – Konst Fom Jul 14 '11 at 17:52

History in database using entity framework

1 Answers1

Linked