LINQ-to-SQL Previous values?

  • Thread starter Thread starter Jakob Lithner
  • Start date Start date
J

Jakob Lithner

I have implemented a solution where I use LINQ-to-SQL to map all my objects
to SQL database. Now I want to create an automated history log of the main
objects. Objects are changed on several places in the application so my
intention was to call an AddHistory method everytime the Insert and Update
methods of the DBML file are called. The basic idea works fine, but I run
into some tricky problems on the details.

What is the best way to obtain previous values of the object?
I would like to log a descriptive text to make the user understand what has
happened.

My first try was to call the database just before the saving, to read
current database values just before they are changed. I created a temporary
datacontext object and used it to return the single object. Then I compared
all relevant properties between the current object and the database version
and logged every difference.

It worked fine for all simple properties but for extended properties
compiled from related tables I have some problems. With extended properties I
mean lookup values related to other tables. I don't want to refer to integer
values but rather the friendly names the user is used to!

If the temporary datacontext is closed before values are read I can not read
values from other tables. But if I keep the temporary datacontext alive it
will block cascading updates on related objects in same table.

Is it possible to create ReadOnly datacontext that will not block updates?

A completely different approach would be to create a detached oldvalue
collection inside the object from the beginning. Before saving the object it
would be easy to compare saving values with previous.

But extended properties need to be taken into account here as well! If I
retrieve a big collection of objects I don't want to slow down the initial
work by retrieving a lot of lookup values from other tables. In most cases
they will probably not be used anyhow.

Can I somehow INSIDE my partial class code detect when the PropertyChanging
event is fired? When a property is going to change seems to be a good place
to retrieve the previous values.

Any help is appreciated.
 
Hi Jakob,

Based on my understanding, you're using LINQ-to-SQL to retrieve data from
DB into your objects and you'd like to get previous values of the object
after you change them in your application. If I'm off base, please feel
free to let me know.
My first try was to call the database just before the saving, to read
Click to expand...
current database values just before they are changed.
This solution is practical, but I don't think it is necessary to get the
previous values from the DB.
But if I keep the temporary datacontext alive it will block cascading
Click to expand...
updates on related objects in same table.
Could you explain what you mean in the above sentence?
IMO, the entity class representing the DB table should have a property
representing the "id" column in the DB table and you can compare the value
of the entity object's "ID" property with the value of the DB table's "id"
column.
A completely different approach would be to create a detached old value
Click to expand...
collection inside the object from the beginning.
Yes, it is. You can implement the IEditableObject interface on the entity
class to maintain the previous values. For example:
public class Customer : IEditableObject
{
struct CustomerData
{
internal string id;
internal string firstName;
internal string lastName;
}

private CustomerData custData;
private CustomerData backupData;
private bool inTxn = false;

// Implements IEditableObject
void IEditableObject.BeginEdit()
{
Console.WriteLine("Start BeginEdit");
if (!inTxn)
{
this.backupData = custData;
inTxn = true;
Console.WriteLine("BeginEdit - " +
this.backupData.lastName);
}
Console.WriteLine("End BeginEdit");
}

void IEditableObject.CancelEdit()
{
Console.WriteLine("Start CancelEdit");
if (inTxn)
{
this.custData = backupData;
inTxn = false;
Console.WriteLine("CancelEdit - " + this.custData.lastName);
}
Console.WriteLine("End CancelEdit");
}

void IEditableObject.EndEdit()
{
Console.WriteLine("Start EndEdit" + this.custData.id +
this.custData.lastName);
if (inTxn)
{
backupData = new CustomerData();
inTxn = false;
Console.WriteLine("Done EndEdit - " + this.custData.id +
this.custData.lastName);
}
Console.WriteLine("End EndEdit");
}

public Customer(string ID)
: base()
{
this.custData = new CustomerData();
this.custData.id = ID;
this.custData.firstName = "";
this.custData.lastName = "";
}

public string ID
{
get
{
return this.custData.id;
}
}
public string FirstName
{
get
{
return this.custData.firstName;
}
set
{
this.custData.firstName = value;
}
}

public string LastName
{
get
{
return this.custData.lastName;
}
set
{
this.custData.lastName = value;
}
}
}
Can I somehow INSIDE my partial class code detect when the
Click to expand...
PropertyChanging event is fired?
You can implement the INotifyPropertyChanged interface on the entity class
to get notified when the values of the entity object is changed.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks Linda for your suggestions.

The IEditableObject construction looks attractive.
I tried it but it does not work the way I hoped.
The BeginEdit, CancelEdit, EndEdit methods are never executed even if I
change the property values. I guess this solution expects all edits to happen
through a control that will trigger the edit methods from outside? Changes
made by code will not be detected.

My next try was to use the INotifyPropertyChanged interface.
Same result here: It is never triggered. And besides I am afraid it occurs
too late, when the values have already changed?

I then tried INotifyPropertyChanging interface to store the values right
before they are actually changed. This will trigger a call already on first
load (which is not really necessary) and set the backup values. But on the
actual change when the property value is edited it will throw an exception
inside the designer generated code of the dbml file:

this.SendPropertyChanging();
ForeignKeyReferenceAlreadyHasValueException
"Operation is not valid due to the current state of the object."

I am stuck again.

Please not that I use LINQ-to-SQL with objects generated by DBML file.
 
Thanks for your suggestion Patrice.
In the previous version of this application I used triggers to accomplish
all sorts of things including this history logging. But as the years went by
and several changes were implemented I found the db code in procedures and
triggers was difficult to maintain. I longed for a clean start with all code
in C# and objects created by O/RM. Thus I really hesitate to go back ....
The logging is not really straightforward with a couple of columns, but
rather a complex comparison of previous values.
 
Jakob,

Although there is Linq there is nobody who tells that you cannot create your
own collections to get your goals.

Linq is not a replacement, it is an addition.

Cor
 
Thanks Patrice and Cor for your help!

Hi Jakob,

Thank you for your prompt response!

I do more research on this issue and find that implementing the
IEditableObject inteface on the entity class is not practical to your
scenario. You're right that only when the edits happen in UI, the methods
of the IEditableObject interface are called.

As Patrice as suggested, we can make use of the GetChangeSet method of the
DataContext object to get the changes that the DataContext tracks. However
this is not enough, because the return value of the GetChangeSet method
only contains current updated values of the items.

A workaround is to add a public property for each public property in the
entity class to maintain the previous value. We can set the initial value
of the property in the OnLoaded method and update it int the On**Changing
method.

For example, let's assume that we have a table named "People" in DB and the
table has two columns:
ID int primary
Name varchar(50)

Create a WinForm Application project in VS2008 and add a "LINQ to SQL
Classes" in the project. From the Server Explorer, add a connection to the
DB and drag the table "People" onto the left panel of the DataClass's
designer.

Add a class file in the project and paste the following code to the file:
partial class People
{
public int OrigionID { get; set; }
public string OrigionName { get; set; }
partial void OnIDChanging(int value)
{
OrigionID = this._ID;
}
partial void OnNameChanging(string value)
{
OrigionName = this._Name;
}
partial void OnCreated()
{
OrigionID = _ID;
OrigionName = _Name;
}
partial void OnLoaded()
{
OrigionID = _ID;
OrigionName = _Name;
}
}

Add two Buttons on the Form1 and paste the following code in the Form1.cs:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
DataClasses1DataContext dc = new DataClasses1DataContext();
BindingSource bs = new BindingSource();
private void Form1_Load(object sender, EventArgs e)
{
var q = from p in dc.Peoples
select p;
bs.DataSource = q;
}
// change values
private void button1_Click(object sender, EventArgs e)
{
IList list = bs.List;
(list[0] as People).Name = "aa";
}
// get changes
private void button2_Click(object sender, EventArgs e)
{
ChangeSet cs = dc.GetChangeSet();
IList<object> updates = cs.Updates;
foreach (object obj in updates)
{
Console.WriteLine("Origion_ID=" + (obj as
People).OrigionID.ToString() + " Origion_name=" + (obj as
People).OrigionName);
}
}
}

Hope this is what you want.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Jakob,

How about the problem now?

If you have any question, please feel free to let me know.

Thank you for using our MSDN Managed Newsgroup Support Service!

Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi.

I am having a similar problem... I have a group of user maintaining data from a table. This table has approx 150 columns with related FK data.

Basically, the gui works with the user loading 1 row from the table. Where they can modify the data. If a user clicks to go to another row, I have set up a catch to ask them if they want to cancel any changes or keep them. using changeset.

What i cant work out how to do is to list the changes that they have made.

I will post some code here...

private void C1CboSelectPortBeforeOpen(object sender, CancelEventArgs e)
{
if (_result != null)
{
ChangeSet cset = Db.GetChangeSet();
if (cset.Updates.Count > 0)
{
　
string s = "Summary: " + cset.ToString() + "\r\n";
foreach (var obj in cset.Updates)
{
Type ty = obj.GetType();
s += "Entity Updated: " + ty.Name +"\r\n";
//var diff = _result.Except(_original);
//Want to build a list of all the changes made.
}
　
//int index = c1cboSelectPort.SelectedIndex;
switch (
MessageBox.Show("There are pending changing. Do you want to cancel them?\r\n" + s, "Cancel Updates?",
MessageBoxButtons.YesNoCancel))
{
case DialogResult.No:
//Commit Changes
VcmdUpdateClick(sender, e);
break;
case DialogResult.Yes:
//cancel updates
Db.Refresh(RefreshMode.OverwriteCurrentValues, _result);
break;
case DialogResult.Cancel:
//revert to previous portfolio
e.Cancel = true;
break;
}
}
}
}
 
Back
Top