private CellTreeNode ConvertUnionsToNormalizedLOJs(CellTreeNode rootNode)
        {
            // Recursively, transform the subtrees rooted at rootNode's children.
            for (var i = 0; i < rootNode.Children.Count; i++)
            {
                // Method modifies input as well.
                rootNode.Children[i] = ConvertUnionsToNormalizedLOJs(rootNode.Children[i]);
            }

            // We rewrite only LOJs.
            if (rootNode.OpType != CellTreeOpType.LOJ
                || rootNode.Children.Count < 2)
            {
                return rootNode;
            }

            // Create the resulting LOJ node.
            var result = new OpCellTreeNode(m_viewgenContext, rootNode.OpType);

            // Create working collection for the LOJ children.
            var children = new List<CellTreeNode>();

            // If rootNode looks something like ((V0 IJ V1) LOJ V2 LOJ V3),
            // and it turns out that there are FK associations from V2 or V3 pointing, let's say at V0,
            // then we want to rewrite the result as (V1 IJ (V0 LOJ V2 LOJ V3)).
            // If we don't do this, then plan compiler won't have a chance to eliminate LOJ V2 LOJ V3.
            // Hence, flatten the first child or rootNode if it's IJ, but remember that its parts are driving nodes for the LOJ,
            // so that we don't accidentally nest them.
            OpCellTreeNode resultIJDriver = null;
            HashSet<CellTreeNode> resultIJDriverChildren = null;
            if (rootNode.Children[0].OpType
                == CellTreeOpType.IJ)
            {
                // Create empty resultIJDriver node and add it as the first child (driving) into the LOJ result.
                resultIJDriver = new OpCellTreeNode(m_viewgenContext, rootNode.Children[0].OpType);
                result.Add(resultIJDriver);

                children.AddRange(rootNode.Children[0].Children);
                resultIJDriverChildren = new HashSet<CellTreeNode>(rootNode.Children[0].Children);
            }
            else
            {
                result.Add(rootNode.Children[0]);
            }

            // Flatten unions in non-driving nodes: (V0 LOJ (V1 Union V2 Union V3)) -> (V0 LOJ V1 LOJ V2 LOJ V3) 
            foreach (var child in rootNode.Children.Skip(1))
            {
                var opNode = child as OpCellTreeNode;
                if (opNode != null
                    && opNode.OpType == CellTreeOpType.Union)
                {
                    children.AddRange(opNode.Children);
                }
                else
                {
                    children.Add(child);
                }
            }

            // A dictionary that maps an extent to the nodes that are from that extent.
            // We want a ref comparer here.
            var extentMap = new KeyToListMap<EntitySet, LeafCellTreeNode>(EqualityComparer<EntitySet>.Default);
            // Note that we skip non-leaf nodes (non-leaf nodes don't have FKs) and attach them directly to the result.
            foreach (var child in children)
            {
                var leaf = child as LeafCellTreeNode;
                if (leaf != null)
                {
                    EntitySetBase extent = GetLeafNodeTable(leaf);
                    if (extent != null)
                    {
                        extentMap.Add((EntitySet)extent, leaf);
                    }
                }
                else
                {
                    if (resultIJDriverChildren != null
                        && resultIJDriverChildren.Contains(child))
                    {
                        resultIJDriver.Add(child);
                    }
                    else
                    {
                        result.Add(child);
                    }
                }
            }

            // We only deal with simple cases - one node per extent, remove the rest from children and attach directly to result.
            var nonTrivial = extentMap.KeyValuePairs.Where(m => m.Value.Count > 1).ToArray();
            foreach (var m in nonTrivial)
            {
                extentMap.RemoveKey(m.Key);
                foreach (var n in m.Value)
                {
                    if (resultIJDriverChildren != null
                        && resultIJDriverChildren.Contains(n))
                    {
                        resultIJDriver.Add(n);
                    }
                    else
                    {
                        result.Add(n);
                    }
                }
            }
            Debug.Assert(extentMap.KeyValuePairs.All(m => m.Value.Count == 1), "extentMap must map to single nodes only.");

            // Walk the extents in extentMap and for each extent build PK -> FK1(PK1), FK2(PK2), ... map
            // where PK is the primary key of the left extent, and FKn(PKn) is an FK of a right extent that 
            // points to the PK of the left extent and is based on the PK columns of the right extent.
            // Example:
            //           table tBaseType(Id int, c1 int), PK = (tBaseType.Id)
            //           table tDerivedType1(Id int, c2 int), PK1 = (tDerivedType1.Id), FK1 = (tDerivedType1.Id -> tBaseType.Id)
            //           table tDerivedType2(Id int, c3 int), PK2 = (tDerivedType2.Id), FK2 = (tDerivedType2.Id -> tBaseType.Id)
            // Will produce:
            //           (tBaseType) -> (tDerivedType1, tDerivedType2)
            var pkFkMap = new KeyToListMap<EntitySet, EntitySet>(EqualityComparer<EntitySet>.Default);
            // Also for each extent in extentMap, build another map (extent) -> (LOJ node).
            // It will be used to construct the nesting in the next step.
            var extentLOJs = new Dictionary<EntitySet, OpCellTreeNode>(EqualityComparer<EntitySet>.Default);
            foreach (var extentInfo in extentMap.KeyValuePairs)
            {
                var principalExtent = extentInfo.Key;
                foreach (var fkExtent in GetFKOverPKDependents(principalExtent))
                {
                    // Only track fkExtents that are in extentMap.
                    ReadOnlyCollection<LeafCellTreeNode> nodes;
                    if (extentMap.TryGetListForKey(fkExtent, out nodes))
                    {
                        // Make sure that we are not adding resultIJDriverChildren as FK dependents - we do not want them to get nested.
                        if (resultIJDriverChildren == null
                            || !resultIJDriverChildren.Contains(nodes.Single()))
                        {
                            pkFkMap.Add(principalExtent, fkExtent);
                        }
                    }
                }
                var extentLojNode = new OpCellTreeNode(m_viewgenContext, CellTreeOpType.LOJ);
                extentLojNode.Add(extentInfo.Value.Single());
                extentLOJs.Add(principalExtent, extentLojNode);
            }

            // Construct LOJ nesting inside extentLOJs based on the information in pkFkMap.
            // Also, track nested extents using nestedExtents.
            // Example:
            // We start with nestedExtents empty extentLOJs as such:
            //      tBaseType -> LOJ(BaseTypeNode)
            //      tDerivedType1 -> LOJ(DerivedType1Node)*
            //      tDerivedType2 -> LOJ(DerivedType2Node)**
            // Note that * and ** represent object references. So each time something is nested, 
            // we don't clone, but nest the original LOJ. When we get to processing the extent of that LOJ,
            // we might add other children to that nested LOJ.
            // As we walk pkFkMap, we end up with this:
            //      tBaseType -> LOJ(BaseTypeNode, LOJ(DerivedType1Node)*, LOJ(DerivedType2Node)**)
            //      tDerivedType1 -> LOJ(DerivedType1Node)*
            //      tDerivedType2 -> LOJ(DerivedType2Node)**
            // nestedExtens = (tDerivedType1, tDerivedType2)
            var nestedExtents = new Dictionary<EntitySet, EntitySet>(EqualityComparer<EntitySet>.Default);
            foreach (var m in pkFkMap.KeyValuePairs)
            {
                var principalExtent = m.Key;
                foreach (var fkExtent in m.Value)
                {
                    OpCellTreeNode fkExtentLOJ;
                    if (extentLOJs.TryGetValue(fkExtent, out fkExtentLOJ) &&
                        // make sure we don't nest twice and we don't create a cycle.
                        !nestedExtents.ContainsKey(fkExtent)
                        && !CheckLOJCycle(fkExtent, principalExtent, nestedExtents))
                    {
                        extentLOJs[m.Key].Add(fkExtentLOJ);
                        nestedExtents.Add(fkExtent, principalExtent);
                    }
                }
            }

            // Now we need to grab the LOJs that have not been nested and add them to the result.
            // All LOJs that have been nested must be somewhere inside the LOJs that have not been nested,
            // so they as well end up in the result as part of the unnested ones.
            foreach (var m in extentLOJs)
            {
                if (!nestedExtents.ContainsKey(m.Key))
                {
                    // extentLOJ represents (Vx LOJ Vy LOJ(Vm LOJ Vn)) where Vx is the original node from rootNode.Children or resultIJDriverChildren.
                    var extentLOJ = m.Value;
                    if (resultIJDriverChildren != null
                        && resultIJDriverChildren.Contains(extentLOJ.Children[0]))
                    {
                        resultIJDriver.Add(extentLOJ);
                    }
                    else
                    {
                        result.Add(extentLOJ);
                    }
                }
            }

            return result.Flatten();
        }
        private CellTreeNode ConvertUnionsToNormalizedLOJs(CellTreeNode rootNode)
        {
            // Recursively, transform the subtrees rooted at rootNode's children.
            for (var i = 0; i < rootNode.Children.Count; i++)
            {
                // Method modifies input as well.
                rootNode.Children[i] = ConvertUnionsToNormalizedLOJs(rootNode.Children[i]);
            }

            // We rewrite only LOJs.
            if (rootNode.OpType != CellTreeOpType.LOJ ||
                rootNode.Children.Count < 2)
            {
                return(rootNode);
            }

            // Create the resulting LOJ node.
            var result = new OpCellTreeNode(m_viewgenContext, rootNode.OpType);

            // Create working collection for the LOJ children.
            var children = new List <CellTreeNode>();

            // If rootNode looks something like ((V0 IJ V1) LOJ V2 LOJ V3),
            // and it turns out that there are FK associations from V2 or V3 pointing, let's say at V0,
            // then we want to rewrite the result as (V1 IJ (V0 LOJ V2 LOJ V3)).
            // If we don't do this, then plan compiler won't have a chance to eliminate LOJ V2 LOJ V3.
            // Hence, flatten the first child or rootNode if it's IJ, but remember that its parts are driving nodes for the LOJ,
            // so that we don't accidentally nest them.
            OpCellTreeNode         resultIJDriver         = null;
            HashSet <CellTreeNode> resultIJDriverChildren = null;

            if (rootNode.Children[0].OpType
                == CellTreeOpType.IJ)
            {
                // Create empty resultIJDriver node and add it as the first child (driving) into the LOJ result.
                resultIJDriver = new OpCellTreeNode(m_viewgenContext, rootNode.Children[0].OpType);
                result.Add(resultIJDriver);

                children.AddRange(rootNode.Children[0].Children);
                resultIJDriverChildren = new HashSet <CellTreeNode>(rootNode.Children[0].Children);
            }
            else
            {
                result.Add(rootNode.Children[0]);
            }

            // Flatten unions in non-driving nodes: (V0 LOJ (V1 Union V2 Union V3)) -> (V0 LOJ V1 LOJ V2 LOJ V3)
            foreach (var child in rootNode.Children.Skip(1))
            {
                var opNode = child as OpCellTreeNode;
                if (opNode != null &&
                    opNode.OpType == CellTreeOpType.Union)
                {
                    children.AddRange(opNode.Children);
                }
                else
                {
                    children.Add(child);
                }
            }

            // A dictionary that maps an extent to the nodes that are from that extent.
            // We want a ref comparer here.
            var extentMap = new KeyToListMap <EntitySet, LeafCellTreeNode>(EqualityComparer <EntitySet> .Default);

            // Note that we skip non-leaf nodes (non-leaf nodes don't have FKs) and attach them directly to the result.
            foreach (var child in children)
            {
                var leaf = child as LeafCellTreeNode;
                if (leaf != null)
                {
                    EntitySetBase extent = GetLeafNodeTable(leaf);
                    if (extent != null)
                    {
                        extentMap.Add((EntitySet)extent, leaf);
                    }
                }
                else
                {
                    if (resultIJDriverChildren != null &&
                        resultIJDriverChildren.Contains(child))
                    {
                        resultIJDriver.Add(child);
                    }
                    else
                    {
                        result.Add(child);
                    }
                }
            }

            // We only deal with simple cases - one node per extent, remove the rest from children and attach directly to result.
            var nonTrivial = extentMap.KeyValuePairs.Where(m => m.Value.Count > 1).ToArray();

            foreach (var m in nonTrivial)
            {
                extentMap.RemoveKey(m.Key);
                foreach (var n in m.Value)
                {
                    if (resultIJDriverChildren != null &&
                        resultIJDriverChildren.Contains(n))
                    {
                        resultIJDriver.Add(n);
                    }
                    else
                    {
                        result.Add(n);
                    }
                }
            }
            Debug.Assert(extentMap.KeyValuePairs.All(m => m.Value.Count == 1), "extentMap must map to single nodes only.");

            // Walk the extents in extentMap and for each extent build PK -> FK1(PK1), FK2(PK2), ... map
            // where PK is the primary key of the left extent, and FKn(PKn) is an FK of a right extent that
            // points to the PK of the left extent and is based on the PK columns of the right extent.
            // Example:
            //           table tBaseType(Id int, c1 int), PK = (tBaseType.Id)
            //           table tDerivedType1(Id int, c2 int), PK1 = (tDerivedType1.Id), FK1 = (tDerivedType1.Id -> tBaseType.Id)
            //           table tDerivedType2(Id int, c3 int), PK2 = (tDerivedType2.Id), FK2 = (tDerivedType2.Id -> tBaseType.Id)
            // Will produce:
            //           (tBaseType) -> (tDerivedType1, tDerivedType2)
            var pkFkMap = new KeyToListMap <EntitySet, EntitySet>(EqualityComparer <EntitySet> .Default);
            // Also for each extent in extentMap, build another map (extent) -> (LOJ node).
            // It will be used to construct the nesting in the next step.
            var extentLOJs = new Dictionary <EntitySet, OpCellTreeNode>(EqualityComparer <EntitySet> .Default);

            foreach (var extentInfo in extentMap.KeyValuePairs)
            {
                var principalExtent = extentInfo.Key;
                foreach (var fkExtent in GetFKOverPKDependents(principalExtent))
                {
                    // Only track fkExtents that are in extentMap.
                    ReadOnlyCollection <LeafCellTreeNode> nodes;
                    if (extentMap.TryGetListForKey(fkExtent, out nodes))
                    {
                        // Make sure that we are not adding resultIJDriverChildren as FK dependents - we do not want them to get nested.
                        if (resultIJDriverChildren == null ||
                            !resultIJDriverChildren.Contains(nodes.Single()))
                        {
                            pkFkMap.Add(principalExtent, fkExtent);
                        }
                    }
                }
                var extentLojNode = new OpCellTreeNode(m_viewgenContext, CellTreeOpType.LOJ);
                extentLojNode.Add(extentInfo.Value.Single());
                extentLOJs.Add(principalExtent, extentLojNode);
            }

            // Construct LOJ nesting inside extentLOJs based on the information in pkFkMap.
            // Also, track nested extents using nestedExtents.
            // Example:
            // We start with nestedExtents empty extentLOJs as such:
            //      tBaseType -> LOJ(BaseTypeNode)
            //      tDerivedType1 -> LOJ(DerivedType1Node)*
            //      tDerivedType2 -> LOJ(DerivedType2Node)**
            // Note that * and ** represent object references. So each time something is nested,
            // we don't clone, but nest the original LOJ. When we get to processing the extent of that LOJ,
            // we might add other children to that nested LOJ.
            // As we walk pkFkMap, we end up with this:
            //      tBaseType -> LOJ(BaseTypeNode, LOJ(DerivedType1Node)*, LOJ(DerivedType2Node)**)
            //      tDerivedType1 -> LOJ(DerivedType1Node)*
            //      tDerivedType2 -> LOJ(DerivedType2Node)**
            // nestedExtens = (tDerivedType1, tDerivedType2)
            var nestedExtents = new Dictionary <EntitySet, EntitySet>(EqualityComparer <EntitySet> .Default);

            foreach (var m in pkFkMap.KeyValuePairs)
            {
                var principalExtent = m.Key;
                foreach (var fkExtent in m.Value)
                {
                    OpCellTreeNode fkExtentLOJ;
                    if (extentLOJs.TryGetValue(fkExtent, out fkExtentLOJ)
                        &&
                        // make sure we don't nest twice and we don't create a cycle.
                        !nestedExtents.ContainsKey(fkExtent) &&
                        !CheckLOJCycle(fkExtent, principalExtent, nestedExtents))
                    {
                        extentLOJs[m.Key].Add(fkExtentLOJ);
                        nestedExtents.Add(fkExtent, principalExtent);
                    }
                }
            }

            // Now we need to grab the LOJs that have not been nested and add them to the result.
            // All LOJs that have been nested must be somewhere inside the LOJs that have not been nested,
            // so they as well end up in the result as part of the unnested ones.
            foreach (var m in extentLOJs)
            {
                if (!nestedExtents.ContainsKey(m.Key))
                {
                    // extentLOJ represents (Vx LOJ Vy LOJ(Vm LOJ Vn)) where Vx is the original node from rootNode.Children or resultIJDriverChildren.
                    var extentLOJ = m.Value;
                    if (resultIJDriverChildren != null &&
                        resultIJDriverChildren.Contains(extentLOJ.Children[0]))
                    {
                        resultIJDriver.Add(extentLOJ);
                    }
                    else
                    {
                        result.Add(extentLOJ);
                    }
                }
            }

            return(result.Flatten());
        }
        private CellTreeNode ConvertUnionsToNormalizedLOJs(CellTreeNode rootNode)
        {
            for (int index = 0; index < rootNode.Children.Count; ++index)
            {
                rootNode.Children[index] = this.ConvertUnionsToNormalizedLOJs(rootNode.Children[index]);
            }
            if (rootNode.OpType != CellTreeOpType.LOJ || rootNode.Children.Count < 2)
            {
                return(rootNode);
            }
            OpCellTreeNode         opCellTreeNode1  = new OpCellTreeNode(this.m_viewgenContext, rootNode.OpType);
            List <CellTreeNode>    cellTreeNodeList = new List <CellTreeNode>();
            OpCellTreeNode         opCellTreeNode2  = (OpCellTreeNode)null;
            HashSet <CellTreeNode> cellTreeNodeSet  = (HashSet <CellTreeNode>)null;

            if (rootNode.Children[0].OpType == CellTreeOpType.IJ)
            {
                opCellTreeNode2 = new OpCellTreeNode(this.m_viewgenContext, rootNode.Children[0].OpType);
                opCellTreeNode1.Add((CellTreeNode)opCellTreeNode2);
                cellTreeNodeList.AddRange((IEnumerable <CellTreeNode>)rootNode.Children[0].Children);
                cellTreeNodeSet = new HashSet <CellTreeNode>((IEnumerable <CellTreeNode>)rootNode.Children[0].Children);
            }
            else
            {
                opCellTreeNode1.Add(rootNode.Children[0]);
            }
            foreach (CellTreeNode cellTreeNode in rootNode.Children.Skip <CellTreeNode>(1))
            {
                OpCellTreeNode opCellTreeNode3 = cellTreeNode as OpCellTreeNode;
                if (opCellTreeNode3 != null && opCellTreeNode3.OpType == CellTreeOpType.Union)
                {
                    cellTreeNodeList.AddRange((IEnumerable <CellTreeNode>)opCellTreeNode3.Children);
                }
                else
                {
                    cellTreeNodeList.Add(cellTreeNode);
                }
            }
            KeyToListMap <EntitySet, LeafCellTreeNode> keyToListMap1 = new KeyToListMap <EntitySet, LeafCellTreeNode>((IEqualityComparer <EntitySet>)EqualityComparer <EntitySet> .Default);

            foreach (CellTreeNode child in cellTreeNodeList)
            {
                LeafCellTreeNode leaf = child as LeafCellTreeNode;
                if (leaf != null)
                {
                    EntitySetBase leafNodeTable = (EntitySetBase)BasicViewGenerator.GetLeafNodeTable(leaf);
                    if (leafNodeTable != null)
                    {
                        keyToListMap1.Add((EntitySet)leafNodeTable, leaf);
                    }
                }
                else if (cellTreeNodeSet != null && cellTreeNodeSet.Contains(child))
                {
                    opCellTreeNode2.Add(child);
                }
                else
                {
                    opCellTreeNode1.Add(child);
                }
            }
            foreach (KeyValuePair <EntitySet, List <LeafCellTreeNode> > keyValuePair in keyToListMap1.KeyValuePairs.Where <KeyValuePair <EntitySet, List <LeafCellTreeNode> > >((Func <KeyValuePair <EntitySet, List <LeafCellTreeNode> >, bool>)(m => m.Value.Count > 1)).ToArray <KeyValuePair <EntitySet, List <LeafCellTreeNode> > >())
            {
                keyToListMap1.RemoveKey(keyValuePair.Key);
                foreach (LeafCellTreeNode leafCellTreeNode in keyValuePair.Value)
                {
                    if (cellTreeNodeSet != null && cellTreeNodeSet.Contains((CellTreeNode)leafCellTreeNode))
                    {
                        opCellTreeNode2.Add((CellTreeNode)leafCellTreeNode);
                    }
                    else
                    {
                        opCellTreeNode1.Add((CellTreeNode)leafCellTreeNode);
                    }
                }
            }
            KeyToListMap <EntitySet, EntitySet>    keyToListMap2 = new KeyToListMap <EntitySet, EntitySet>((IEqualityComparer <EntitySet>)EqualityComparer <EntitySet> .Default);
            Dictionary <EntitySet, OpCellTreeNode> dictionary    = new Dictionary <EntitySet, OpCellTreeNode>((IEqualityComparer <EntitySet>)EqualityComparer <EntitySet> .Default);

            foreach (KeyValuePair <EntitySet, List <LeafCellTreeNode> > keyValuePair in keyToListMap1.KeyValuePairs)
            {
                EntitySet key = keyValuePair.Key;
                foreach (EntitySet fkOverPkDependent in BasicViewGenerator.GetFKOverPKDependents(key))
                {
                    ReadOnlyCollection <LeafCellTreeNode> valueCollection;
                    if (keyToListMap1.TryGetListForKey(fkOverPkDependent, out valueCollection) && (cellTreeNodeSet == null || !cellTreeNodeSet.Contains((CellTreeNode)valueCollection.Single <LeafCellTreeNode>())))
                    {
                        keyToListMap2.Add(key, fkOverPkDependent);
                    }
                }
                OpCellTreeNode opCellTreeNode3 = new OpCellTreeNode(this.m_viewgenContext, CellTreeOpType.LOJ);
                opCellTreeNode3.Add((CellTreeNode)keyValuePair.Value.Single <LeafCellTreeNode>());
                dictionary.Add(key, opCellTreeNode3);
            }
            Dictionary <EntitySet, EntitySet> nestedExtents = new Dictionary <EntitySet, EntitySet>((IEqualityComparer <EntitySet>)EqualityComparer <EntitySet> .Default);

            foreach (KeyValuePair <EntitySet, List <EntitySet> > keyValuePair in keyToListMap2.KeyValuePairs)
            {
                EntitySet key = keyValuePair.Key;
                foreach (EntitySet entitySet in keyValuePair.Value)
                {
                    OpCellTreeNode opCellTreeNode3;
                    if (dictionary.TryGetValue(entitySet, out opCellTreeNode3) && !nestedExtents.ContainsKey(entitySet) && !BasicViewGenerator.CheckLOJCycle(entitySet, key, nestedExtents))
                    {
                        dictionary[keyValuePair.Key].Add((CellTreeNode)opCellTreeNode3);
                        nestedExtents.Add(entitySet, key);
                    }
                }
            }
            foreach (KeyValuePair <EntitySet, OpCellTreeNode> keyValuePair in dictionary)
            {
                if (!nestedExtents.ContainsKey(keyValuePair.Key))
                {
                    OpCellTreeNode opCellTreeNode3 = keyValuePair.Value;
                    if (cellTreeNodeSet != null && cellTreeNodeSet.Contains(opCellTreeNode3.Children[0]))
                    {
                        opCellTreeNode2.Add((CellTreeNode)opCellTreeNode3);
                    }
                    else
                    {
                        opCellTreeNode1.Add((CellTreeNode)opCellTreeNode3);
                    }
                }
            }
            return(opCellTreeNode1.Flatten());
        }