many to many relationships : serialization and cascade delete corrections

Sep 9, 2008 at 8:32 AM
Hi Matt,

1) As declared in my last post of the "many to many relationships : problem ...." discussion, I had to write a function to correct some problems related to entities containing two foreign keys.

----I have added a new private member to the EntityBase class :

        private List<PropertyInfo> _listFKPropertyInfo; //pa:stores the property infos for foreignKey associations in a list

----I have added this new instruction in the Init function

            _listFKPropertyInfo = new List<PropertyInfo>();

----I have modified the FindImportantProperties() function as follow

                    // Store the FK relationships seperately (i.e. child to parent relationships);
                    if (assocAttribute.IsForeignKey != true)
                        _entityAssociationProperties.Add(propInfo.Name, propInfo);
                    else
                    {
                        _entityAssociationFKProperties.Add(propInfo.Name, propInfo);
                        _listFKPropertyInfo.Add(propInfo);
                    }

----I have added this function

         /// <summary>
        /// pa:Entities with more than two foreign keys (not nulls) must be modified after a serialize-deserialise processing
        /// because only one foreign key is correctly initialised for each of the many instances of the same original entity
        ///
        /// !!!!!This function only process entities with exactly 2 foreign keys
        ///
        /// </summary>
        /// <param name="iEntity"></param>
        /// <param name="iForUpdating"></param>
        /// <param name="iSb"></param>
        public static void CorrectGraphConsistency(LINQEntityBase iEntity, StringBuilder iSb)
        {
            iSb.Append("\nCorrectGraphConsistency");
            List<LINQEntityBase> EntityList = iEntity.ToEntityTree();
            List<LINQEntityBase> EntityFK2 = new List<LINQEntityBase>();

            foreach (LINQEntityBase ent1 in EntityList)
            {
                if (ent1._entityAssociationFKProperties.Count==2)
                {
                    EntityFK2.Add(ent1);
                }
            }
            if (EntityFK2.Count % 2 != 0)
            {
                iSb.Append(String.Format("\n*******EntityFK2.Count impair : {0}", EntityFK2.Count));
                return;
            }

            var lFK2 = (from ent in EntityFK2
                        orderby ent.LINQEntityGUID
                        select ent).ToList();

            for (int i = 0; i < lFK2.Count; i += 2)
            {
                LINQEntityBase e1 = lFK2[i];
                LINQEntityBase e2 = lFK2[i + 1];
                int i1_null, i1_ref, i2_null, i2_ref;
                i1_null = 0; i1_ref = 1;
                i2_null = 0; i2_ref = 1;

                LINQEntityBase e1RefNull = (LINQEntityBase)
                                     e1._listFKPropertyInfo[i1_null].GetValue(e1, null);
                LINQEntityBase e1Ref = (LINQEntityBase)
                                     e1._listFKPropertyInfo[i1_ref].GetValue(e1, null);
                LINQEntityBase e2RefNull = (LINQEntityBase)
                                     e2._listFKPropertyInfo[i2_null].GetValue(e2, null);
                LINQEntityBase e2Ref = (LINQEntityBase)
                                     e2._listFKPropertyInfo[i2_ref].GetValue(e2, null);

                if (e1RefNull != null && e1Ref == null)
                {
                    LINQEntityBase temp = e1RefNull;
                    e1RefNull = e1Ref;
                    e1Ref = temp;
                    i1_ref = 0;
                    i1_null = 1;
                }
                if (e1RefNull != null || e1Ref == null)
                {
                    iSb.Append(String.Format("\n*******(e1RefNull != null || e1Ref == null)"));
                    return;
                }

                if (e2RefNull != null && e2Ref == null)
                {
                    LINQEntityBase temp = e2RefNull;
                    e2RefNull = e2Ref;
                    e2Ref = temp;
                    i2_ref = 0;
                    i2_null = 1;
                }
                if (e2RefNull != null || e2Ref == null)
                {
                    iSb.Append(String.Format("\n*******(e2RefNull != null || e2Ref == null)"));
                    return;
                }

                e1._listFKPropertyInfo[i1_null].SetValue(e1, e2Ref, null);
                e2._listFKPropertyInfo[i2_ref].SetValue(e2, null, null);
            }
            return;
        }

2) I encountered some problems with the cascade delete for entities containing several foreign keys.

----I have modifed the SynchroniseWithDataContext function as follows (cascade delete) :

                    // Check to see if cascading deletes is allowed
                    if (cascadeDelete)
                    {
                        // Grab the entity tree and reverse it so that this entity is deleted last
                        List<LINQEntityBase> entityTreeReversed = entity.ToEntityTree();
                        entityTreeReversed.Reverse();

                        // Cascade delete children and then this object
                        foreach (LINQEntityBase toDelete in entityTreeReversed)
                        {
                            //padeb
                            if (!entitiesDeleted.Contains(toDelete))
                            {
                                //pafin
                                toDelete.SetAsDeleteOnSubmit();
                                targetDataContext.GetTable(toDelete.GetEntityType()).Attach(toDelete);
                                targetDataContext.GetTable(toDelete.GetEntityType()).DeleteOnSubmit(toDelete);
                                //padeb
                                entitiesDeleted.Add(toDelete);
                            }
                            //pafin

                        }
                        //add these to a list to make sure we don't attach them twice.
                        //padeb
                        //entitiesDeleted.AddRange(entityTreeReversed);
                        //pafin
                    }


Patrick
Sep 22, 2008 at 11:56 PM
Edited Sep 22, 2008 at 11:58 PM
Hi Patrick,
Sorry for the late reply, I'm actually on leave and I'm having a hard time trying to concentrate on the project ;)

Anyway, it looks like you've done some good work here!  When I get back into the swing of things (after my leave ends), I'll take a closer look.

Cheers

Matt
Oct 5, 2008 at 12:57 AM
Hi Patrick, 

I haven't had a full look at your solution yet, but I thought you'd like to know....

I stumbled accross some interesting facts about the .NET 3.5 framework and SP1, and apparently reference serialization is now supported on the data contract serializer.  At this stage however Microsoft have not integrated it with the LINQ 2 SQL editor, but it is still available in the background.

I did a really quick test and it seemed to work okay (hey, it compiled and ran!), but thought you would want to try it out and see if it got around issue #1 about (i.e. two copies of the object).

See this link:
http://www.zamd.net/2008/05/20/DataContractSerializerAndIsReferenceProperty.aspx

Basically, change the LINQEntityBase class DataContract attribute to:

[DataContract(IsReference = true)]

What this does it creates a reference Id for each element, and hopefully de-serialize properly without having duplicates (well, from what i read it should!).

Anyway, give it a whirl.  I don't necessarily think it will fix the deletion issue, but it should correct the first issue.

Cheers

Matt.


Oct 6, 2008 at 12:27 PM
Hi Matt,

I have been trying the new [DataContract(IsReference = true)] to work around the problem I have with my LINQ Entity Heirachy which contains a lot of EntityRef relationships which aren't serialised with the pre-SP1 serialisation.

I am using this LINQ to SQL template generator: http://damieng.com/blog/2008/09/14/linq-to-sql-template-for-visual-studio-2008

Serialisation over WCF works great now, with my EntityRef's serialised correctly.

However, the LINQEntityState of my EntityRef's are
EntityState.NotTracked, meaning that any changes to them client side are not reflected in the database after calling SynchroniseWithDataContext() and SubmitChanges() server side.

Any suggestions?  Any plans to update LINQ2SQLEB to support change tracking on EntityRef's?

Thanks,
Murray
Oct 10, 2008 at 1:06 AM
Edited Oct 10, 2008 at 1:15 AM
Hi Murray,

The unidirectional serialization used by LINQ2SQL is on based on Parent --> Child Relationships, I've tried to explain it in this post http://www.codeplex.com/LINQ2SQLEB/Thread/View.aspx?ThreadId=33742

Entity Refs are not seralized, if you want to seralize them you need to treat them seperately in their own parent --> child graph, which may be as simple as putting this graph in it's own parameter seperate from the other original graph when you call the back end.

E.g. UpdateData(Customer, Products)

Where Customer is the entity your working with and products is a list of products containing at least one product you are referencing.  In the UpdateData() Method, you should be able to sync both with the same data context and then submit the changes so that all changes are sent in one hit to the db.

The other way is to change the relationship in the dbml and/or in the physical database to make the reference data into a "child".  Entity ref data is usually shared reference data (like a list of codes, products or something) and is shared between many entities - if this doesn't sound like what you have - have a look at the relationship ship in your data model. Often, when I find myself in a situation where an entity ref needs to be updated at the same time as the data referencing it, i figure out that I've had the relationship the wrong way round in the first place, and what I have done is not good database schema senese anyway - I then change the relationship to make the entity a child rather than referenced data. 

Please let me know if this doesn't answer your question, or if you need more help.

Cheers

Matt.



Oct 10, 2008 at 4:25 AM
Edited Oct 10, 2008 at 6:31 AM
Hi Matt,

Thanks for the reply - and good work on this by the way.

My database design actually has quite a few EntityRef relationships which I want to serialise in 1 object heirachy.

eg. Project table, ProjectDocument table, Document table.  I have several other tables in addition to ProjectDocument referencing Document table.
My L2S entity heirachy is then Project.ProjectDocuments where each ProjectDocument has a Document EntityRef.  This 1 example is very common throughout my db schema.

What I have done to solve my problem is bastardised your code a little (sorry!) in conjunction with customising the LINQ to SQL template generator: http://damieng.com/blog/2008/09/14/linq-to-sql-template-for-visual-studio-2008 and using [DataContract(IsReference = true)]  to get all the EntityRefs serialising correctly.

I have changed the L2S template generator to create an overridden contructor in my L2S classes which takes a couple of params - 1 indicating whether the object is a change tracking root, and a 2nd param which indicates the initial EntityState

eg. public Project(bool setAsChangeTrackingRoot, EntityState entityState)

This also simplifies the creating of New entities client side.

I have changed your LINQ2SQLEB code to allow the LINQEntityState for EntityRef's to be set from this new constructor (so my EntityRef objects are no longer NotTracked but Original).  My EntityRef objects then automatically have their LINQEntityState changed whenever object properties are changed.

I then split your SetAsChangeTrackingRoot() code, and also your SynchroniseWithDataContext() code out into a couple of public methods I can call to explicitly set the EntityState on an EntityRef, and Attach EntityRefs that have changes.

For the above example, my Middle Tier Retrieve and Update code ends up looking like:
RETRIEVE:
                // LINQ query etc to populate here....

                project.SetAsChangeTrackingRoot();
                foreach (ProjectDocument pd in project.ProjectDocuments)
                    pd.Document.SetAsChangeTrackingNode();

UPDATE:
            project.SynchroniseWithDataContext(db, true);
            foreach (ProjectDocument pd in project.ProjectDocuments)
                pd.Document.SynchroniseThisEntityWithDataContext(db);
            db.SubmitChanges();

Certainly not as clean as it could be (I initially tried changing your SetAsChangeTrackingRoot() etc methods to handle the EntityRef's but got myself in a bit of a mess with the ToEntityTree() trying to handle EntityRef FK etc) but it is now doing the job I need from it.

Let me know if you would like me to send through my bastardised version of your code ;)

Cheers,
Murray
Oct 22, 2008 at 1:54 AM
Hi Patrick,

I've included fixes to both your issues in RC 3.0 which I just released - thanks for your help - let me know how you go.

Cheers

Matt
Oct 22, 2008 at 1:58 AM
Hi Murray,

Sorry for the late reply.

That sounds good what you are doing - I haven't had a change to look at the T4 stuff in any detail, but will do soon.  Some of the guys here at work have had a look and think it's pretty neat.

Feel free to send through your code (please start a different post) - I need to get my head around it + T4 stuff.

Cheers

Matt