Multiple lists of same class in Entity Framework

Question

I'm trying to create a data model of companies with relations to the same type in Entity Framework code first. I want to create the relation one-way, so when a company adds another as its customer, the other company doesn't get that company as a supplier automatically.

public class Company {
    [Key]
    public string Id { set; get; }
    public string Name { set; get; }

    public virtual ICollection<Company> Customers { set; get; }
    public virtual ICollection<Company> Suppliers { set; get; }
}

When I update the database like this, I end up with one connection table called "CompanyCompanies".

CompanyCompanies

| Company_Id | Company_Id1 |
|------------|-------------|
| 1234       | 1234        |

I want to create two tables to connect these values, like this:

CompanyCustomers

| Company_Id | Customer_Id |
|------------|-------------|
| 1234       | 1234        |

CompanySuppliers

| Company_Id | Supplier_Id |
|------------|-------------|
| 1234       | 1234        |

I've looked a lot at the fluent api and tried to define the relations there, but I can't seem to get my head around how to do this. I have found a lot of examples using different entities, but only this answer with relations to itself. However, when I implement the solution from that question and update the database I get an error saying that the connection timed out.

modelBuilder.Entity<Company>()
    .HasMany(c => c.Customers)
    .WithMany()
    .Map(m =>
    {
        m.ToTable("CompanyCustomer");
        m.MapLeftKey("CompanyId");
        m.MapRightKey("CustomerId");
    });

Error Number:-2,State:0,Class:11 Execution Timeout Expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.

When I try with both CompanyCustomer and CompanySupplier I get antoher error

Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

I also tried to create a new object for the relation and having the Company object holding a list of CompanyCustomers, but when i updated the database with this I got a connection-table with three columns.

public class CompanyCustomer
{
    [Key, Column(Order = 0)]
    [ForeignKey("Company")]
    public string CompanyId { set; get; }
    [Key, Column(Order = 1)]
    [ForeignKey("Customer")]
    public string CustomerId { set; get; }

    public virtual Company Company { set; get; }
    public virtual Company Customer { set; get; }
}

CompanyCustomer

| CompanyId | CustomerID | Company_Id |
|-----------|------------|------------|
| 1234      | 1234       | 1234       |

I don't understand how i can build several relationship types between the same entity. Can I do this with the help of fluent api or do I have to re-design my model?

The timeout doesn't mean that the model isn't correct, just that the migration takes to much time executing DDL statements. Technically, two of these `HasMany - WithMany` configurations should be OK. — Gert Arnold, Mar 07 '17 at 21:14
When you say you did the implicit `many-to-many` config (with `.HasMany(c => c.Customers).WithMany()`, did you do the same for the other relationship `CompanySuppliers`? When I do so and look at the generated migration, it's exactly what you want. — Ivan Stoev, Mar 07 '17 at 21:16
I updated my question Ivan. I only get the timeout when doing this with one of the collections. When I try with both I get an error saying @objname is ambigous... — Kristoffer Berge, Mar 07 '17 at 21:19
I have no idea what operation is giving you the exception mentioned - all I know is that this is the correct way to configure EF to produce what you want. — Ivan Stoev, Mar 07 '17 at 21:24

score 2 · Accepted Answer · answered Mar 07 '17 at 21:46

The fluent configuration

modelBuilder.Entity<Company>()
    .HasMany(c => c.Customers)
    .WithMany()
    .Map(m =>
    {
        m.ToTable("CompanyCustomer");
        m.MapLeftKey("CompanyId");
        m.MapRightKey("CustomerId");
    });

is indeed the right way to configure the first relationship. You need to do the similar for the second:

modelBuilder.Entity<Company>()
    .HasMany(c => c.Suppliers)
    .WithMany()
    .Map(m =>
    {
        m.ToTable("CompanySupplier");
        m.MapLeftKey("CompanyId");
        m.MapRightKey("SupplierId");
    });

which in my clean EF test environment (latest EF6.1.3 if that matters) produces the following migration:

CreateTable(
    "dbo.Company",
    c => new
        {
            Id = c.String(nullable: false, maxLength: 128),
            Name = c.String(),
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.CompanyCustomer",
    c => new
        {
            CompanyId = c.String(nullable: false, maxLength: 128),
            CustomerId = c.String(nullable: false, maxLength: 128),
        })
    .PrimaryKey(t => new { t.CompanyId, t.CustomerId })
    .ForeignKey("dbo.Company", t => t.CompanyId)
    .ForeignKey("dbo.Company", t => t.CustomerId)
    .Index(t => t.CompanyId)
    .Index(t => t.CustomerId);

CreateTable(
    "dbo.CompanySupplier",
    c => new
        {
            CompanyId = c.String(nullable: false, maxLength: 128),
            SupplierId = c.String(nullable: false, maxLength: 128),
        })
    .PrimaryKey(t => new { t.CompanyId, t.SupplierId })
    .ForeignKey("dbo.Company", t => t.CompanyId)
    .ForeignKey("dbo.Company", t => t.SupplierId)
    .Index(t => t.CompanyId)
    .Index(t => t.SupplierId);

which is exactly what you want.

What about the exceptions you are getting, they might be related to your experiments with the database. Make sure to either start with clean database or remove the Company entity (and CompanyCustomer if it's there) , corresponding DbSets and the fluent configurations, update the database to clean the previous mess and try again.

score 1 · Answer 2 · answered Mar 07 '17 at 21:11

1

You can do this just with attributes. Your issue is you are using the [ForeignKey] attribute on the wrong property - it should be on the navigation properties.

[ForeignKey(nameof(CompanyId))]
public virtual Company Company { set; get; }

[ForeignKey(nameof(CustomerId))]
public virtual Company Customer { set; get; }

Also, you can add [InverseProperty] attributes to the collections on the model. This helps in understanding the relational navigation.

[InverseProperty(nameof(CompanyCustomer.Company))]
public virtual ICollection<Company> Customers { set; get; }

answered Mar 07 '17 at 21:11

Brad M

7,857
1
23
40

Thanks, but I really want to avoid creating a new class just for the relation. Is there a way around this? – Kristoffer Berge Mar 07 '17 at 21:15
Why avoid the new class? Personally, I find it easier to understand and extend than using the fluent api. – Brad M Mar 07 '17 at 21:19

Multiple lists of same class in Entity Framework

2 Answers2