Please can anyone help with this issue I'm having. I've exhausted the current suggestions on here.
We are rewriting an application in MVC EF6 Codefirst using the existing database structure (SQL 2005).
The SQL Script for the table in question:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStaff](
[staffID] [int] IDENTITY(1,1) NOT NULL,
[firstName] [nvarchar](50) NOT NULL,
[lastName] [nvarchar](50) NOT NULL,
[Alias] [nvarchar](50) NULL,
[addressID] [int] NULL,
[teamID] [int] NULL,
[managerID] [int] NULL,
[clientID] [int] NULL,
[jobTitle] [int] NULL,
[activeFlag] [bit] NULL,
[contractorID] [int] NULL,
[fullName] [nvarchar](101) NULL,
[securityTrainingDate] [datetime] NULL,
[CRBCheckDate] [datetime] NULL,
[CMSTrainingDate] [datetime] NULL,
CONSTRAINT [PK_tblStaff_1] PRIMARY KEY CLUSTERED
(
[staffID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The Model code in the application:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CMS3.Model.DBTables
{
public class tblStaff
{
// [ColumnAttribute(IsPrimaryKey = true, IsDbGenerated = true)]
[Key]
public int staffID { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
public string Alias { get; set; }
public int? addressID { get; set; }
public int? managerID { get; set; }
public int? clientID { get; set; }
public int? jobTitle { get; set; }
public bool? activeFlag { get; set; }
public int? contractorID { get; set; }
public string fullName { get; set; }
public DateTime? securityTrainingDate { get; set; }
public DateTime? CRBCheckDate { get; set; }
public DateTime? CMSTrainingDate { get; set; }
public int? teamID { get; set; }
public virtual tblPlussTeams Team { get; set; }
[ForeignKey("staffID")]
public ICollection<tblClientSchemeHistory> CaseWorker1Schemes { get; set; }
[ForeignKey("staffID")]
public ICollection<tblClientSchemeHistory> CaseWorker2Schemes { get; set; }
}
}
and
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CMS3.Model.DBTables
{
public class tblClientSchemeHistory
{
[Key]
public int clientSchemeHistoryID { get; set; }
public int clientID { get; set; }
public string SchemeName { get; set; }
public DateTime? startDate { get; set; }
public DateTime? endDate { get; set; }
public int? wcID { get; set; }
public string currentModule { get; set; }
[ForeignKey("Caseworker1"), Column(Order = 1)]
public int? caseWorker1ID { get; set; }
[ForeignKey("Caseworker2"), Column(Order = 2)]
public int? caseWorker2ID { get; set; }
public int? LDID { get; set; }
public int? MHID { get; set; }
public int contractTypeID { get; set; }
public int? FSFID { get; set; }
public bool? hiddenFlag { get; set; }
[ForeignKey("clientID")]
public virtual tblClients Client { get; set; }
[InverseProperty("CaseWorker1Schemes")]
public virtual tblStaff Caseworker1 { get; set; }
[InverseProperty("CaseWorker2Schemes")]
public virtual tblStaff Caseworker2 { get; set; }
//public virtual tblClients Client { get; set; }
}
}
Within the context we have to define that tblClientSchemeHistory.Caseworker1 and tblClientSchemeHistory.Caseworker2 both map to tblStaff.staffID
EF Context
..DbSet<tblStaff> Staff { get; set;}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<tblClientSchemeHistory>().HasOptional(b => b.Caseworker1).WithMany(a => a.CaseWorker1Schemes).HasForeignKey(b => b.caseWorker1ID);
modelBuilder.Entity<tblClientSchemeHistory>().HasOptional(b => b.Caseworker2).WithMany(a => a.CaseWorker2Schemes).HasForeignKey(b => b.caseWorker2ID);
modelBuilder.Entity<tblStaff>().HasKey(a => a.staffID);
}
User Repository where the error occurs. When inspecting the newStaffRecord Object, staffID is set to 0, implying the database will deal with indexing the primary key. This exact call works with the same Db using our old application which uses an EDMX Diagram to map relationships.
UserRepository.cs
bool ILoginRepository.CreateUser(string firstName, string lastName, DateTime securityTrainingDate, DateTime cRBCheckDate, DateTime cMSTrainingDate, string password, string email)
{
CMS3Context _db = new CMS3Context();
string salt = null;
string passwordHash = pwdManager.GeneratePasswordHash(password, out salt);
var newStaffRecord = new tblStaff()
{
firstName = firstName,
lastName = lastName,
securityTrainingDate = securityTrainingDate,
CRBCheckDate = cRBCheckDate,
CMSTrainingDate = cMSTrainingDate,
activeFlag = true,
Alias = "",
//TODO - these values should come from dropdowns that are fed in to this method
contractorID = 1,
teamID = null,
fullName = firstName + " " + lastName
};
_db.Staff.Add(newStaffRecord);
_db.SaveChanges();
var newLoginRecord = new tblLogin()
{
staffID = 1,
userName = firstName + "." + lastName,
active = true,
password = passwordHash,
salt = salt,
passwordChanged = DateTime.Now,
failedLoginAttempts = 0
};
_db.Users.Add(newLoginRecord);
_db.SaveChanges();
return (true);
}
Creating new records in different tables using the new application work fine, automatically indexing the PK.
Thanks for reading.
