Many to many relationship in MVC - write to a table with foreign keys

Question

In model I have 3 tables in relation to many many.

public class Order
{
    [Key] 
    public int IdOrder { get; set; }
    public string UserId { get; set; }
    public virtual User User { get; set; }
    public int IdOrderAttachment { get; set; }
    public virtual OrderAttachment OrderAttachment { get; set; }
    public virtual ICollection<Employee> Employee { get; set; }

    [Required(ErrorMessage = "Specify the date of order acceptance")]
    [Display(Name = "Date of acceptance of the order")]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTimeDateOfAcceptance  { get; set; }

    [Display(Name = "Date of completion planning")]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTime? DateOfCompletionPlanning { get; set; }

    [Display(Name = "End Date")]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTime? EndDate { get; set; }

    [Required(ErrorMessage = "Enter the subject")]
    [MaxLength(200, ErrorMessage = "Name max 200 characters")]
    [Display(Name = "Subject")]
    public string Subject { get; set; }

    public virtual ICollection<OrderPosition> OrderPosition{ get; set; }
}

public class OrderPosition
{
    [Key]
    public int IdOrderPosition { get; set; }
    public int IdOrder { get; set; }
    public int IdPosition { get; set; }
    public virtual Order Order { get; set; }
    public virtual Position Position { get; set; }
}

public class Position
{
    [Key]
    public int IdPosition { get; set; }
    [Column(TypeName = "nvarchar(MAX)")]
    [Display(Name = "Description")]
    [UIHint("tinymce_jquery_full"), AllowHtml]
    public string Description { get; set; }
    public virtual ICollection<OrderPosition> OrderPosition { get; set; }
}


[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreateOrder(HttpPostedFileBase file, DataOrderUserViewModel viewModel)
{
    var userId = User.Identity.GetUserId();         

    if (ModelState.IsValid)
    {
        if (file != null && file.ContentLength > 0)
        {
            string path = "/Content/Layout/CompanyFile";
            if (!Directory.Exists(HttpContext.Server.MapPath(path)))
            {
                Directory.CreateDirectory(HttpContext.Server.MapPath(path));
            }
            string filename = Path.GetFileName(file.FileName);

            file.SaveAs(Path.Combine(HttpContext.Server.MapPath(path), filename));
            viewModel.NameFile = path+ "/" + filename;

            //var nameFile = Path.GetFileName(file.FileName);

            //var path = Path.Combine(Server.MapPath("/Content/Layout/CompanyFile"), nameFile);

            //file.SaveAs(path);

        }

        var order = new Order()
        {
            DateTimeDateOfAcceptance  = viewModel.DateTimeDateOfAcceptance,
            Subject= viewModel.Subject,                 
            UserId = userId

        };

        var position  = new Position ()
        {
            Description = viewModel.Description 
        };

        var orderAttachment = new OrderAttachment ()
        {
            NameFile= viewModel.NameFile,
            Description = viewModel.Description2 
        };

        db.Order.Add(order);
        db.Position.Add(position);
        db.OrderAttachment.Add(orderAttachment);
        db.SaveChanges();

    }
    return RedirectToAction("Index", "Administration");
}

My question is how to write data to the OrderPosition table. I understand that I should first write data in the Order and Position table. Then, having two keys from these arrays, I should read them and send them to the OrderPosition as external keys where they will be saved. What should a record of these instructions look like?

presumably it will look like a series of INSERT statements. This appears to be unrelated to MVC or ASP.NET specifically, it's lower down the stack. Are we really talking about Entity Framework? If so then please tag the question appropriately. (Yes it's an MVC app but it's not MVC giving you the problem). Anyway, in that case case a series of `Add` and then `SaveChanges` statements. You can add OrderPosition objects to the Order or Position objects in a list, and save the main object, that should achieve it. Look up some EF examples of many-many, there are plenty already online. — ADyson, Sep 05 '17 at 09:13
First result on google: https://stackoverflow.com/questions/4253165/insert-update-many-to-many-entity-framework-how-do-i-do-it . Normally EF does not generate a 3rd entity OrderPosition like you've got. Did you use code-first (you wrote the classes and EF created the DB) or database-first (you wrote the database and EF wrote the classes)? — ADyson, Sep 05 '17 at 09:44
My cod first is modeled on the SQL database so there are 3 tables. ( I'm working with programmers who mostly write in Sql server). I'm using Code First then I update the database using migration. — Przemysław Szkaradek, Sep 05 '17 at 10:10
You can simply create two classes, both of which can include a list of the other, and I think EF will do the rest and generate a many-many relationship. If not, then simply you need 3 Add methods in your code, one for each table. The third one will need to fetch the IDs generated by the first two. — ADyson, Sep 05 '17 at 10:14

score 0 · Answer 1 · answered Sep 05 '17 at 12:30

0

Do I need to define the following relationships in my case?

modelBuilder.Entity<OrderPosition>()
       .HasKey(c => new { c.IdOrder , c.IdPosition });

   modelBuilder.Entity<Order>()
       .HasMany(c => c.Subject )
       .WithRequired()
       .HasForeignKey(c => c.IdOrder );

   modelBuilder.Entity<Position>()
       .HasMany(c => c.Description )
       .WithRequired()
       .HasForeignKey(c => c.IdPosition );

answered Sep 05 '17 at 12:30

Przemysław Szkaradek

33
1
10

Please move this to your question. It's not an answer. – Gert Arnold Sep 05 '17 at 14:54

Many to many relationship in MVC - write to a table with foreign keys

1 Answers1