As I said in the title of the topic, I have a problem with DataContext.SubmitChanges() method. I have two tables: Users and Administrators (and other roles as Doctors etc, but now I am working with these two tables). SQL scripts for those two tables: Users:
`CREATE TABLE [dbo].[Users](
[usrId] [int] IDENTITY(1,1) NOT NULL,
[login] [nvarchar](50) NOT NULL,
[password] [nvarchar](50) NOT NULL,
[role] [char](3) NOT NULL,
[staffId] [int] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[usrId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]`
And for Administrators:
`CREATE TABLE [dbo].[Administrators](
[adminId] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[surname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Administrators] PRIMARY KEY CLUSTERED
(
[adminId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]`
Now Administrators join Users on Administrators.adminId = Users.staffId (adminId is PK, staffId is FK). Simply - I know that User from table Users is an Administrator when User.staffId == Administrator.adminId AND User.role = "ADM".
So where is the probem? I made LINQ mapping for this database:
[Database(Name = "BD_PROJ_GKiO3")]
public class BD_PROJ_GKiO3 : DataContext
{
public Table<User> Users;
public Table<Doctor> Doctors;
public Table<LaboratoryWorker> LaboratoryWorkers;
public Table<LaboratoryManager> LaboratoryManagers;
public Table<Registrator> Registrators;
public Table<Administrator> Administrators;
public Table<Appointment> Appointments;
public Table<ExaminationType> ExaminationTypes;
public Table<LaboratoryExamination> LaboratoryExaminations;
public Table<Patient> Patients;
public Table<PhysicalExamination> PhysicalExaminations;
public BD_PROJ_GKiO3(IDbConnection connection) : base(connection)
{
}
}
And mapping for User and Administrator:
[Table(Name = "Users")]
public class User
{
//kolumna PK w tabeli Users
[Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "Int NOT NULL IDENTITY")]
public int usrId { get; set; }
//Reszta kolumn
[Column] public string login { get; set; }
[Column] public string password { get; set; }
[Column] public string role { get; set; }
[Column] public string status { get; set; }
[Column] public int staffId { get; set; }
//Mapowanie relacji z tabelami aktorów
private EntitySet<Doctor> _Doctors;
[Association(Storage = "_Doctors", OtherKey = "docId", ThisKey = "staffId")]
public EntitySet<Doctor> Doctors
{
get { return this._Doctors; }
set { this._Doctors.Assign(value); }
}
private EntitySet<Registrator> _Registrators;
[Association(Storage = "_Registrators", OtherKey = "registratorId", ThisKey = "staffId")]
public EntitySet<Registrator> Registrators
{
get { return this._Registrators; }
set { this._Registrators.Assign(value); }
}
private EntitySet<Administrator> _Administrators;
[Association(Storage = "_Administrators", OtherKey = "adminId", ThisKey = "staffId", IsForeignKey = true)]
public EntitySet<Administrator> Administrators
{
get { return this._Administrators; }
set { this._Administrators.Assign(value); }
}
private EntitySet<LaboratoryManager> _LaboratoryManagers;
[Association(Storage = "_LaboratoryManagers", OtherKey = "laboratoryPrincipalId", ThisKey = "staffId")]
public EntitySet<LaboratoryManager> LaboratoryManagers
{
get { return this._LaboratoryManagers; }
set { this._LaboratoryManagers.Assign(value); }
}
private EntitySet<LaboratoryWorker> _LaboratoryWorkers;
[Association(Storage = "_LaboratoryWorkers", OtherKey = "laboratoryWorkerId", ThisKey = "staffId")]
public EntitySet<LaboratoryWorker> LaboratoryWorkers
{
get { return this._LaboratoryWorkers; }
set { this._LaboratoryWorkers.Assign(value); }
}}
[Table(Name = "Administrators")]
public class Administrator
{
//PK w tabeli Administrators
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int adminId { get; set; }
//Reszta kolumn
[Column] public string name { get; set; }
[Column] public string surname { get; set; }
//Mapowanie relacji tabeli Administrators z tabelą Users
private EntitySet<User> _Users;
[Association(Storage = "_Users", OtherKey = "staffId", IsForeignKey = true, ThisKey = "adminId")]
public EntitySet<User> Users
{
get { return this._Users; }
set { this._Users.Assign(value); }
}}
Of course both classes have constructors, but I didn't pasted it here (when I will have to, I will do it).
So above you can see, how I mapped tables and relations from database into objects.
The problem is when I'm trying to update existing row in table Users with related row in table Administrators. I've created User instance (toUpdate), which stores data, with which table row should be updated, next, I'm adding to it an Administrator instances data (admin). As first - I'm getting DataContext object, next - I'm looking in database for my User to edit.
As first:
BD_PROJ_GKiO3 newDB = new BD_PROJ_GKiO3(Program.getConnection());
User u = newDB.Users.SingleOrDefault(us => us.usrId == currentlyEdited.usrId);
Proper User (u) is found, so I'm trying to execute code below:
u.login = toUpdate.login;
u.role = toUpdate.role;
u.status = toUpdate.status;
u.staffId = toUpdate.staffId;
u.Administrators.ElementAt(0).name = admin.name;
u.Administrators.ElementAt(0).surname = admin.surname;
newDB.SubmitChanges();
And during executing last line (newDB.SubmitChanges()) is thrown NullReferenceException.
Stack Trace: ExceptionStackTrace
Thanks in advance to everyone who will try to help me, I will be very grateful! Edit: I just forgot to mention, that I think database made and mapped this way probably works well, because when my application starts, I'm creating new database as public field of program (application root) and using it, I'm giving user a possibility to log in, what works perfectly (on log in step we have to check data from Users and for example Administrators).