C# asp.net EF MVC postgresql error 23505: Duplicate key violates unique constraint
- by user2721755
EDIT: It was issue with database table - dropping and recreating table id column did the work. Problem solved.
I'm trying to build web application, that is connected to postgresql database. Results are displaying in view with Kendo UI. When I'm trying to add new row (with Kendo UI 'Add new record' button), I get error 23505: 'Duplicate key violates unique constraint'. My guess is, that EF takes id to insert from the beginning, not the last one, because after 35 (it's number of rows in table) tries - and errors - adding works perfectly.
Can someone help me to understand, what's wrong?
Model:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace MainConfigTest.Models
{
[Table("mainconfig", Schema = "public")]
public class Mainconfig
{
[Column("id")]
[Key]
[Editable(false)]
public int Id { get; set; }
[Column("descr")]
[Editable(true)]
public string Descr { get; set; }
[Column("hibversion")]
[Required]
[Editable(true)]
public long Hibversion { get; set; }
[Column("mckey")]
[Required]
[Editable(true)]
public string Mckey { get; set; }
[Column("valuexml")]
[Editable(true)]
public string Valuexml { get; set; }
[Column("mcvalue")]
[Editable(true)]
public string Mcvalue { get; set; }
}
}
Context:
using System.Data.Entity;
namespace MainConfigTest.Models
{
public class MainConfigContext : DbContext
{
public DbSet<Mainconfig> Mainconfig { get; set; }
}
}
Controller:
namespace MainConfigTest.Controllers
{
public class MainConfigController : Controller
{
#region Properties
private Models.MainConfigContext db = new Models.MainConfigContext();
private string mainTitle = "Mainconfig (Kendo UI)";
#endregion
#region Initialization
public MainConfigController()
{
ViewBag.MainTitle = mainTitle;
}
#endregion
#region Ajax
[HttpGet]
public JsonResult GetMainconfig()
{
int take = HttpContext.Request["take"] == null ? 5 : Convert.ToInt32(HttpContext.Request["take"]);
int skip = HttpContext.Request["skip"] == null ? 0 : Convert.ToInt32(HttpContext.Request["skip"]);
Array data = (from Models.Mainconfig c in db.Mainconfig
select c).OrderBy(c => c.Id).ToArray().Skip(skip).Take(take).ToArray();
return Json(new Models.MainconfigResponse(data, db.Mainconfig.Count()), JsonRequestBehavior.AllowGet);
}
[HttpPost]
public JsonResult Create()
{
try
{
Mainconfig itemToAdd = new Mainconfig()
{
Descr = Convert.ToString(HttpContext.Request["Descr"]),
Hibversion = Convert.ToInt64(HttpContext.Request["Hibversion"]),
Mckey = Convert.ToString(HttpContext.Request["Mckey"]),
Valuexml = Convert.ToString(HttpContext.Request["Valuexml"]),
Mcvalue = Convert.ToString(HttpContext.Request["Mcvalue"])
};
db.Mainconfig.Add(itemToAdd);
db.SaveChanges();
return Json(new { Success = true });
}
catch (InvalidOperationException ex)
{
return Json(new { Success = false, msg = ex });
}
}
//other methods
}
}
Kendo UI script in view:
<script type="text/javascript">
$(document).ready(function () {
$("#config-grid").kendoGrid({
sortable: true,
pageable: true,
scrollable: false,
toolbar: ["create"],
editable: {
mode: "popup"
},
dataSource: {
pageSize: 5,
serverPaging: true,
transport: {
read: {
url: '@Url.Action("GetMainconfig")',
dataType: "json"
},
update: {
url: '@Url.Action("Update")',
type: "Post",
dataType: "json",
complete: function (e) {
$("#config-grid").data("kendoGrid").dataSource.read();
}
},
destroy: {
url: '@Url.Action("Delete")',
type: "Post",
dataType: "json"
},
create: {
url: '@Url.Action("Create")',
type: "Post",
dataType: "json",
complete: function (e) {
$("#config-grid").data("kendoGrid").dataSource.read();
}
},
},
error: function (e) {
if(e.Success == false) {
this.cancelChanges();
}
},
schema: {
data: "Data",
total: "Total",
model: {
id: "Id",
fields: {
Id: { editable: false, nullable: true },
Descr: { type: "string"},
Hibversion: { type: "number", validation: {required: true,}, },
Mckey: { type: "string", validation: { required: true, }, },
Valuexml:{ type: "string"},
Mcvalue: { type: "string" }
}
}
}
}, //end DataSource
// generate columns etc.
Mainconfig table structure:
id serial NOT NULL,
descr character varying(200),
hibversion bigint NOT NULL,
mckey character varying(100) NOT NULL,
valuexml character varying(8000),
mcvalue character varying(200),
CONSTRAINT mainconfig_pkey PRIMARY KEY (id),
CONSTRAINT mainconfig_mckey_key UNIQUE (mckey)
Any help will be appreciated.
