Foreign key problem when saving data to SQL Server using Entity Framework

Question

C# code and database view

Here is my database schema:

CREATE TABLE University 
(
    UniversityID int NOT NULL IDENTITY,
    UniversityName nvarchar(100) NOT NULL,
    PRIMARY KEY (UniversityID)
)

CREATE TABLE Enstitute 
(
    EnstituteID int NOT NULL IDENTITY,
    UniversityID int NOT NULL,
    EnstituteName nvarchar(100),
    PRIMARY KEY (EnstituteID),
    FOREIGN KEY (UniversityID) REFERENCES University(UniversityID)
) 

Part of the Entity Framework code:

en.EnstituteName = enstituteBox.Text; //entitute name input
uni.UniversityName = universityBox.Text;  //university name input

db.Enstitute.Add(en);
db.University.Add(uni);
db.SaveChanges(); 

My problem is, when I add data that the user inputs, I write the same university name but in the database records the same university names saved with different UniversityID. 1 enstitute has 1 university and 1 university have more than one enstitute. When I select a university, if the university is selected before, I want save to same UniversityID (foreign key) to Enstitute table, not a new Foreign key ID. Can someone help me?

Answer 1

As far as I understood the problem is that you are adding the university even if already exist. Your PK is pointing to the ID, so the name is not part of it. Probably you need to specify a double PK or check if the university name exist already.

Try this

var university = await db.University.FirstOrDefaultAsync(x =>x.UniversityName == uni.UniversityName);  

if(university == null){   
    //your code 
}
else 
{   
    en.UniversityId = university.UniversityId;   
    db.Enstitude.Add(en);
}  
await db.SaveChangesAsync();