Associative Table of Companies and Users, Many to Many

Question

I am starting my studies in ASP.NET and I have a certain difficulty to make a link between 2 tables of the type Many to Many.

Next I have 2 tables: Company and User, so that a company can own multiple users and at the same time a user can belong to several companies.

This way I created an intermediate table with foreign key for both.

EX:

[TB_EMPRESA] [PK] COD_EMP, NOME_EMP

[TB_USUARIO] [PK] COD_USU, NOME_USU

[TB_USUARIOEMPRESA] [FK]COD_EMP, [FK]COD_USU

But I’m kind of lost on how to ride the controller and the view to enter the data.

Does anyone know any article or documentation that can help me?

I am using ASP.NET MVC5 and Entity Framework 6.

Answer 1

I have answered several times on how to make N to N membership here on the site. Anyway, I’m gonna work this out for you better.

There are two ways to define association N to N in the Entity Framework: via Fluent API, which is a more plastered form because it does not allow a larger associatva table configuration, or by manual mapping, which gives a little more work but is better.

First, define a Model thus:

public class UsuarioEmpresa
{
    [Key]
    public int UsuarioEmpresaId { get; set }
    [Index("IUQ_UsuarioEmpresa_UsuarioId_EmpresaId", IsUnique = true, Order = 1)]
    public int UsuarioId { get; set; }
    [Index("IUQ_UsuarioEmpresa_UsuarioId_EmpresaId", IsUnique = true, Order = 2)]
    public int EmpresaId { get; set; }

    // Você pode definir mais campos aqui

    public virtual Usuario Usuario { get; set; }    
    public virtual Empresa Empresa { get; set; }
}

[Index], introduced in this form from the Entity Framework 6.1.0, ensures the uniqueness of the associative record. Additional validations may be required in the application to avoid strange key duplicity errors for the user.

With that, I’m guessing you have two others Models in its application with something like this:

public class Usuario
{
    [Key]
    public int UsuarioId { get; set; }

    // Outras propriedades aqui

    public virtual ICollection<UsuarioEmpresa> EmpresasUsuario {get; set;}
}

public class Empresa
{
    [Key]
    public int EmpresaId { get; set; }

    // Outras propriedades aqui    

    public virtual ICollection<UsuarioEmpresa> EmpresaUsuarios {get; set;}
}

Once done, we can write a Controller manual or generate a pathway Scaffolding:

using System.Data.Entity;
using System.Threading.Tasks;
using System.Net;
using System.Web.Mvc;
using SeuProjeto.Models;

namespace SeuProjeto.Controllers
{
    public class UsuariosEmpresasController : Controller
    {
        private ApplicationDbContext db = new ApplicationDbContext();

        // GET: UsuariosEmpresas
        public async Task<ActionResult> Index()
        {
            var usuarioEmpresas = db.UsuarioEmpresas.Include(u => u.Empresa).Include(u => u.Usuario);
            return View(await usuarioEmpresas.ToListAsync());
        }

        // GET: UsuariosEmpresas/Details/5
        public async Task<ActionResult> Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            UsuarioEmpresa usuarioEmpresa = await db.UsuarioEmpresas.FindAsync(id);
            if (usuarioEmpresa == null)
            {
                return HttpNotFound();
            }
            return View(usuarioEmpresa);
        }

        // GET: UsuariosEmpresas/Create
        public ActionResult Create()
        {
            ViewBag.EmpresaId = new SelectList(db.Empresas, "EmpresaId", "Nome");
            ViewBag.UsuarioId = new SelectList(db.Usuarios, "UsuarioId", "Nome");
            return View();
        }

        // POST: UsuariosEmpresas/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Create([Bind(Include = "UsuarioEmpresaId,UsuarioId,EmpresaId")] UsuarioEmpresa usuarioEmpresa)
        {
            if (ModelState.IsValid)
            {
                db.UsuarioEmpresas.Add(usuarioEmpresa);
                await db.SaveChangesAsync();
                return RedirectToAction("Index");
            }

            ViewBag.EmpresaId = new SelectList(db.Empresas, "EmpresaId", "Nome", usuarioEmpresa.EmpresaId);
            ViewBag.UsuarioId = new SelectList(db.Usuarios, "UsuarioId", "Nome", usuarioEmpresa.UsuarioId);
            return View(usuarioEmpresa);
        }

        // GET: UsuariosEmpresas/Edit/5
        public async Task<ActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            UsuarioEmpresa usuarioEmpresa = await db.UsuarioEmpresas.FindAsync(id);
            if (usuarioEmpresa == null)
            {
                return HttpNotFound();
            }
            ViewBag.EmpresaId = new SelectList(db.Empresas, "EmpresaId", "Nome", usuarioEmpresa.EmpresaId);
            ViewBag.UsuarioId = new SelectList(db.Usuarios, "UsuarioId", "Nome", usuarioEmpresa.UsuarioId);
            return View(usuarioEmpresa);
        }

        // POST: UsuariosEmpresas/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Edit([Bind(Include = "UsuarioEmpresaId,UsuarioId,EmpresaId")] UsuarioEmpresa usuarioEmpresa)
        {
            if (ModelState.IsValid)
            {
                db.Entry(usuarioEmpresa).State = EntityState.Modified;
                await db.SaveChangesAsync();
                return RedirectToAction("Index");
            }
            ViewBag.EmpresaId = new SelectList(db.Empresas, "EmpresaId", "Nome", usuarioEmpresa.EmpresaId);
            ViewBag.UsuarioId = new SelectList(db.Usuarios, "UsuarioId", "Nome", usuarioEmpresa.UsuarioId);
            return View(usuarioEmpresa);
        }

        // GET: UsuariosEmpresas/Delete/5
        public async Task<ActionResult> Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            UsuarioEmpresa usuarioEmpresa = await db.UsuarioEmpresas.FindAsync(id);
            if (usuarioEmpresa == null)
            {
                return HttpNotFound();
            }
            return View(usuarioEmpresa);
        }

        // POST: UsuariosEmpresas/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> DeleteConfirmed(int id)
        {
            UsuarioEmpresa usuarioEmpresa = await db.UsuarioEmpresas.FindAsync(id);
            db.UsuarioEmpresas.Remove(usuarioEmpresa);
            await db.SaveChangesAsync();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

And Views (also by Scaffolding):

Create.cshtml

@model SeuProjeto.Models.UsuarioEmpresa

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>UsuarioEmpresa</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.UsuarioId, "UsuarioId", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownList("UsuarioId", null, htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.UsuarioId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpresaId, "EmpresaId", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownList("EmpresaId", null, htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.EmpresaId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Delete.cshtml

@model SeuProjeto.Models.UsuarioEmpresa

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>UsuarioEmpresa</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Empresa.Nome)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Empresa.Nome)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Usuario.Nome)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Usuario.Nome)
        </dd>

    </dl>

    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>

Details.cshtml

@model SeuProjeto.Models.UsuarioEmpresa

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>UsuarioEmpresa</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Empresa.Nome)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Empresa.Nome)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Usuario.Nome)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Usuario.Nome)
        </dd>

    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.UsuarioEmpresaId }) |
    @Html.ActionLink("Back to List", "Index")
</p>

Edit.cshtml

@model SeuProjeto.Models.UsuarioEmpresa

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>UsuarioEmpresa</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.UsuarioEmpresaId)

        <div class="form-group">
            @Html.LabelFor(model => model.UsuarioId, "UsuarioId", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownList("UsuarioId", null, htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.UsuarioId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpresaId, "EmpresaId", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownList("EmpresaId", null, htmlAttributes: new { @class = "form-control" })
                @Html.ValidationMessageFor(model => model.EmpresaId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Index.cshtml

@model IEnumerable<SeuProjeto.Models.UsuarioEmpresa>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Empresa.Nome)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Usuario.Nome)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Empresa.Nome)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Usuario.Nome)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.UsuarioEmpresaId }) |
            @Html.ActionLink("Details", "Details", new { id=item.UsuarioEmpresaId }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.UsuarioEmpresaId })
        </td>
    </tr>
}

</table>

Having this, just modify Controller and Views in order to achieve the desired behaviour.