0

I have a question about the many to many relation in an ASP.NET MVC application. I have created the database with following code:

CREATE TABLE [dbo].[Address] (
[AddressId] INT          IDENTITY (1, 1) NOT NULL,
[Country]   VARCHAR (50) NOT NULL,
[City]      VARCHAR (50) NOT NULL,
[Street]    VARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([AddressId] ASC)
);


CREATE TABLE [dbo].[Employee] (
[EmployeeId] INT          IDENTITY (1, 1) NOT NULL,
[Name]       VARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
);

CREATE TABLE [dbo].[Employee_Address] (
[Employee_EmployeeId] INT NOT NULL,
[Address_AddressId]   INT NOT NULL,
PRIMARY KEY CLUSTERED ([Employee_EmployeeId] ASC, [Address_AddressId] ASC),
CONSTRAINT [FK_Employee_EmployeeId] FOREIGN KEY ([Employee_EmployeeId]) REFERENCES [dbo].[Employee] ([EmployeeId]),
CONSTRAINT [FK_Address_AddressId] FOREIGN KEY ([Address_AddressId]) REFERENCES [dbo].[Address] ([AddressId])
);

Two tables (address and employee) with a link table (employee_address). And I have the following classes:

public partial class Employee
{
    public Employee()
    {
        this.Addresses = new HashSet<Address>();
    }

    public int EmployeeId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Address> Addresses { get; set; }
}

public partial class Address
{
    public Address()
    {
        this.Employees = new HashSet<Employee>();
    }

    public int AddressId { get; set; }
    public string Country { get; set; }
    public string City { get; set; }
    public string Street { get; set; }

    public virtual ICollection<Employee> Employees { get; set; }
}

public partial class Database1Entities : DbContext
{
    public Database1Entities()
        : base("name=Database1Entities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
        .HasMany<Address>(s => s.Addresses)
        .WithMany(c => c.Employees)
        .Map(cs =>
        {
            cs.MapLeftKey("EmployeeId");
            cs.MapRightKey("AddressId");
            cs.ToTable("Employee_Address");
        });
    }

    public virtual DbSet<Address> Addresses { get; set; }
    public virtual DbSet<Employee> Employees { get; set; }
}

Now I want that when I create an employee, I can assign one or multiple addresses to the employee with something like checkboxes or a dropdown. How can I accomplish this?

I have tried the answer in this topic

But I can't figure out what I need to add from the answer to my project.

Thanks in advance for any help.

Community
  • 1
  • 1
Barry The Wizard
  • 1,258
  • 2
  • 12
  • 25
  • This could be a huge answer. Do you have any UI components? Do you have any APIs? Any methods to Add/Update/Delete for your entities? There is so much missing here to even poke at a starting point – Mark C. Oct 27 '16 at 20:18
  • Is Employee to Address really a many-to-many relationship? Do you often have multiple employees with the same address? Seems like making it one-to-many with the occasional duplicate would be cleaner overall. – D Stanley Oct 27 '16 at 20:19
  • @Mark C. I have just a clean project with the models for employee and address. And the scaffolded controllers for both. Nothing more in this project. I'm trying to understand the many to many relationship in a clean and new project. – Barry The Wizard Oct 27 '16 at 20:20
  • 1
    That said, your UI would need to either have fields to add a new address, or pick from a list of existing addresses across the whole domain. Considering that most addresses are NOT shared I can see that getting ugly quickly. – D Stanley Oct 27 '16 at 20:20
  • As for the many-to-many relationship, I would scrap the join table and add `EmployeeID` to the Address table – Mark C. Oct 27 '16 at 20:21

0 Answers0