I have a SQL table with a datetime field of Version_Date as a primary key, I can see its data saved as this format: "2018-09-26 00:00:00 000",
Now, from the razor view with a update when I change this field from razor view and save it, here is the code in controller:
[HttpPost]
[ValidateAntiForgeryToken]
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "Version_Date,xxxx")] tbl_Version_Information tbl_Version)
{
if (ModelState.IsValid)
{
// Date(tbl_Version.Version_Date) = xxx //I need to do some changes here_
db.Entry(tbl_Version).State = EntityState.Modified;
db.SaveChanges();
}}
Now, when I changed the Version_Date field from razor view and save it, I got this error:
System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded
After some searches, I found the error caused by the Version_Date field since it is a Primary key in SQL table with millisecond portion of data, how
to make this update working?
This is not common since the primary key value could not be updated by db.Entry(tbl_Version).State = EntityState.Modified; logically which you can refer to Setting
EntityState: Any related objects will not be tracked. Since the ChangeTracker is unaware of which properties were modified, the context will issue an SQL statement updating all property values (apart from the primary key value).
If you force it to be updated, you might met the error :
The property 'Version_Date' is part of the object's key information and cannot be modified.
Can you provide more information such as your Index and Edit view, all Edit related action code, tbl_Version_Information class and anything related if it's possible.
// GET: Items/Edit/5
public ActionResult Edit(DateTime id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Items items = db.Items.Find(id);
if (items == null)
{
return HttpNotFound();
}
return View(items);
}
// POST: Items/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see https://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "Version_Date,IName")] Items items)
{
if (ModelState.IsValid)
{
db.Entry(items).State = EntityState.Modified;
//db.Entry(items).Property(x => x.Version_Date).IsModified = false;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(items);
}
Anyway, if you just want to get rid of the millisecond part, please check the Date Time. To String Method where you can find all datetime format you
want.
In addition, updating the primary key is not recommended in EF, delete and create a new record should be a better practice. Check
this.
This is not common since the primary key value could not be updated by db.Entry(tbl_Version).State = EntityState.Modified; logically which you can refer to Setting
EntityState: Any related objects will not be tracked. Since the ChangeTracker is unaware of which properties were modified, the context will issue an SQL statement updating all property values (apart from the primary key value).
If you force it to be updated, you might met the error :
The property 'Version_Date' is part of the object's key information and cannot be modified.
Can you provide more information such as your Index and Edit view, all Edit related action code, tbl_Version_Information class and anything related if it's possible.
// GET: Items/Edit/5publicActionResultEdit(DateTime id){if(id ==null){returnnewHttpStatusCodeResult(HttpStatusCode.BadRequest);}Items items = db.Items.Find(id);if(items ==null){returnHttpNotFound();}returnView(items);}// POST: Items/Edit/5// To protect from overposting attacks, please enable the specific properties you want to bind to, for // more details see https://go.microsoft.com/fwlink/?LinkId=317598.[HttpPost][ValidateAntiForgeryToken]publicActionResultEdit([Bind(Include="Version_Date,IName")]Items items){if(ModelState.IsValid){
db.Entry(items).State=EntityState.Modified;//db.Entry(items).Property(x => x.Version_Date).IsModified = false;
db.SaveChanges();returnRedirectToAction("Index");}returnView(items);}
Anyway, if you just want to get rid of the millisecond part, please check the Date Time. To String Method where you can find all datetime format you
want.
In addition, updating the primary key is not recommended in EF, delete and create a new record should be a better practice. Check
this.
Best Regard,
Yang Shen
Hi Yang Shen,
Thank you so much for your help, yes, I agree with you, we should not update primary key, I understand it is not a good way to do that, But this is a legacy project with VB codes to be converted to asp.net MVC C# codes, the existing back end SQL tables of
the legacy system remains same, we just create front end UI and C# codes, therefore, this SQL table remains same even it was not designed properly (Date_Time as Primary key).
But your "delete and create a new record should be a better practice" solution may be used since this table only contains one record which is as the same as the legacy system is working now.
But I am using another method to change it a bit without needing modify the original SQL table, but this way allows user to create new record which is Not as same as the legacy system doing, but I think it is a better since I am thinking user may need to
refer the info of old version info documents. this is my codes: basically, when user updates every record, it will check if this is a new record or not based on a sorted query with Version_date as key, if it is a new record, that means the Version_Date is
changed, so I will add a new record by using EntityState.Modified, otherwise, it means the version_Date (primary key) is Not changed, so I will update original record by using EntityState.Added.
string strSQL = "SELECT * FROM tbl_Version_Information WHERE Version_Date = '" + tbl_Version.Version_Date + "'" + " order by Version_Date";
var result = db.Database.SqlQuery<tbl_Version_Information>(strSQL).ToList();
if (ModelState.IsValid)
{
if(result.Count > 0) //Version_Date remains same
{
db.Entry(tbl_Version).State = EntityState.Modified;
} else
{
db.Entry(tbl_Version).State = EntityState.Added;
}
db.SaveChanges();
But this method adds new record and keep the original record which is not as same as the original design, if client complains about this, then I will change it to use your idea to delete the original record after the new record is added.
Member
356 Points
2189 Posts
How to make this DateTime as primary key issue work when saving in mvc?
Mar 04, 2020 01:48 PM|Peter Cong|LINK
I have a SQL table with a datetime field of Version_Date as a primary key, I can see its data saved as this format: "2018-09-26 00:00:00 000",
Now, from the razor view with a update when I change this field from razor view and save it, here is the code in controller:
[HttpPost]
[ValidateAntiForgeryToken]
[HttpPost] [ValidateAntiForgeryToken] public ActionResult Edit([Bind(Include = "Version_Date,xxxx")] tbl_Version_Information tbl_Version) { if (ModelState.IsValid) { // Date(tbl_Version.Version_Date) = xxx //I need to do some changes here_ db.Entry(tbl_Version).State = EntityState.Modified; db.SaveChanges(); }}Now, when I changed the Version_Date field from razor view and save it, I got this error:
System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded
After some searches, I found the error caused by the Version_Date field since it is a Primary key in SQL table with millisecond portion of data, how to make this update working?
Thanks a lot,
Contributor
2920 Points
938 Posts
Re: How to make this DateTime as primary key issue work when saving in mvc?
Mar 05, 2020 03:22 AM|Yang Shen|LINK
Hi Peter Cong,
This is not common since the primary key value could not be updated by
db.Entry(tbl_Version).State = EntityState.Modified;logically which you can refer to Setting EntityState: Any related objects will not be tracked. Since the ChangeTracker is unaware of which properties were modified, the context will issue an SQL statement updating all property values (apart from the primary key value).If you force it to be updated, you might met the error :
The property 'Version_Date' is part of the object's key information and cannot be modified.
Can you provide more information such as your Index and Edit view, all Edit related action code, tbl_Version_Information class and anything related if it's possible.
Below is the demo i created based on the tutorial: Getting Started with Entity Framework 6 Database First Using MVC 5.
SQL structure and data:
Items class:
public partial class Items { public System.DateTime Version_Date { get; set; } public string IName { get; set; } }Index.cshtml: ( I updated the highlighted part so that the hidden primary key can be visible and the url can be recognized)
@model IEnumerable<MVCDemo01.Models.Items> @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <p> @Html.ActionLink("Create New", "Create") </p> <table class="table"> <tr> <th> @Html.DisplayNameFor(model => model.IName) </th> <th> @Html.DisplayNameFor(model => model.Version_Date) </th> <th></th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.IName) </td> <td> @Html.DisplayFor(modelItem => item.Version_Date) </td> <td> @Html.ActionLink("Edit", "Edit", new { id=item.Version_Date.ToString("u").Remove(item.Version_Date.ToString("u").Length-1,1) }) | @Html.ActionLink("Details", "Details", new { id=item.Version_Date }) | @Html.ActionLink("Delete", "Delete", new { id=item.Version_Date }) </td> </tr> } </table> </body> </html>Edit.cshtml:
@model MVCDemo01.Models.Items @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Edit</title> </head> <body> @using (Html.BeginForm()) { @Html.AntiForgeryToken() <div class="form-horizontal"> <h4>Items</h4> <hr /> @Html.ValidationSummary(true, "", new { @class = "text-danger" }) @Html.HiddenFor(model => model.Version_Date) <div class="form-group"> @Html.LabelFor(model => model.IName, htmlAttributes: new { @class = "control-label col-md-2" }) @Html.LabelFor(model => model.Version_Date, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.IName, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.IName, "", new { @class = "text-danger" }) @Html.EditorFor(model => model.Version_Date, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.Version_Date, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Save" class="btn btn-default" /> </div> </div> </div> } <div> @Html.ActionLink("Back to List", "Index") </div> </body> </html>Controller:
// GET: Items/Edit/5 public ActionResult Edit(DateTime id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } Items items = db.Items.Find(id); if (items == null) { return HttpNotFound(); } return View(items); } // POST: Items/Edit/5 // To protect from overposting attacks, please enable the specific properties you want to bind to, for // more details see https://go.microsoft.com/fwlink/?LinkId=317598. [HttpPost] [ValidateAntiForgeryToken] public ActionResult Edit([Bind(Include = "Version_Date,IName")] Items items) { if (ModelState.IsValid) { db.Entry(items).State = EntityState.Modified; //db.Entry(items).Property(x => x.Version_Date).IsModified = false; db.SaveChanges(); return RedirectToAction("Index"); } return View(items); }Anyway, if you just want to get rid of the millisecond part, please check the Date Time. To String Method where you can find all datetime format you want.
In addition, updating the primary key is not recommended in EF, delete and create a new record should be a better practice. Check this.
Best Regard,
Yang Shen
Member
356 Points
2189 Posts
Re: How to make this DateTime as primary key issue work when saving in mvc?
Mar 05, 2020 12:39 PM|Peter Cong|LINK
Hi Yang Shen,
Thank you so much for your help, yes, I agree with you, we should not update primary key, I understand it is not a good way to do that, But this is a legacy project with VB codes to be converted to asp.net MVC C# codes, the existing back end SQL tables of the legacy system remains same, we just create front end UI and C# codes, therefore, this SQL table remains same even it was not designed properly (Date_Time as Primary key).
But your "delete and create a new record should be a better practice" solution may be used since this table only contains one record which is as the same as the legacy system is working now.
But I am using another method to change it a bit without needing modify the original SQL table, but this way allows user to create new record which is Not as same as the legacy system doing, but I think it is a better since I am thinking user may need to refer the info of old version info documents. this is my codes: basically, when user updates every record, it will check if this is a new record or not based on a sorted query with Version_date as key, if it is a new record, that means the Version_Date is changed, so I will add a new record by using EntityState.Modified, otherwise, it means the version_Date (primary key) is Not changed, so I will update original record by using EntityState.Added.
string strSQL = "SELECT * FROM tbl_Version_Information WHERE Version_Date = '" + tbl_Version.Version_Date + "'" + " order by Version_Date"; var result = db.Database.SqlQuery<tbl_Version_Information>(strSQL).ToList(); if (ModelState.IsValid) { if(result.Count > 0) //Version_Date remains same { db.Entry(tbl_Version).State = EntityState.Modified; } else { db.Entry(tbl_Version).State = EntityState.Added; } db.SaveChanges();But this method adds new record and keep the original record which is not as same as the original design, if client complains about this, then I will change it to use your idea to delete the original record after the new record is added.
Thanks a lot again,