Swap items in a list using EF6 and c# with primary keys

Question

I want to swap items to re order. Reorder index is primary key so I can not edit it. So what I am doing:

1. I am searching items with name and cloned it to another object.
2. I am also searching previous item on that with key found on case 1. and cloned it to an object.
3. I deleted both the search items from DB.
4. Insert New replaced item with previous key and change previous cloned item key to a random no. add to DB.
5. During search when this preivous item through name in loop it key change from clone and add to db.

Its working when First Compile. But when I reorder again in list then it throws error in Conflicting Primary keys in both case Attach() and Remove().

Code is as follows

foreach (var item in items)
{
    var task = db.ProjectTasks.Where(wh => wh.ProjectID == item.projectID && wh.TaskDesc==item.taksName).FirstOrDefault();
    var oldTask = db.ProjectTasks.Where(wh => wh.ProjectID == item.projectID && wh.TaskID == item.taskID).FirstOrDefault();
    if (oldTask != null)
    {
        //db.ProjectTasks.Attach(oldTask);
        db.ProjectTasks.Remove(oldTask);
        //db.SaveChanges();
    }


  //  var cloneTask = task;   //Cloned task to inser same ater delete


    var taskClone1 = new ProjectTask { 
        ActualManHrs=task.ActualManHrs,
        AFDate=task.AFDate,
        ASDate=task.ASDate,
        ConDate=task.ConDate,
        DaysComplete=task.DaysComplete,
        DaysRemaining=task.DaysRemaining,
        Duration=task.Duration,
        DurationActual=task.DurationActual,
        EFDate=task.EFDate,
        ESDate=task.ESDate,
        orderIndex=task.orderIndex,
        PercentComplete=task.PercentComplete,
        PlannedManHrs=task.PlannedManHrs,
        PriorTask=task.PriorTask,
        ProjectID=task.ProjectID,
        ProjectMaster=task.ProjectMaster,
        Remarks=task.Remarks,
        ResourceCenter=task.ResourceCenter,
        TaskDesc=task.TaskDesc,
        TaskGroup=task.TaskGroup,
        TaskID=item.taskID,
        TaskStatus=task.TaskStatus,
        WorkingDays=task.WorkingDays

    };

    var swap1 = new List<ProjectTask>();
    swap1.Add(taskClone1);

    ProjectTask oldTaskDetails = null;
    if (oldTask != null)
    {
        oldTaskDetails = new ProjectTask
        {
            ActualManHrs = oldTask.ActualManHrs,
            AFDate = oldTask.AFDate,
            ASDate = oldTask.ASDate,
            ConDate = oldTask.ConDate,
            DaysComplete = oldTask.DaysComplete,
            DaysRemaining = oldTask.DaysRemaining,
            Duration = oldTask.Duration,
            DurationActual = oldTask.DurationActual,
            EFDate = oldTask.EFDate,
            ESDate = oldTask.ESDate,
            orderIndex = oldTask.orderIndex,
            PercentComplete = oldTask.PercentComplete,
            PlannedManHrs = oldTask.PlannedManHrs,
            PriorTask = oldTask.PriorTask,
            ProjectID = oldTask.ProjectID,
            ProjectMaster = oldTask.ProjectMaster,
            Remarks = oldTask.Remarks,
            ResourceCenter = oldTask.ResourceCenter,
            TaskDesc = oldTask.TaskDesc,
            TaskGroup = oldTask.TaskGroup,
            TaskID = oldTask.TaskID,
            TaskStatus = oldTask.TaskStatus,
            WorkingDays = oldTask.WorkingDays
        };
    }

    var swap2 = new List<ProjectTask>();
    swap2.Add(oldTaskDetails);

    if(task!=null) //Check Nll or not
    {
        db.ProjectTasks.Remove(task); //2nd time on ward Primary key voilation
        db.SaveChanges();

        db.ProjectTasks.AddRange(swap1); //2nd time on ward Primary key voilation 

        if (oldTaskDetails != null)
        {
            oldTaskDetails.TaskID = Guid.NewGuid().ToString().Substring(0, 4);                                
            db.ProjectTasks.AddRange(swap2);                                
        }
    }
    //db.SaveChanges();
}

Answer 1

The thing is that SQL server autoincrements the primary keys even though you are deleting records. Check this post, it will help you out in how to reset back those primary keys. That's why you are getting exceptions regarding primary key conflicts.

However, that is a short term solution and I would not recommend it. I would definitely redesign your solution. At the end, you just want a Display Order for the items that you are swapping.

I would create a new column in the table called DisplayOrder that would add values from 1 to n every time a record is added. And, then I would just call OrderBy(i => i.DisplayOrder). And, at the end, if you really need to swap values (change their order), I would create a new function called SwapValues(ProjectTaskA, projectTaskB) that would change their display order. Or, if you really want to make it even better, you could create something called SwapValues(ProjectTask, moveDown, numberOfSteps) with the object in question, the number of steps that would have to go until finding the object to be swapped with and a direction, up or down.

These are just a couple of ideas of what you could do. I hope they help you.

Answer 2

After 4 days of fighting with Primery Key voilation... I found simple solution

var projectID = items.FirstOrDefault().projectID;
                    var group=items.FirstOrDefault().sourceGroup;

                    //Loop through changable item
                    foreach(var item in items)
                    {
                    checkPrimeryKey: //return back after changin duplicate item taskID
                        var checkDuplicate = db.ProjectTasks.Where(wh => wh.ProjectID == projectID && wh.TaskID == item.taskID).FirstOrDefault();
                        if (checkDuplicate == null) //If not Duplicate found
                        {
                            string sql = "update ProjectTasks set taskID='" + item.taskID + "' where projectID=" + projectID + " and TaskDesc='" + item.taksName + "'";
                            db.Database.ExecuteSqlCommand(sql);
                        }
                        else //If duplicate found change its task ID and return back to Step:1
                        {
                            string sql = "update ProjectTasks set taskID='" + Guid.NewGuid().ToString().Substring(0, 4) + "' where projectID=" + projectID + " and TaskDesc='" + checkDuplicate.TaskDesc + "'";
                            db.Database.ExecuteSqlCommand(sql);
                            goto checkPrimeryKey;
                        }
                    }
                    db.SaveChanges();