Update Record based on WHERE clause in Entity Framework 6

Question

_{Friday, November 13, 2015 5:28 AM}

I want to update a record in MVC 5 and EF6 based on a where clause. Basically this query:

UPDATE [dbo].[users_projects]
   SET [Username] = 'myname'
 WHERE [ProjectID] = 8 AND [Role] = 'Role1'
GO

At the moment I've got this in my controller:

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "ID,Name,User1,User2,Description,DateCreated,Locked,Archived")] projects projects)
        {
            if (ModelState.IsValid)
            {
                // Modify 'projects' using submitted data
                db.Entry(projects).State = EntityState.Modified;

                // now update 'users_projects' table with User1 and User2
                var users_projects = new users_projects { Username = projects.User1 };
                db.Entry(users_projects).State = EntityState.Modified;
                users_projects = new users_projects { Username = projects.User2 };
                db.Entry(users_projects).State = EntityState.Modified;

                // Save changes
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(projects);
        }

So I want to update the users_projects table based on the value of two other columns (Role and ProjectID) and update only the username field. Any ideas?

Thanks in advance
Adam

All replies (2)

_{Sunday, November 15, 2015 9:50 PM ✅Answered}

Hi Adam,

So I want to update the users_projects table based on the value of two other columns (Role and ProjectID) and update only the username field. Any ideas?

As for this issue, when update the records using EF, first we need to use Find method or where clause to find the relevant records, then change the value, finally calling SaveChange method to update the database.

You could refer to the following code:

[HttpPost, ActionName("Edit")]
[ValidateAntiForgeryToken]
public ActionResult EditPost(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    var studentToUpdate = db.Students.Find(id);
    if (TryUpdateModel(studentToUpdate, "",
       new string[] { "LastName", "FirstMidName", "EnrollmentDate" }))
    {
        try
        {
            db.SaveChanges();

            return RedirectToAction("Index");
        }
        catch (DataException /* dex */)
        {
            //Log the error (uncomment dex variable name and add a line here to write a log.
            ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists, see your system administrator.");
        }
    }
    return View(studentToUpdate);
}

More details, please see:

http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/implementing-basic-crud-functionality-with-the-entity-framework-in-asp-net-mvc-application

http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/updating-related-data-with-the-entity-framework-in-an-asp-net-mvc-application

Best regards,
Dillion

---

_{Friday, November 13, 2015 11:12 AM}

You might consider using a stored procedure within the database.  You can use the Fluent API to associate the SP with your update process.

Try this, though it's not verified, so you may need to tweak it:

modelBuilder.Entity<Project>.MapToStoredProcedures(s =>
{
   s.Update(u => u.HasName("update_projects").RowsAffectedParameter("Role"))
}