EF Code First Foreign Keys

Question

Code

I have a couple of classes.

Test.cs

class Test
{
    public int TestId { get; set; }
    public string Name { get; set; }
    public ICollection<LevelNode> Nodes { get; set; }
    public ICollection<AttributeNode> AttributeNodes { get; set; }

    public Test()
    {
        Nodes = new Collection<LevelNode>();
        AttributeNodes = new Collection<AttributeNode>();
    }
}

Node.cs

abstract class Node
{
    public int NodeId { get; set; }

    public string Key { get; set; }

    public string Value { get; set; }

    public virtual Node ParentNode { get; set; }

    public virtual ICollection<AttributeNode> Attributes { get; set; }

    public Test Test { get; set; }

    public Node()
    {
        Attributes = new Collection<AttributeNode>();
    }
}

LevelNode.cs

class LevelNode : Node
{
    public virtual ICollection<LevelNode> Nodes { get; set; }

    public LevelNode() : base()
    {
        Nodes = new Collection<LevelNode>();
    }
}

AttributeNode.cs

class AttributeNode : Node
{
    public int Source { get; set; }

    public AttributeNode() : base()
    {
    }
}

TestCFContext.cs

class TestCFContext : DbContext
{
    public DbSet<Test> Tests { get; set; }

    public TestCFContext()
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    }
}

And the Main function:

static void Main(string[] args)
{
    // Test
    Test t = new Test() { Name = "My Test" };

    // Root & sub
    LevelNode root = new LevelNode() { Key = "root", Test = t };
    LevelNode sub = new LevelNode() { Key = "sub1", Test = t, ParentNode = root };
    root.Nodes.Add(sub);
    t.Nodes.Add(root);

    // Attr1
    AttributeNode attr1 = new AttributeNode() { Key = "Attr1 key", Value = "Attr1 value", Source = 1, Test = t, ParentNode = sub };
    AttributeNode subattr1 = new AttributeNode() { Key = "Subattr1 key", Value = "Subattr1 value", Source = 2, Test = t, ParentNode = attr1 };
    attr1.Attributes.Add(subattr1);
    sub.Attributes.Add(attr1);

    // Attr2
    sub.Attributes.Add(new AttributeNode() { Key = "Attr2 key", Value = "Attr2 value", Source = 3, Test = t, ParentNode = sub });

    // Add to DB
    TestCFContext c = new TestCFContext();
    c.Tests.Add(t);
    c.SaveChanges();

    // Perform search
    IEnumerable<AttributeNode> resultAttributes = t.AttributeNodes.Where(x => x.Key == "Attr2 key" && x.Value == "Attr2 value");
    // => 0 results! :-(
}

Aim

What I want to accomplish is as follows. All LevelNodes and LevelAttributes (both derived classes from Node) contain a reference to the Test object. After saving the hierarchy of nodes, I would like to search for nodes within the test with a specific key and value.

Issue

The data is stored in the database, however at the moment I search for a specific attribute using the AttributeNodes property of the Test, no results are found. Moreover, in the database, the Nodes table contains 3 (!) columns referring to the Tests table, where most values are NULL.

NodeId  Key Value   Source  Discriminator   Node_NodeId ParentNode_NodeId   Test_TestId LevelNode_NodeId    Test_TestId1    Test_TestId2
1   root    NULL    NULL    LevelNode   NULL    NULL    1   NULL    NULL    1
2   sub1    NULL    NULL    LevelNode   NULL    1   1   1   NULL    NULL
3   Attr1 key   Attr1 value 1   AttributeNode   2   2   1   NULL    NULL    NULL
4   Subattr1 key    Subattr1 value  2   AttributeNode   3   3   1   NULL    NULL    NULL
5   Attr2 key   Attr2 value 3   AttributeNode   2   2   1   NULL    NULL    NULL

Question

Is it possible to simply have a single foreign key to the Test table in the database and also have it yield the expected results when querying it using the Nodes and AttributeNodes attributes of the Test class? If this is not possible using EF Code First, what would be the best alternative way to accomplish this?

Answer 1

1) You have a little bug

IEnumerable<AttributeNode> resultAttributes = t.AttributeNodes.Where(x => x.Key == "Attr2 key" && x.Value == "Attr2 value");

should be

IEnumerable<AttributeNode> resultAttributes = c.AttributeNodes.Where(x => x.Key == "Attr2 key" && x.Value == "Attr2 value");

2) You have declared three different foreign key relationships Node -> Test (in node.cs), AttributeNode -> Test, and LevelNode -> Test both in (test.cs). I think you have to model it like this:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace ConsoleApp8
{
    class Test
    {
        public int TestId { get; set; }
        public string Name { get; set; }

        public ICollection<Node> Nodes { get; } = new HashSet<Node>();
        public IEnumerable<LevelNode> LevelNodes
        {
            get
            {
                return Nodes.OfType<LevelNode>(); 
            }
        }

        public IEnumerable<AttributeNode> AttributeNodes
        {
            get
            {
                return Nodes.OfType<AttributeNode>();
            }
        }


    }

    abstract class Node
    {
        public int NodeId { get; set; }

        public string Key { get; set; }

        public string Value { get; set; }

        public virtual Node ParentNode { get; set; }

        public virtual ICollection<AttributeNode> Attributes { get; } = new HashSet<AttributeNode>();

        public Test Test { get; set; }

    }

    class LevelNode : Node
    {
        public virtual ICollection<LevelNode> Nodes { get; } = new HashSet<LevelNode>();

    }

    class AttributeNode : Node
    {
        public int Source { get; set; }

    }
    class TestCFContext : DbContext
    {
        public DbSet<Test> Tests { get; set; }
        public DbSet<LevelNode> LevelNodes { get; set; }

        public DbSet<AttributeNode> AttributeNodes { get; set; }


        public TestCFContext()
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }
    }



    class Program
    {


        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<TestCFContext>());
            // Test
            Test t = new Test() { Name = "My Test" };

            // Root & sub
            LevelNode root = new LevelNode() { Key = "root", Test = t };
            LevelNode sub = new LevelNode() { Key = "sub1", Test = t, ParentNode = root };
            root.Nodes.Add(sub);
            t.Nodes.Add(root);

            // Attr1
            AttributeNode attr1 = new AttributeNode() { Key = "Attr1 key", Value = "Attr1 value", Source = 1, Test = t, ParentNode = sub };
            AttributeNode subattr1 = new AttributeNode() { Key = "Subattr1 key", Value = "Subattr1 value", Source = 2, Test = t, ParentNode = attr1 };
            attr1.Attributes.Add(subattr1);
            sub.Attributes.Add(attr1);

            // Attr2
            sub.Attributes.Add(new AttributeNode() { Key = "Attr2 key", Value = "Attr2 value", Source = 3, Test = t, ParentNode = sub });

            // Add to DB
            using (TestCFContext c = new TestCFContext())
            {
                c.Database.Log = m => Console.WriteLine(m);
                c.Tests.Add(t);
                c.SaveChanges();

            }

            using (TestCFContext c = new TestCFContext())
            {
                c.Database.Log = m => Console.WriteLine(m);
                // Perform search
                IEnumerable<AttributeNode> resultAttributes = c.AttributeNodes.Where(x => x.Key == "Attr2 key" && x.Value == "Attr2 value");
                var numFound = resultAttributes.Count();
                Console.WriteLine($"{numFound} found.");

            }
            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();


        }
    }
}