Reputation: 23
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! :-(
}
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.
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
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?
Upvotes: 2
Views: 73
Reputation: 89371
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();
}
}
}
Upvotes: 1