c# object equality for database persistance

Question

I want to learn how others cope with the following scenario.

This is not homework or an assignment of any kind. The example classes have been created to better illustrate my question however it does reflect a real life scenario which we would like feedback on.

We retrieve all data from the database and place it into an object. A object represents a single record and if multiple records exist in the database, we place the data into a List<> of the record object.

Lets say we have the following classes;

    public class Employee
    {
        public bool _Modified;
        public string _FirstName;
        public string _LastName;
        public List<Emplyee_Address> _Address;
    }

    public class Employee_Address
    {
        public bool _Modified;
        public string _Address;
        public string _City;
        public string _State;
    }

Please note that the Getters and Setters have been omitted from the classes for the sake of clarity. Before any code police accuse me of not using them, please note that have been left out for this example only.

The database has a table for Employees and another for Employee Addresses.

Conceptually, what we do is to create a List object that represents the data in the database tables. We do a deep clone of this object which we then bind to controls on the front end. We then have two objects (Orig and Final) representing data from the database.

The user then makes changes to the "Final" object by creating, modifying, deleting records. We then want to persist these changes to the database.

Obviously we want to be as elegant as possible, only editing, creating, deleting those records that require it.

We ultimately want to compare the two List objects so that we can;

1. See what properties have changed so that the changes can be persisted to the database.

2. See what properties (records) no longer exist in the second List<> so that these records can be deleted from the database.

3. See what new properties exist in the new List<> so that we can create these in the database.

Who wants to get the ball rolling on how we can best achieve this. Keep in mind that we also need to drill down into the Employee_Address list to check for any changes, not just the top level properties.

I hope I have made myself clear and look forward to any suggestions.

Answer 1

I would do exactly the same thing .NET does in their Data classes, that is keep the record state (System.Data.DataRowState comes to mind) and all associated versions together in one object.

This way:

• You can tell at a glance whether it has been modified, inserted, deleted, or is still the original record.
• You can quickly find what has been changed by querying the new vs old versions, without having to dig in another collection to find the old version.

Answer 2

Why would you want to compare two list objects? You will potentially be using a lot of memory for what is essentially duplicate data.

I'd suggest having a status property for each object that can tell you if that particular object is New, Deleted, or Changed. If you want go further than making the property an Enum, you can make it an object that contains some sort of Dictionary that contains the changes to update, though that will most likely apply only in the case of the Changed status.

After you've added such a property, it should be easy to go through your list, add the New objects, remove the Deleted objects etc.

You may want to check how the Entity Framework does this sort of thing as well.

Answer 3

You should investigate the use of the Identity Map pattern. Coupled with Unit of Work, this allows you to maintain an object "cache" of sorts from which you can check which objects need saving to the database, and when reading, to return objects from the identity map rather than creating new objects and returning those.

Answer 4

Add nullable ObjectID field to your layer's base type. Pass it to front end and back to see if particular instance persists in the database. It also has many other uses even if you don't have any kind of Identity Map