Entity Framework Self-Referencial Many-To-Many with Payload ( Bill of Materials BOM )

I asked this question a while back without an answer, I believe it may be the most bizarre implementation for the EF, although it is quite practical. Here is my previous post:

Entity Framework Self Referencing Hierarchical Many To Many

I've decided to ask again with the additional keyword Payload and a clearer understanding.

In the Apress publication: Entity Framework 4.0 Recipes: A Problem-Solution Approach, recipe 2-6 on pg. 26 is titled Modeling a Many-To-Many Relationship with a Payload. Recipe 2-7 is titled Modeling a Self-Referential Relationship.

Reading that would give you a foundation for what my problem is, the difference is that I have a Self-Referential Many-To-Many with Payload which is not discussed in the book or anywhere in the universe to my knowledge.

To put it very simply I have a Resource table with ID and Type fields. I also have a ResourceHierarchy table which serves as the junction or bridge table in that it has a composite primary key made up of a Parent_ID and Child_ID and a composite foreign key. So a Resource entity can either serve as a Child Resource or Parent Resource or BOTH.

By now the Entity Framework would have generated the Resource Entity but the ResourceHierarchy Entity would actually be hidden from the EDMX Designer since in the EDMX file it is being treated as only a relationship and not an entity.

The generated Resource Entity will have navigation properties like Resources and Resources1 which I renamed Parents and Children.

So I can write code like this: (It doesn't do anything I'm just showing some examples)

List<Resource> listResources = Context.Resouces.ToList()
foreach (Resource resc in listResources)
{
List<Resource> listParents = resc.Parents.ToList()
List<Resource> listChildren = resc.Children.ToList()
foreach (Resource parent in listParents)
{
Console.WriteLine(parent.Type);
}
foreach (Resource child in listChildren)
{
Console.WriteLine(child.Type);
}
resc.Children.Add(new Resource());
Console.WriteLine(resc.Parents.First().Children.First().Type);
}

Lets say I have a Resource that is being shared by two other Resouces. The two other resources would be the Parents of said Resource. Said Resource is also the only Child of each of it's Parents. Yes, a Resource can have three or more "Parents", even two dads if you like but would ancestors share a child? Not on my watch. So anyway... we have to think of this from a real world scenario for it to make sense from this point on.

Here is some code to get us started:

Resource parent1 = new Resource();
Resource parent2 = new Resource();
Resource child = new Resource();

parent1.Type = "WidgetA";
parent2.Type = "WidgetB";
child.Type = "1/4 Screw"

parent1.Children.Add(child);
parent2.Children.Add(child);

Product product1 = new Product();
Product product2 = new Product();

product1.Resources.Add(parent1);
product2.Resources.Add(parent2);

So we have two widgets that have a screw. WidgetA and WidgetB are listed as products on a website. What if WidgetA sells, what will happen to WidgetB's screw? So now you see we need a Quantity property on the Resource Entity.

Fast forward many months to were I currently am in my project and assume the fetal position after realizing how limited the EF is.

This part gets a little more complicated. If

child.Quantity = 4
parent1.Quantity = 1
parent2.Quantity = 1

How will we know or set it up so that we can assign 2 of the child to parent1 and 2 of the child to parent2?

That can only be done via adding another quantity(int) column which we'll call "Required" to the ResourceHierarchy table so it would look like:

Parent_ID int not null,
Child_ID int not null,
Required int not null default 1

So we've attached the payload to the ResourceHierarchy Entity in the db. If we regenerate the model from the EDMX designer ResourceHierarchy is no longer a Relationship but is now an Entity. If I choose instead to only Refresh the ResourceHierarchy table from the EDMX designer I can see the Required property in the Storage Model but it is no where in the Conceptual or Mapping models since the ResourceHierarchy would be a relationship. However if I delete the Resource table and the ResourceHierarchy table and regenerate them the ResourceHierarchy table is now visible with the Required column and it is now an Entity.

It is possible to work with this setup but it's so much more difficult than simply being able to access the ResourceHierarchy Relationship and retrieve the Required property. Even if the ResourceHierarchy EntityType includes the Required property in the Storage Model I cannot access the Required property from code after accessing the AssociationSet. If the ResourceHierarchy table is a relationship in the EF it looks like this in the Storage Model.

<EntityType Name="ResourceHierarchy">
          <Key>
            <PropertyRef Name="Parent_ID" />
            <P开发者_C百科ropertyRef Name="Child_ID" />
          </Key>
          <Property Name="Parent_ID" Type="int" Nullable="false" />
          <Property Name="Child_ID" Type="int" Nullable="false" />
          <Property Name="Required" Type="int" Nullable="false" />
</EntityType>

If I try to merge the generated EDMX files I get the error telling me that the ResourceHierarchy can either be an Entity or a Relationship but not both.

This is called a Many-To-Many with Payload. Trying to implement this with a Self-Referential Hierarchy is a nightmare in the EF. I'm working with VS2010, SQL 2008, and the .NET 4.0 Framework.

The concept is that I want to have Products that are made up of Resources which themselves are made up of other Resources or serve to make up other Resources and each one requires a certain quantity amount of Resources. It's basically a Bill of Materials BOM. Does the EF not support the BOM model?

Would the new HIERARCHYID feature in SQL Server 2008 help by any chance?

---

So I ended up with a surprisingly graceful solution.

CREATE TABLE Resource
(
ID INT NOT NULL,
Type VARCHAR(25) NOT NULL
)

ALTER TABLE Resource
ADD CONSTRAINT PK_Resource PRIMARY KEY (ID)

CREATE TABLE ResourceHierarchy
(
Ancestor_ID INT NOT NULL,
Descendant_ID INT NOT NULL,
Required INT NOT NULL DEFAULT 1
)

ALTER TABLE ResourceHierarchy
ADD CONSTRAINT PK_ResourceHierarchy PRIMARY KEY (Ancestor_ID, Descendant_ID)
ALTER TABLE ResourceHierarchy
ADD CONSTRAINT FK_Ancestors FOREIGN KEY (Ancestor_ID) REFERENCES Resource (ID)
ALTER TABLE ResourceHierarchy
ADD CONSTRAINT FK_Descendants FOREIGN KEY (Descendant_ID) REFERENCES Resource (ID)

When the EDMX was generated I renamed the Resource Entity navigation properties from ResourceHierarchy to DescendantRelationships and ResourceHierarchy1 to AncestorRelationships. I then renamed the ResourceHierarchy Entity navigation properties from Resource to Descendant and Resource1 to Ancestor.

Whereas before I could write code like this:

Resource resource = new Resource();
resource.Descendants.Add(new Resource());
foreach (Resource descendant in resource.descendants)
{
descendant.Type = "Brawr";
List<Resource> ancestors = descendant.Ancestors.ToList();
}

Of course this approach did not allow me to access the Required property.

Now I must do the following:

Resource ancestor = new Resource();
Resource descendant = new Resource();

ResourceHierarchy rh = new ResourceHierarchy { Ancestor = ancestor, Descendant = descendant, Required = 1 };

ancestor.DescendantRelationships.Add(rh);

But check it out I can now get to the Required property like so:

int req = ancestor.DescendantRelationships.First().Required;

One might rename the Required field to RequiredDescendants since Descendants don't need a required amount of Ancestors, only Ancestors need to specify how many Descendants are required.

So it's a hop, but a graceful one.

Please let me know your thoughts, especially if I've overlooked a gotcha or something.

---

Something to watch out for...

When we want to add a descendant to a resource we need to remember that DescendantRelationships gives us the hierarchy where the referenced resource is acting as descendants to other resources.

So in order to add a descendant to a resource we must do the following:

Resource resource = new Resource { Type = "WidgetA" };
Resource descendant = new Resource { Type = "Screw" };
resource.AncestorRelationships.Add(new ResourceHierarchy { Descendant = descendant, Required = 1 };

Of course this all depends on how you name your navigation properties, I'm just saying be careful. AncestorRelationships would be the go to navigation property in order to add descendants and vice versa. A better thing to do might be to rename AncestorRelationships to AncestorRoles and DescendantRelationships to DescendantRoles.

AncestorRoles would translate to ResourceHierarchiesWhereCurrentResourceIsAnAncestor. DescendantRoles would translate to ResourceHierarchiesWhereCurrentResourceIsADescendant.

So we could do:

// print descendant types
foreach (ResourceHierarchy rh in resource.AncestorRoles)
{
Console.WriteLine(rh.Descendant.ResourceType.Type);
}

Sorry to change up the nomenclature so much but I think it helps understand what's going on.

---

The MSDN Libary link : http://msdn.microsoft.com/en-us/library/ms742451.aspx is titled PropertyPath XAML Syntax and has a section titled Source Traversal (Binding to Hierarchies of Collections)

This is the HierarchicalDataTemplate I want to use:

<HierarchicalDataTemplate DataType="{x:Type Inventory:Resource}" ItemsSource="{Binding Path=AncestorRoles/Descendant}">
<CustomControls:ResourceTreeItem Type="{Binding ResourceType.Type}"/>
</HierarchicalDataTemplate>

Only the first Resource is displayed. After the following code runs the TreeView shows one ResourceTreeItem in the TreeView.

ObservableCollection<Resource> Resources = new ObservableCollection<Resource>
Resources.Add(new Resource { ResourceType.Type = "WidgetA" });
MyTreeView.ItemsSource = Resources;

So that works. However when I run the following code the TreeView is not updated.

Resource resource = MyTreeView.Items[0] as Resource;
resource.AncestorRoles.Add( new ResourceHierarchy { Descendant = new Resource { ResourceType = "Screw" }, Required = 1 } )

Even if I get the CollectionViewSource of the TreeView.ItemsSource and call Refresh() it doesn't show up. I triple checked the relationships and it's all there.

I think it's a bug with the PropertyPath Traversal syntax.

The solution was to add a TreeParent property to the Resource partial class declaration and utilize 3 ValueConverters which is a long story but it's because the datacontext naturally alternates from a Resource to a ResourceHierarchy. The RequiredConverter is the one that checks the TreeParent and finds the Required property Payload.

class ValidatorConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        {
            Resource resource = value as Resource;
            ResourceHierarchy rh = value as ResourceHierarchy;
            if (resource != null)
            {
                value = resource;
            }
            else if (rh != null)
            {
                value = rh.Descendant;
            }

            return value;
        }

        public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

    class ResourceConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        {
            Resource resource = value as Resource;
            ResourceHierarchy hierarchy = value as ResourceHierarchy;

            if (resource != null)
            {
                if (resource.AncestorRoles.Count > 0)
                {
                    value = resource.AncestorRoles;
                }
                else
                {
                    value = resource;
                }
            }
            else if (hierarchy != null)
            {
                value = hierarchy.Descendant.AncestorRoles;
            }

            return value;
        }

        public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

    class RequiredConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        {
            Resource resource = (Resource)value;
            Resource parent = ((Resource)value).TreeParent as Resource;
            if (parent != null)
            {
                value = parent.AncestorRoles.Where(p => p.Descendant == resource).First().Required;
            }
            else
            {
                value = 0;
            }

            return value;
        }

        public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

And here is the final HierarchicalDataTemplate:

<HierarchicalDataTemplate DataType="{x:Type Inventory:Resource}" ItemsSource="{Binding Path=., Converter={StaticResource resourceconv}}">
<StackPanel DataContext="{Binding Path=., Converter={StaticResource validatorconv}}">
<CustomControls:ResourceTreeItem Required="{Binding Path=., Converter={StaticResource requiredconv}}" Free="{Binding Free}" OnHand="{Binding OnHand, Mode=TwoWay}" Type="{Binding ResourceType.Type}"/>
</StackPanel>
</HierarchicalDataTemplate>

The StackPanel only serves to add another DataContext layer. I left in the Free, OnHand and Type properties so you could see that 3 properties are receiving their binding from the StackPanels DataContext and the Required property is doing it's thing like a mad man.

So the moral is if you need a Payload maybe the EF isn't for you.