Into The Deep - Deep Copy With PLINQO

There are two reasons for this blog entry. First, I needed a way to replicate some data across databases using Linq To Sql. Second, the good folks at CodeSmith offer a free copy of CodeSmith Professional if you blog your experience with PLINQO. So, in the words of Ricky Ricardo, I got a lot of splainin to do.

Oh!, and by the way, please excuse the formatting of the code. It feels like I spent YEARS trying to get the formatting right on this article. Just download the code.

PLINQO is made up of a set of templates for the CodeSmith code generator. These templates allow the generation of several classes that add to the functionality of Linq To Sql. PLINQO is Linq To Sql, only better. You get more functionality, the structure of the project is improved from what you get with Linq To Sql, and PLINQO provides some nifty forward/backward synching of your changes between code and the dbml file. CodeSmith is integrated with Visual Studio, so much of what it does is done right in the good old VS IDE.

You can find more information, and download CodeSmith and PLINQO at the following sites;
Code Smith Tools
PLINQO

I could go into an exhaustive example of how to setup and use PLINQO here, but I'm going to limit this article to the main reason I'm using PLINQO, which has to do with a glaring problem in Linq To Sql. Specifically, Linq To Sql doesn't give you an easy way to load a record from one Data Context and write it to another. Once a data entity is attached to the Data Context, it cannot be attached to another, and there is no way to detach it. This makes it rather difficult to copy records from one database to another, or even copy records within the same database.

The reason that you cant easily move a data entity from one data context to another is because the entity object and the data context are aware of one another through an event model. In other words, when a change is made to an entity's data, an event is fired which the data context handles. This allows the data context to track changes in its attached entities and handle updates quickly and correctly. In order for an entity to be moved to another data context, the event handlers need to be detached. Also, each entity has lists of references to related entities(one to many) and references to other entities (many to one) that must be followed and updated in order for the entity to be used anywhere except attached to its original data context.

Along comes PLINQO and adds... (drum roll please)... Detach();

PLINQO's Detach method is implemented in a base class from which all the entities inherit. It navigates down through all linked entities and also detaches them from the data context. All that needs to be done in order to insert these records into another data context is to set any identities to zero, which indicates that this is a new record that needs to be inserted, detach from the current data context, attach the top level entity to a new data context, call SubmitChanges() and BANG! Your records have been inserted into the target database with new identities, which are all wired up correctly.

As a side note, you should wait to set the primary key to 0 until after you have updated data in any required child entities or you will find the lists of entities to be empty. See the PrepForCopy method in Listing 2 for more details.

So, let's get down to some code and see if we can make this vague explanation into something more concrete. Again, I am not going into how to setup and use CodeSmith or PLINQO, just the end results and how I used them. For the sake of simplicity I have created a small data model here.

Figure 1
This database is pretty simple, with only four tables and three relationships. It also has a unique index on the Code field in the Genre table. This will cause PLINQO to generate a GetByCode method, which you will see later.
Below you will see some code that uses PLINQO, and some home grown code, to select a book from one data context and deep copy it to another.

Listing 1

            // Get the book records from the source database.  ddlBooks is a ComboBox control that contains a list of books,
            // with the BookID as the value.
            Book book = BookExtensions.GetByKey(dc.Book, (int)ddlBooks.SelectedValue);

            // Create a destination data context.
            PLINQODemoDataContext destDC = new PLINQODemoDataContext(PLINQODemo.Properties.Settings.Default.PLINQODemoConnectionString);

            // Grab the genre from the destination database, by code because the ids may not match.  This demonstrates using the
            // PLINQO Query classes to retrieve a record based on a unique key.
            Genre destGenre = GenreExtensions.GetByCode(destDC.Genre, book.Genre.Code);

            // Grab the author from the destination database, by lastname/firstname.  This demonstrates using standard Linq To Sql to
            // retrieve a record.
            Author destAuthor = (from auths in destDC.Author
                                 where auths.LastName == book.Author.LastName &&
                                 auths.FirstName == book.Author.FirstName
                                 select auths).Single();

            // Set the ids to 0 in book and all sub-entities, so these records will be inserted as new records in the destination.
            // destDC is passed to this method because it is possible to override the ZeroIds method in individual entity classes
            // and do your destination entity lookup, such as genre and author, at that level.
            book.PrepForCopy(destDC);

            // By assigning a new Genre and Author we prevent the insertion of a genre and auther.
            // We are connect the new Book record to an existing Genre and Author in the destination database.
            book.Genre = destGenre;
            book.Author = destAuthor;

            // Detatch book and all sub-entities from source database.
            book.Detach();

            // Update the database.
            destDC.Book.InsertOnSubmit(book);
            destDC.SubmitChanges();

Look at the third line of code from the bottom. The Detach() method is provided by PLINQO, not by Linq To Sql. As described above, Detach separates the entity (and all sub-entities) from the data context. However, that is not all we need to do in order to prepare this entity tree to be written to another data context. Since the database has identity columns we must set the identity field to 0 in all entities we want to write out to the new data context, and we must also, prevent some entities from being written, such as lookup tables. In this case, our lookup tables are Genre and Author. In order to prevent new Genre and Author records from being inserted, we grab a copy of the matching Genre and Author entities from the destination database. Notice the PLINQO provided method GenreExtensions.GetByCode(). This allows a very easy way to retrieve a specific Genre record. Since I didn't define a unique index on the LastName and FirstName fields in the Author table, we dont have a handy-dandy method to retrieve that entity, so I use standard Linq To Sql syntax. Yes, I realize that there could be multiple authors with the same name and I haven't accounted for it. This is an example, not a production ready app. Now I have an entity for Genre and Author, but what do I do with them? Well, we'll get to that in a minute...

The next line down in the code calls the book.PrefForCopy method. Where did this method come from? Well, that's the home-grown code I mentioned. This is a public virtual method in a brand-spanking new partial class I declared. This partial class adds to the LinqEntityBase class, which is the basis for all of the entity classes. Here's the code for the PrepForCopy method;

Listing 2

        /// <summary>
        /// Zeroes the ids of all related entities, recursively, using polymorphism to ZeroIds on entities with special cases.
        /// Each entity with a special case will have a partial calss defined "classname.Extended.cs" to override this behavior.
        /// </summary>
        /// <param name="destDataContext">The dest data context.</param>
        public virtual void PrepForCopy(PLINQODemoDataContext destDataContext)
        {
            // Using reflection, get the type of the incoming entity, then get all properties for that type.  Then loop through the
            // properties looking for entity sets, which represent lists of sub-entities (table records).
            Type t = this.GetType();
            PropertyInfo[] properties = t.GetProperties();

            foreach (PropertyInfo pi in properties)
            {
                if (pi.PropertyType.Name.ToLower().Contains("entityset"))
                {
                    object oEntities = null;
                    try
                    {
                        // Get the property value from the property on the provided entity object.
                        oEntities = pi.GetValue(this, null);
                    }
                    catch (Exception ex)
                    {
                        Exception nex = ex;
                        throw ex;
                    }
                    // If we got a value from the property, process it as its correct type.
                    if (oEntities != null)
                    {
                        IList list = (IList)oEntities;
                        foreach (LinqEntityBase lbe in list)
                        {
                            lbe.PrepForCopy(destDataContext);
                        }
                    }
                }
            }

            // Now zero out the ID fields for this item.  This must be done AFTER the lists of other entities have been processed
            // so that the lists of entities still exist.  Setting IDs to 0 disconnects some of the object model heirarchy.
            foreach (PropertyInfo pi in properties)
            {
                // Make sure we are dealing with the primary key on the currently processing entity class.
                if ((pi.Name.ToLower().Equals(this.GetType().Name.ToLower() + "id")) && (pi.PropertyType == typeof(Int32)))
                {
                    // Set "ID" properties to 0.  This indicates that the record should be inserted as a new record on the next
                    // SubmitChanges call.
                    pi.SetValue(this, 0, null);
                }
            }
            return;
        }

This method uses reflection to locate all the entity lists and identity columns and recursively set the proper keys to 0. Hmmm... Doesn't this mean that we just walked down into the Genre and Author tables and set the identity columns to 0, which will cause these records to be inserted as new records in the target database? Good eyes you have there. Yes it does mean that, which brings me back to those two entities we grabbed and held on to up above. Now look at Listing 1, near the bottom you will see where I assign those entities back into the book entity. Since these already exist in the new data context were good to go. Just call InsertOnSubmit and SubmitChanges and a new book record is inserted. The magic is that the matching Reference records are also replicated.
But, what if I didn't want to copy the references? What if I just wanted the Book record to be copied into the target database? We can handle that in a couple of ways. First you could just;

book.ReferenceList = null;

before calling InsertOnSubmit and SubmitChanges, or we could do something a little more creative, using the fact that PrepForCopy is a virtual method on the base class for all the entities... Lets see, if we declare another partial Book class and override the PrepForCopy method, it could look something like this;

    public partial class Book
    {
        public override void PrepForCopy(PLINQODemoDataContext destDC)
        {
            // Grab the genre from the destination database, by code because the ids may not match.  This demonstrates using the
            // PLINQO Query classes to retrieve a record based on a unique key.
            Genre destGenre = GenreExtensions.GetByCode(destDC.Genre, this.Genre.Code);

            // Grab the author from the destination database, by lastname/firstname.  This demonstrates using standard Linq To Sql to
            // retrieve a record.
            Author destAuthor = (from auths in destDC.Author
                                 where auths.LastName == this.Author.LastName &&
                                 auths.FirstName == this.Author.FirstName
                                 select auths).Single();

            base.PrepForCopy(destDC);
           
            //this.ReferenceList = null;
            this.Genre = destGenre;
            this.Author = destAuthor;
        }
    }

Which means that the code from listing 1 can change to simply this;

Listing 4

            // Get the book records from the source database.  ddlBooks is a ComboBox control that contains a list of books,
            // with the BookID as the value.
            Book book = BookExtensions.GetByKey(dc.Book, (int)ddlBooks.SelectedValue);

            // Create a destination data context.
            PLINQODemoDataContext destDC = new PLINQODemoDataContext(PLINQODemo.Properties.Settings.Default.PLINQODemoConnectionString);

            // Set the ids to 0 in book and all sub-entities, so these records will be inserted as new records in the destination.
            // destDC is passed to this method because it is possible to override the ZeroIds method in individual entity classes
            // and do your destination entity lookup, such as genre and author, at that level.
            book.PrepForCopy(destDC);

            // Detatch book and all sub-entities from source database.
            book.Detach();

            // Update the database.
            destDC.Book.InsertOnSubmit(book);
            destDC.SubmitChanges();

Either way will work. The second way may not be as flexible if you are planning to use the data model for copying records in multiple ways. However, using the second method I was able to generate my PLINQO classes, override the PrepForCopy method on for certain classes, and deep copy database records from a data model with 44 interconnected tables. I had to make some special arrangements for some of the tables where foreign key relationships were missing from the database, but all in all, this was much easier than if I had to do the same thing using DataSets and DataTables, handling the primary and foreign key values myself.

There were some things about PLINQO that I thought could be improved. Thats what new versions are all about, right? One thing I noticed hasto do with regeneration of the dbml file. The database I was trying to use was already being used in a production environment and could not be changed. It was also missing some relationships between some of the tables, which caused some code generation problems. I assumed I could add those relationships in the dbml editor and have those changes be persisted down into the generated code. However, my changes were overwritten the next time I compiled. I found a reference to this in a forum, where the folks at Code Smith Tools indicate that they already know about this and are considering adding this ability for a future release.

Overall, I am impressed with PLINQO, and I will be using PLINQO in the future.

Code for this project can be downloaded at; PLINQO Demo