locked
Update problem in Master Detail CRUD Operations using EF and ASP.net MVC 3 RRS feed

  • Question

  • Problem : On update it always delete the records in database & insert new record.

    Solution Required : Without delete, update the existing record & insert new one added by user in child table while updateing the details.

    Details of Sample project is as below:

    Master Table : SalesMains

    SalesId int  (PK  Identity Increment )
    ReferenceNo varchar
    SalesDate datetime 
    SalesPerson varchar

    Child Table : SalesSubs

    SalesId int FK , PK
    SalesSubsID int (PK Identity Increment (1))
    ItemName varchar
    Qty int 
    UnitPrice decimal(18, 0) 

    Models :

     public class SalesMain
        {       
            [Key]
            public int SalesId { get; set; }
            [Required]
            public string ReferenceNo { get; set; }
            [Required]
            public DateTime SalesDate { get; set; }
            public string SalesPerson { get; set; }

            public virtual ICollection<SalesSub> SalesSubs { get; set; }
        }

    public class SalesSub
        {       
            [Key, Column(Order = 0)]
            public int SalesId { get; set; }

            [Key, Column(Order = 1)]
            [DatabaseGenerated(DatabaseGenerationOption.Identity)]
            public int SalesSubsID { get; set; }
           
            public string ItemName { get; set; }

            public int Qty { get; set; }
            public decimal UnitPrice { get; set; }     

            public virtual SalesMain SalesMain { get; set; }
        }

    SalesController Code:

     public ActionResult Edit(int id)
            {
                ViewBag.Title = "Edit";
                SalesMain salesmain = db.SalesMains.Find(id);

                //Call Create View
                return View("Create", salesmain);
            }

    [HttpPost]
            public JsonResult Create(SalesMain salesmain)
            {
                try
                {
                    if (ModelState.IsValid)
                    {

                        // If sales main has SalesID then we can understand we have existing sales Information
                        // So we need to Perform Update Operation

                        // Perform Update
                        if (salesmain.SalesId > 0)
                        {

                            var CurrentsalesSUb = db.SalesSubs.Where(p => p.SalesId == salesmain.SalesId);

                            foreach (SalesSub ss in CurrentsalesSUb)
                                db.SalesSubs.Remove(ss);

                            foreach (SalesSub ss in salesmain.SalesSubs)                      
                                db.SalesSubs.Add(ss);
                         
                            db.Entry(salesmain).State = EntityState.Modified;                       

                        }
                        //Perform Save
                        else
                        {                       
                            db.SalesMains.Add(salesmain);

                        }

                        db.SaveChanges();

                        // If Sucess== 1 then Save/Update Successfull else there it has Exception
                        return Json(new { Success = 1, SalesID = salesmain.SalesId, ex = "" });
                    }

                }
                catch (Exception ex)
                {
                    // If Sucess== 0 then Unable to perform Save/Update Operation and send Exception to View as JSON
                    return Json(new { Success = 0, ex = ex.Message.ToString() });
                }

                return Json(new { Success = 0, ex = new Exception("Unable to save").Message.ToString() });
            }

    Create View :

                           

    // Creating SalesSub Json Object
             var salessub = { "SalesId": "", "ItemName": "", "Qty": "", "UnitPrice": "", "SalesSubsID": "" };


      // Creating SalesMain Json Object
            var salesmain = { "SalesId":"","ReferenceNo": "", "SalesDate": "", "SalesPerson": "", "SalesSubs":[] };

      salesmain.SalesSubs.push(salessub);

     // Here i have used ajax post for saving/updating information
            $.ajax({
                url: '/Sales/Create',
                data: JSON.stringify(salesmain),
                type: 'POST',
                contentType: 'application/json;',
                dataType: 'json',
                success: function (result) {

                    if (result.Success == "1") {
                        window.location.href = "/Sales/index";
                    }


    Monday, August 13, 2012 7:49 AM

Answers

  • @SilentJealousy : Yes, the records in problem are the SalesSub records, these are always deleted & inserted new one

    @Glenn Condron : Your logic is good enough to solve the problem.

    Finally, I have done with this problem & hope it will help to all of them who ever are struggling with this. The solution is below:


     // Perform Update
    List<int> previousSalesIds = db.SalesSubs
                    .Where(ep => ep.SalesId == salesmain.SalesId)
                    .Select(ep => ep.SalesSubsID)
                    .ToList();

                            List<int> currentSalesIds = salesmain.SalesSubs
                                .Select(o => o.SalesSubsID)
                                .ToList();

                            List<int> deletedSalesIds = previousSalesIds
                                .Except(currentSalesIds).ToList();

                            foreach (var deletedSalesId in deletedSalesIds)
                            {
                                SalesSub deletedOrderDetail = db.SalesSubs
                                    .Where(od => od.SalesId == salesmain.SalesId && od.SalesSubsID == deletedSalesId)
                                    .Single();

                                db.Entry(deletedOrderDetail).State = EntityState.Deleted;
                            }

                            foreach (var orderDetail in salesmain.SalesSubs)
                            {
                                if (orderDetail.SalesSubsID == 0)
                                {
                                    db.Entry(orderDetail).State = EntityState.Added;
                                    orderDetail.SalesId = salesmain.SalesId;
                                }
                                else
                                {
                                    db.Entry(orderDetail).State = EntityState.Modified;
                                }
                            }
                            db.Entry(salesmain).State = EntityState.Modified;

      
    Tuesday, August 14, 2012 4:49 PM

All replies

  • Problem : On update it always delete the records in database & insert new record.

    Solution Required : Without delete, update the existing record & insert new one added by user in child table while updateing the details.

    Hi,

    I am not sure if the records in problem are the SalesSub records. Or it always delete the SalesMain and SalesSub recordsin database?

    Tuesday, August 14, 2012 7:36 AM
  • Hi

    I think you are pretty close with what you have at the moment. In the bold section of your code I think you need to try something like the following:

    db.SalesMain.Add(salesmain);
    
    db.Entry(salesmain).State = unchanged; //or modified if salesmain has changed
    
    foreach(var ss in db.SalesSubs.Where(p => p.SalesId == salesmain.SalesId)
    {
    
          if(ss.SubSalesId > 0)
    
          {
    
                //set state of ss to modified.
    
          }
    
    }
    Adding salesmain should also add all its children to the context, after that it is just a matter of setting the correct modified states.

    This assumes that all the new sub sales objects have an id of 0.

    Does that help or have I missed something?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.



    Tuesday, August 14, 2012 3:22 PM
  • @SilentJealousy : Yes, the records in problem are the SalesSub records, these are always deleted & inserted new one

    @Glenn Condron : Your logic is good enough to solve the problem.

    Finally, I have done with this problem & hope it will help to all of them who ever are struggling with this. The solution is below:


     // Perform Update
    List<int> previousSalesIds = db.SalesSubs
                    .Where(ep => ep.SalesId == salesmain.SalesId)
                    .Select(ep => ep.SalesSubsID)
                    .ToList();

                            List<int> currentSalesIds = salesmain.SalesSubs
                                .Select(o => o.SalesSubsID)
                                .ToList();

                            List<int> deletedSalesIds = previousSalesIds
                                .Except(currentSalesIds).ToList();

                            foreach (var deletedSalesId in deletedSalesIds)
                            {
                                SalesSub deletedOrderDetail = db.SalesSubs
                                    .Where(od => od.SalesId == salesmain.SalesId && od.SalesSubsID == deletedSalesId)
                                    .Single();

                                db.Entry(deletedOrderDetail).State = EntityState.Deleted;
                            }

                            foreach (var orderDetail in salesmain.SalesSubs)
                            {
                                if (orderDetail.SalesSubsID == 0)
                                {
                                    db.Entry(orderDetail).State = EntityState.Added;
                                    orderDetail.SalesId = salesmain.SalesId;
                                }
                                else
                                {
                                    db.Entry(orderDetail).State = EntityState.Modified;
                                }
                            }
                            db.Entry(salesmain).State = EntityState.Modified;

      
    Tuesday, August 14, 2012 4:49 PM
  • Thank you!  I spent too much time trying to get all the updates in just the right order.
    Tuesday, March 19, 2013 12:19 AM