EF6 One-To-Many Relationship with 2 tables in Legacy SQL Database

Question

I am trying to configure a One-To-Many relationship where the second table has a reference to the first table. The tables are in an existing legacy Sql Server database.

I have a Customer table and a CustomerBranches table like so:

I have a POCO that will allow a Customer object to navigate to its BranchCustomers (Children) if it has any, and also to navigate to its ParentCustomer (Parent) if itself is a Child.

public class Customer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Customer> BranchCustomers { get; set; }
    public virtual Customer ParentCustomer { get; set; }
}

I can't get this to work with data annotations or with the Fluent API.

The only way I can get the relationship to "sort" of work in EF is to use a many-to-many relationship using the Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{    
        modelBuilder.Entity<Customer>()
            .HasMany<Customer>(s => s.BranchCustomers)
            .WithRequired(c => c.ParentCustomers)
            .Map(cs =>
            {
                cs.MapLeftKey("ParentCustId");
                cs.MapRightKey("ChildCustId");
                cs.ToTable("CustomerBranches");
            });

    }

and the POCO to:

public class Customer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Customer> BranchCustomers { get; set; }
    public virtual List<Customer> ParentCustomers { get; set; }
}

In this case, when the Customer is a child, the ParentCustomers collection will only have One customer. It's a kludge, I know. What is the appropriate approach in this situation?

Thank you.

The design of the `CustomerBranches` database table implies *many-to-many* relationship, hence the "appropriate" approach probably is to keep the "sort of work" entity design. The original `Customer` model cannot be mapped to such table structure anyway. — Ivan Stoev, Feb 18 '19 at 09:47

Roman Pokrovskij · Answer 1 · 2019-02-19T15:21:57.250

ORM is not an object database. ORM is what it is - just a mapping tool. Map tables to classes, map FK to references.

But you can add your own "tooling" properties to entity. Why not:

 public class Customer
 {
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Customer> BranchCustomers { get; set; }
    public virtual List<Customer> ParentCustomers { get; set; }
    public Customer ParentCustomer { 
           get{
              return BranchCustomers.FirstOrDefault(); 
           }
    }
    public void SetParent(var p) { 
              return BranchCustomers.Add(p);
    }
}

Of course this increases the number of abstraction leaks.

score 0 · Answer 2 · answered Feb 19 '19 at 17:09

If I got it right, it looks like you are trying to implement (part of) the composite pattern in theory. But your tables structure is misleading. So the best option would be first to reorganize the POCO classes. Because there are no extra advantages of having this extra table (or did I miss something?).

You could just have one unique table of Customers with an extra column referencing its ParentCustomerID. Then when Customer is on top with no parent, it's ParentCustomerID would be set to null.

Hence, your POCO classes should look something like this:

public abstract class AbstractCustomer{
   [Key]
   public int Id { get; set; }
   public string Name { get; set; }

   public Guid? ParentCustomerID { get; set; }
   public virtual Customer ParentCustomer { get; set; }
}

public class Customer : AbstractCustomer
{        
    public virtual List<AbstractCustomer> BranchCustomers { get; set; }   
}

Note the ? operator and that the parent class must be abstract. Then the EF configuration would look something like this using fluent API:

modelBuilder.Entity<AbstractCustomer>().ToTable("Customer").HasOptional(a => a.ParentCustomer).WithMany(s => s.BranchCustomers).HasForeignKey(fk => fk.ParentCustomerID);

Hope it helps & happy coding!

EF6 One-To-Many Relationship with 2 tables in Legacy SQL Database

2 Answers2