LINQ to SQL : Using ChangeSet for Audit Trail

  • Article

Most of the transactional scenario, you may need to add the transaction to some table for ref. many apps does it for admin perspective.

Problem

You can do it in LINQ to SQL with ChangeSet but the problem is with Inserts. It does not give you the Identity field value until and unless you call SubmitChanges. But after SubmitChages() you cannot get the objects. So the trick is as below,

//Add new Emp

Emp emp1 = new Emp() { Name = "E : "+DateTime.Now.ToString() };

//Add new Dept

Dept dep1 = new Dept() { DeptName = "D : " + DateTime.Now.ToString() };

db.Emps.InsertOnSubmit(emp1);

db.Depts.InsertOnSubmit(dep1);

//Update Employee

Emp eUpd = (from em in db.Emps

            where em.Id == 170

            select em).First();

eUpd.Name = DateTime.Now.ToString();

//Track the changed objects

ChangeSet changedOnes = db.GetChangeSet();

//Insert

/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 * Since we do not get the Identity column value before SubmitChanges()

 * We need to store them in offline List<T> and then find the Id column after commit

 * This is tricky as we cannot get the Object after calling SubmitChanges()

 * +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 */

List<Emp> lstEmp = new List<Emp>();

List<Dept> lstDept = new List<Dept>();

foreach (var obj in changedOnes.Inserts)

{

    Type ty = obj.GetType();

    if (ty.Name == "Emp")

    {

        lstEmp.Add((Emp)obj);

    }

    if (ty.Name == "Dept")

    {

        lstDept.Add((Dept)obj);

    }

}

string sData = "Summary : " + changedOnes.ToString() + "\r\n";

//Track Updates

foreach (var obj in changedOnes.Updates)

{

    Type ty = obj.GetType();

    sData += "Entity Updated : " + ty.Name + "\r\n";

}

//Save the changes

db.SubmitChanges();

//Now find out the Identity Column value

foreach (var sEmp in lstEmp)

{

    sData += String.Format("New Emp Id {0} for {1} ", sEmp.Id.ToString(), "Emp") + "\r\n";

}

foreach (var sDept in lstDept)

{

    sData += String.Format("New Dept Id {0} for {1} ", sDept.DeptId.ToString(), "Dept") + "\r\n";

}

MessageBox.Show(sData);

Namoskar!!!