Trouble with Code First DatabaseGenerated Composite Primary Key
- by Nick Fleetwood
This is a tad complicated, and please, I know all the arguments against natural PK's, so we don't need to have that discussion.
using VS2012/MVC4/C#/CodeFirst
So, the PK is based on the date and a corresponding digit together. So, a few rows created today would be like this:
20131019 1
20131019 2
And one created tomorrow:
20131020 1
This has to be automatically generated using C# or as a trigger or whatever. The user wouldn't input this. I did come up with a solution, but I'm having problems with it, and I'm a little stuck, hence the question.
So, I have a model:
public class MainOne
{
//[Key]
//public int ID { get; set; }
[Key][Column(Order=1)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string DocketDate { get; set; }
[Key][Column(Order=2)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string DocketNumber { get; set; }
[StringLength(3, ErrorMessage = "Corp Code must be three letters")]
public string CorpCode { get; set; }
[StringLength(4, ErrorMessage = "Corp Code must be four letters")]
public string DocketStatus { get; set; }
}
After I finish the model, I create a new controller and views using VS2012 scaffolding.
Then, what I'm doing is debugging to create the database, then adding the following instead of trigger after Code First creates the DB [I don't know if this is correct procedure]:
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[MainOnes]
instead OF INSERT AS
BEGIN
DECLARE @number INT
SELECT @number=COUNT(*) FROM [dbo].[MainOnes] WHERE [DocketDate] = CONVERT(DATE, GETDATE())
INSERT INTO [dbo].[MainOnes] (DocketDate,DocketNumber,CorpCode,DocketStatus) SELECT (CONVERT(DATE, GETDATE
())),(@number+1),inserted.CorpCode,inserted.DocketStatus FROM inserted
END
And when I try to create a record, this is the error I'm getting:
The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: The object state cannot be changed. This exception may result from one or more of the primary key properties being set to null. Non-Added objects cannot have null primary key values. See inner exception for details.
Now, what's interesting to me, is that after I stop debugging and I start again, everything is perfect. The trigger fired perfectly, so the composite PK is unique and perfect, and the data in other columns is intact.
My guess is that EF is confused by the fact that there is seemingly no value for the PK until AFTER an insert command is given. Also, appearing to back this theory, is that when I try to edit on of the rows, in debug, I get the following error:
The number of primary key values passed must match number of primary key values defined on the entity.
Same error occurs if I try to pull the 'Details' or 'Delete' function.
Any solution or ideas on how to pull this off? I'm pretty open to anything, even creating a hidden int PK. But it would seem redundant.
EDIT 21OCT13
[HttpPost]
public ActionResult Create(MainOne mainone)
{
if (ModelState.IsValid)
{
var countId = db.MainOnes.Count(d => d.DocketDate == mainone.DocketNumber); //assuming that the date field already has a value
mainone.DocketNumber = countId + 1; //Cannot implicitly convert type int to string
db.MainOnes.Add(mainone);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(mainone);
}
EDIT 21OCT2013 FINAL CODE SOLUTION
For anyone like me, who is constantly searching for clear and complete solutions.
if (ModelState.IsValid)
{
String udate = DateTime.UtcNow.ToString("yyyy-MM-dd");
mainone.DocketDate = udate;
var ddate = db.MainOnes.Count(d => d.DocketDate == mainone.DocketDate); //assuming that the date field already has a value
mainone.DocketNumber = ddate + 1;
db.MainOnes.Add(mainone);
db.SaveChanges();
return RedirectToAction("Index");
}
