Complex Data Operations with ZCompare and a Micro ORM

The Scenario

This tutorial expands on the scenario introduced in the quick start tutorial whereby data is modified and needs to be saved to the database. This is a very common scenario where we want to know what has been updated, added and deleted. With ZCompare we can do this easily and efficiently. This tutorial will show how we can work with complex, nested entities and perform all the database operations very easily.

The Data

Although not essential, you will find the completed code sample used to this tutorial in the ZCee Project. Download it and look in the ResultsViewModel.cs file for the mirco ORM section.

We are going to work with a list of Suppliers. Take a look here for class definitions.

The code below shows how we create a list of 6 Suppliers as originalSuppliers. We create the same list again as updatedSuppliers.

List<Supplier> originalSuppliers = SampleData.CreateSuppliers(6);
List<Supplier> updatedSuppliers = SampleData.CreateSuppliers(6);

The updatedSuppliers data is modified...

updatedSuppliers[0].Name = "New Supplier Name";
updatedSuppliers[2].Addresses["Head Office"].PostCode.Inner = "PC99"; // Modify an address PostCode for a supplier
updatedSuppliers[2].Products.RemoveAt(0); // Delete a product from this supplier
updatedSuppliers[2].Products.Add(SampleData.CreateProduct(20)); // Add a product to this supplier
updatedSuppliers[2].Products[2].ImageData = new byte[2] { 34, 36 }; // Change a property of a product
updatedSuppliers[3].Name = "New Supplier Name";
updatedSuppliers[3].Addresses.Add("Northern HQ", SampleData.CreateAddressList(3)[0]); // Add an Address
updatedSuppliers[3].Products.Add(SampleData.CreateProduct(21)); // Add a product to this supplier
updatedSuppliers[3].Products[1].Code = new ProductCode { Category = 'K', ProductID = 923 };
updatedSuppliers.RemoveAt(4); // Delete a Supplier
updatedSuppliers.Add(SampleData.CreateSupplier(7)); // Add a Supplier

Use ZCompare.Compare() to get our results.

var results = ZCompare.Compare(originalSuppliers, updatedSuppliers);

Now, we have our Supplier type, which has a list of Product types and also Address types. Typically these types would map onto our database as entities in associated tables. So with that in mind let's start to process our results.

 var supplierResults = results.GetResults<Supplier>(originalSuppliers);
supplierResults.ForEach(s =>
{
    if (s.Status == ResultStatus.Added)
    {
        ORM.Insert(s.ChangedToValue);
    }
    else if (s.Status == ResultStatus.Deleted)
    {
        ORM.Delete<Supplier>(s.OriginalValue.ID);
    }
    else if (s.Status == ResultStatus.Changed)
    {
        ORM.Update(s.ChangedToValue);
    }
});

We are just using a stub for the micro ORM in this case.

// Our Micro ORM stub
public static class ORM {
    public static int Insert<T>(T insertObject)
    {
        Debug.WriteLine($"Insert : {insertObject.ToString()}");
        return 999;
    }
    public static void Update<T>(T updateObject)
    {
        Debug.WriteLine($"Update : {updateObject.ToString()}");
    }
    public static void Delete<T>(int id)
    {
        Debug.WriteLine($"Delete : {id} : {typeof(T).ToString()}");
    }
}

This shows how with ZCompares' typesafe results we can use the ChangedToValue to update/insert a Supplier and the OriginalValue to delete a Supplier.

However, we are missing something, this code only updates the 'Supplier' table. Products and Addresses are properties of a Supplier that will have their own tables and we need to do the same for them for each supplier that has modifications. So, just concentrating on the added suppliers...

supplierResults.ForEach(s =>
{
    if (s.Status == ResultStatus.Added)
    {
        ORM.Insert(s.ChangedToValue);
                           
        s.ChangedToValue.Products.ForEach(p =>
        {
            // Insert all the new Products
            ORM.Insert(p);
        });
            
        s.ChangedToValue.Addresses.ToList().ForEach(a =>
        {
            // Insert all the new Addresses
            ORM.Insert(a);
        });
    }
});

ZCompare gives us typesafe results and allows us access to the original and modified objects. This allows us to process each supplier along with their products and address.

In a moment we will expand this code to include the updates and deletions. Firstly though, the code above is overlooking a crucial property, the Id property. We would want to know the new Id of the added Supplier to then use it as a foreign key field on the Product and Address table entries perhaps. I have deliberately left these specifics out as there are many ways this can be done dependent on your ORM, entities or data access model. But one I frequently use is shown below.

supplierResults.ForEach(s =>
{
    if (s.Status == ResultStatus.Added)
    {
        var newID = ORM.Insert(s.ChangedToValue);
        s.ChangedToValue.Products.ForEach(p =>
        {
            // Insert all the new Products
            var productEntity = p.ToProductEntity();
            productEntity.SupplierID = newID;
            ORM.Insert(productEntity);
        });
    }
});

var newID = ORM.Insert(s.ChangedToValue); gets the new Supplier ID inserted in the database. var productEntity = p.ChangedToValue.ToProductEntity(); is an extension method (not shown) used to convert my object model Product type into my database entity Product type. I assign the productEntity.SupplierID foreign key field.

I hope this clears up the approach and that it really is dependent on your data access model how you achieve this.

Back to ZCompare and expanding the code for updates and deletions. Lets focus now on the updates of Suppliers.

supplierResults.ForEach(s =>
{
    if (s.Status == ResultStatus.Changed)
    {
        if (s.ChangesType.HasFlag(ChangesType.ValueTypes)) ORM.Update(s.ChangedToValue);
        var productResults = results.GetResults<Product>(s.OriginalValue.Products, true);
        productResults.ForEach(p =>
        {
            // Insert, Update or Delete Products
        });

        var addressResults = results.GetResults<Address>(s.OriginalValue.Addresses, true);
        addressResults.ForEach(a =>
        {
            // Insert, Update or Delete Addresses
        });
    }
});

s.Status == ResultStatus.Changed tells us that the Supplier has been modified.

In the first Supplier, updatedSuppliers[0].Name = "New Supplier Name"; The suppliers' Name property has changed, and we will want to update the Suppliers table.

The s.ChangesType.HasFlag(ChangesType.ValueTypes) tells us that properties of the first Supplier have changed are ValueTypes (string is treated as a value type) so we use ORM.Update(s.ChangedToValue) to perform the update.

For the next modified supplier , updatedSuppliers[2] , that supplier has it's Products and Address properties modified which are ReferenceTypes. In these cases, s.ChangesType == ChangesType.ReferenceTypes. So, s.ChangesType.HasFlag(ChangesType.ValueTypes) will return false. Calling ORM.Update(s.ChangedToValue); would be redundant in this case.

Finally last modified Supplier, updatedSuppliers[3], this supplier has its Name changed along with it's Products and Addresses. In this case s.ChangesType == ChangesType.ValueTypes | ChangesType.ReferenceTypes. So s.ChangesType.HasFlag(ChangesType.ValueTypes) will return true.

Note how we use the GetResults<T>() method and pass in the context reference object to get the results we want to process. First we use results.GetResults<Product>(s.OriginalValue.Products, true) to get our Products that need processing and then results.GetResults<Address>(s.OriginalValue.Addresses, true); for our Addresses.

That is essentially all you need, it is easy and very efficient to make your data updates with full control. Below is the complete code structure...

supplierResults.ForEach(s =>
{
    if (s.Status == ResultStatus.Added)
    {
        ORM.Insert(s.ChangedToValue);
        s.ChangedToValue.Products.ForEach(p =>
        {
            // Insert all the new Products
            ORM.Insert(p);
        });
        s.ChangedToValue.Addresses.ToList().ForEach(a =>
        {
            // Insert all the new Addresses
            ORM.Insert(a);
        });
    }
    else if (s.Status == ResultStatus.Deleted)
    {
        s.OriginalValue.Products.ForEach(p =>
        {
            // Delete all Products
            ORM.Delete<Product>
            (p.ID);
        });

        s.OriginalValue.Addresses.ToList().ForEach(a =>
        {
            // Delete all Addresses
            ORM.Delete<Address>(a.Value.ID);
        });
            
        // Delete the Supplier entry
        ORM.Delete<Supplier>(s.ID);
    }
    else if (s.Status == ResultStatus.Changed)
    {
        if (s.ChangesType.HasFlag(ChangesType.ValueTypes)) ORM.Update(s.ChangedToValue);
        var productResults = results.GetResults<Product>(s.OriginalValue.Products, true);
        productResults.ForEach(p =>
        {
            // Insert, Update or Delete Products
        });
        var addressResults = results.GetResults<Address>(s.OriginalValue.Addresses, true);
        addressResults.ForEach(a =>
        {
            // Insert, Update or Delete Addresses
        });
    }
});