How to insert data to a relationship table in EF Core

Question

I have two tables here (Teacher and User). Table User has a relationship to table Teacher. These are the images of Table Teacher, Table User.

I need to make a manage teacher form. So, every time I insert data to table Teacher, it must do insert the data into table User too. By my experiences, I can insert data into the table Teacher, but I can't insert the data to table User.

This is the error: An error occurred while updating the entries. See the inner exception for details” [duplicate]

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_User_Student". The conflict occurred in database "LKSN2017", table "dbo.Student", column 'StudentId'. The statement has been terminated.

I use Entity Framework Core Linq for this. This is the example of the syntax :

db.Teachers.Add(insert);

This is my code to insert the data into table Teacher and User. The variables of gender and passwordGenerate are Strings. It doesn't have a problem, so don't care about the variables.

Teacher insert = new Teacher()
{
  TeacherId = textboxTeacherID.Text.ToString(),
  Name = textboxTeacherName.Text.ToString(),
  Address = textboxAddress.Text.ToString(),
  Gender = gender,
  DateofBirth = Convert.ToDateTime(datepickerDateOfBirth.Text.ToString()),
  PhoneNumber = textboxPhoneNumber.Text.ToString()
};                                      
db.Teachers.Add(insert);

User insertMore = new User()
{
  username = textboxTeacherID.Text.ToString(),
  password = passwordGenerate
};                                    
db.Users.Add(insertMore);
db.SaveChanges();

Ok, actually i have one more form named manage student form. It has the same task with manage teacher. Every time i insert data into table Student, it need to insert the data to table User too.

This is the Images for table Student and Table User : Table Student, Table User

So, if i can finish the manage teacher form, i can finish the manage student form too

Please help me by using the Entity Framework Core Linq. If u don't know about how to fix it by using Entity Framework Core Linq, u guys can give the MySql ways or name of technique to fix this. So, i can search it in browser. Thank you so much!

Please add your teacher and user entities and code how do you create `insert`. — Guru Stron, Jul 01 '20 at 13:13
Are you using SQL or MySQL? See answer I posted yesterday : https://stackoverflow.com/questions/62663042/import-english-wikipedia-dump-into-sql-server#comment110815323_62663042 The line shows how to add constraint and reference. — jdweng, Jul 01 '20 at 13:15
@jdweng, i use Entitiy Framework Core Linq. Example of the sytaxs is : db.Teachers.Add(insert). But, if u only know the way to fix it with MySql. It's Okay, because i have some knowledges about MySql. The MySql i mean is like the ("Select * from teacher;"). — Jeki Gates, Jul 01 '20 at 13:17
I like to create SQL Database using queries in SQL Server Management Studio like I showed in the link. then when you have issues you can delete the existing database and create new from a text file. — jdweng, Jul 01 '20 at 13:21
@Jeki are you using MySQL or SQL Server? Those are two different products, one by Oracle, one by Microsoft. You can't connect to MySQL with SSMS — Panagiotis Kanavos, Jul 01 '20 at 13:23
@JekiGates 1) please add code for entities 2) error refers to table `Students` and `FK_User_Student` please add info about it and relations to user/teacher entities/tables. — Guru Stron, Jul 01 '20 at 13:45
@GuruStron, i don't know why u want the table Students, but i just want to tell you if the ManageTeacherForm only insert data into table Teacher and table User. ManageStudentForm only insert data into table Student and table User. I don't have a form that insert data into table Teacher, table Student and Table User. By the way, i have uploaded the images of table Student and table User. — Jeki Gates, Jul 01 '20 at 13:58
@JekiGates because error you've added specifically refers to it. How `FK_User_Student` is set up? — Guru Stron, Jul 01 '20 at 13:59
@GuruStron i don't have the code for ManageStudentForm for now, because the form do the same task with ManageTeacherForm. So, if i can finish one of the form, so i can finish the other one too — Jeki Gates, Jul 01 '20 at 14:00
@GuruStron oh ya i just noticed, the error contains 'dbo.student'. By the way, the table User has the column named 'userid'. So, if u just insert data into table Teacher from ManageTeacherForm, the id that u created need and will be put in the 'userid' in table User. It's same to the ManageStudentForm, so if u insert data into table Student, the studentid will be put to 'userid' in table User too. The table of User has a relationship to table Teacher and table Student — Jeki Gates, Jul 01 '20 at 14:10

akurasu · Answer 1 · 2020-07-01T13:44:43.147

0

I think you need to set the relationship for Teacher and User is 1-1 example in EF

    public class Teacher
    {
        [ForeignKey("User")]
        public int UserId { get; set; }

        .............
        public virtual User User { get; set; }
    }

    public class User
    {
        [Key]
        public int Id { get; set; }

        ..............

        public virtual Teacher Teacher { get; set; }
    }

and example for insert new teacher like

var newTeacher = new Teacher()
{
    // other property
    User = new User()
    {
        //other property
    }
}
db.Teachers.Add(newTeacher);

don't forget to put the primary key.

edited Jul 01 '20 at 13:44

answered Jul 01 '20 at 13:21

akurasu

13
4

It only has one error " Invalid initializer member declarator " – Jeki Gates Jul 01 '20 at 13:34
If i use User = new User(){}, it gives me an error : 'Teacher' does not contain a definition for 'User'. But, if i use Users = new User(), it gives an error : Cannot implicitly convert type 'LKS_2017.User' to 'System.Collections.Generic.ICollection'. An explicit conversion exists (are you missing a cast?) – Jeki Gates Jul 01 '20 at 14:16
You can check on this post, it may help you https://stackoverflow.com/questions/5957515/entity-framework-saving-data-in-one-to-one-associations – akurasu Jul 01 '20 at 14:47
because you need to make a one to one relationship before do that – akurasu Jul 01 '20 at 14:48
Umm, i don't know if this is wrong or no. But, my user table has two relation ships. The first is table Student (studentId) as primaryKey. The second is table Teacher (teacherId) as primaryKey. For the foreign key, it is from table User (username) as primaryKey. Do we need to change the foreign key from username into userid too?. I have tried it, but it says "Select a column with the same data type properties as the related column" – Jeki Gates Jul 01 '20 at 15:02
it will make the relationship of User to Teacher is many to one, one teacher has many user, you need to change it to one to one first, example in https://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx – akurasu Jul 01 '20 at 15:23
learn more about the relationship in DB in: https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 – akurasu Jul 01 '20 at 15:24

How to insert data to a relationship table in EF Core

1 Answers1