ASP.NET EF6 - 2 Tables & 1 Link Table

Question

I am not the most fluent with ASP.NET/EF, so I am having a bit of a hard time trying to find the best way to accomplish what I need to get done. I'm trying to create a CRUD with an already existing database.

Instead of the Employees table having specific columns for contact information, they designed it to link to the Contact_Info table with Contact_Links being the middle-man.

I have 3 tables:

Employee
Columns: EmployeeID (PK), FirstName, LastName

Employee Links to Contact Links via Contact_ID=EmployeeID

Contact_Links
Columns: ID (PK), Contact_ID (FK), Contact_Info_ID (FK), Type

Contact_Links links to Contact_Info via Contact_Info_ID=ID

Contact_Info
Columns: ID (PK), Data

The type column in Contact_Links determines whether it's an email address, cell phone, fax, etc. So its actually more than just a PK/FK table.

Maybe the easiest way would be to have an Action Method create an Employee first, then in the details page of an option to add a Contact Info item? I'm not sure where to begin in mapping this out.

Any suggestions on what would be the best approach to do what I'm trying would be GREATLY appreciated!

Answer 1

Depends on what your requirements is. If you try to do many to many relationship then best not to use any primary key in join table (or in this case entity) This way you dont have to worry about mapping entity/table at all.

Answer 2

If you are looking to add an employee and their contact info on the same form, then you should use a View Model.

Your View Model will be an amalgamation of the properties you need on both the Employee and Contact into one class. You will then implement your Employee Create/View/Edit screens to expect your EmployeeContactViewModel and you will have access to all the properties you need.

Your Save/Edit methods on the Controller will take an EmployeeContactViewModel as the type. Then you will be able to arrange it into the correct structure to persist to your database.

Have a look at the answer on this question: What is ViewModel in MVC?

A quick google into using View Models and you'll be flying!

EDIT: Thanks for clarifying your problem further.

Once you get your View Model through to your controller, you're going to have to do a series of Inserts.

Here is what your controller method will look like:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create(EmployeeContactViewModel employeecontactviewmodel)
    {
        if (ModelState.IsValid)
        {
            var employee = new EmployeeModel {
                FirstName = employeecontactviewmodel.FirstName,
                LastName = employeecontactviewmodel.LastName
            };

            var contact = new ContactInfoModel {
                Data = employeecontactviewmodel.ContactInfo
            };


            db.Employee.Add(employee);
            db.Contact.Add(contact);
            db.SaveChanges();

            var contactLink = new ContactLinkModel {
                ContactID = employee.ID,
                ContactInfoID = contact.ID
            };

            db.ContactInfo.Add(contactLink);
            db.SaveChanges();

            return RedirectToAction("Index");
        }

        return View(employeecontactviewmodel);
    }