title: Update/Edit many-to-many tables using asp core mvc and sql Stored Procedure
body: <p>I have asp core mvc project , That tracks orders , The user will create a new tracking and add many orders in it ,and the order may will added in many tracking, so the relationship between the orders and tracking is many-to-many , and each order has a driver , so the relationship between orders and driver is one-to-many , so I created models as below .</p>
  <p>1- Orders Model :</p>
  <pre><code>    [Key]
      public int OrderId { get; set; }
      public int? OrderNo { get; set; }
      [Column(&quot;DriverID&quot;)]
      public int? DriverId { get; set; }
  
      [ForeignKey(nameof(DriverId))]
      [InverseProperty(&quot;Orders&quot;)]
      public virtual Driver Driver { get; set; }
      [InverseProperty(nameof(TrackingOrder.Order))]
      public virtual ICollection&lt;TrackingOrder&gt; TrackingOrders { get; set; }
  </code></pre>
  <p>2- Driver Model :</p>
  <pre><code>    [Key]
      [Column(&quot;DriverID&quot;)]
      public int DriverId { get; set; }
      [StringLength(50)]
      public string DriverName { get; set; }
  
      [InverseProperty(nameof(Order.Driver))]
      public virtual ICollection&lt;Order&gt; Orders { get; set; }
  </code></pre>
  <p>3- Tracking Model :</p>
  <pre><code>    [Key]
      [Column(&quot;TrackingID&quot;)]
      public int TrackingId { get; set; }
      [Column(&quot;TDate&quot;, TypeName = &quot;date&quot;)]
      public DateTime? Tdate { get; set; }
      public int OrderID { get; set; }
      public int DiverID { get; set; }
  
      [InverseProperty(nameof(TrackingOrder.Tracking))]
      public virtual ICollection&lt;TrackingOrder&gt; TrackingOrders { get; set; }
      public object OrderNo { get; internal set; }
      public object DriverID { get; internal set; }
  </code></pre>
  <p>4- Tracking_Orders Model :</p>
  <pre><code>    [Key]
      [Column(&quot;TrackingID&quot;)]
      public int TrackingId { get; set; }
      [Key]
      [Column(&quot;OrderID&quot;)]
      public int OrderId { get; set; }
  
      [ForeignKey(nameof(OrderId))]
      [InverseProperty(&quot;TrackingOrders&quot;)]
      public virtual Order Order { get; set; }
      [ForeignKey(nameof(TrackingId))]
      [InverseProperty(&quot;TrackingOrders&quot;)]
      public virtual Tracking Tracking { get; set; }
  </code></pre>
  <p>Then I added a View Model (AddNewTracking) to create a new tracking  :</p>
  <pre><code>    [Key]
      public int id { get; set; }
      public int? TrackingID { get; set; }
      public int? OrderNo { get; set; }
      public DateTime Tdate { get; set; }
     
      [Column(&quot;DriverID&quot;)]
      public int? DriverId { get; set; }
  
     public Driver driver { get; set; }
  </code></pre>
  <p>Then I added controller to create new tracking using Stored Procedure and it is working correctly as below :</p>
  <pre><code>    [HttpPost]
      [ValidateAntiForgeryToken]
      public ActionResult Create([Bind(&quot;OrderNo,Tdate,DriverId&quot;)] AddNewTracking addNewTracking)
      {
          var orderNo = addNewTracking.OrderNo;
          var ch = (from x in _context.Orders
                    where x.OrderNo == orderNo
                    select x).ToList();
          if (ModelState.IsValid)
          {
             if(ch.Count&gt;0)
              {
                  ViewBag.Duplicate = &quot;Order No Already Exist&quot;;
              }
             else
              {
                  
                  
                  int NewTrack = _context.Database.ExecuteSqlInterpolated($&quot;NewTracking @OrderNo={addNewTracking.OrderNo},@DriverID={addNewTracking.DriverId},@TrackingDate={addNewTracking.Tdate}&quot;);
                  ViewBag.successMsg = &quot;Order Added&quot;;
                  ModelState.Clear();
              }
              ViewData[&quot;DriverId&quot;] = new SelectList(_context.Drivers, &quot;DriverId&quot;, &quot;DriverName&quot;);
  
              
              return View();
  </code></pre>
  <p>Here is my insert stored procedure Code :</p>
  <pre><code>ALTER PROCEDURE [dbo].[NewTracking]
  -- Add the parameters for the stored procedure here
  @OrderNo int,
  @DriverID int,
  @TrackingDate date
  
  AS
  BEGIN
  Declare @OrderID int
  Declare @TrackingID int
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  select @OrderID=OrderID from Orders where OrderNo=@OrderNo
  select @TrackingID=TrackingID from Tracking where TDate=@TrackingDate
  if (@OrderID is null)
  begin
  insert into Orders values (@OrderNo,@DriverID)
  select @OrderID=SCOPE_IDENTITY()
  End
  
  if (@TrackingID is null)
  begin
  insert into Tracking values (@TrackingDate)
  select @TrackingID=SCOPE_IDENTITY()
  end
  insert into Tracking_Order values (@TrackingID,@OrderID)
  END
  </code></pre>
  <p>Now, I want to write a stored procedure to edit the new tracking (edit orderNo or driver name )</p>
  <p>I created View model for editing named (update) with code below :</p>
  <pre><code>    [Key]
      public int Id { get; set; }
      public int OrderNo { get; set; }
      
      public DateTime Tdate { get; set; }
  
      [Column(&quot;DriverID&quot;)]
      public int? DriverId { get; set; }
  
      public Driver driver { get; set; }
  </code></pre>
  <p>So , how I'll write Sql stored procedure to Edit as per (update) view model and put it in the code below :</p>
  <pre><code>    // POST:/Edit/5
      NewTracking
      [HttpPost]
      [ValidateAntiForgeryToken]
      public ActionResult Edit(int id)
      {
  
      }
  </code></pre>
  <p>I hope that I describe my issue correctly .</p>
  <p>Thanks .</p>