Working with Foreign Keys

Article
08/29/2011

Starting with the .NET Framework version 4, you can expose foreign key properties on entity types and define relationships through foreign keys. The Include foreign key columns in the model option in the Entity Data Model Wizard is selected by default. When this option is selected, the generated entity objects have scalar properties that map to foreign key columns.

Foreign key properties allow you to create or modify a relationship without querying for the principal object. When you include foreign key properties, you can still modify a relationship by updating the reference to the principal object on the dependent or adding a dependent object to a collection on the principal object. A common way to access references and collections on entities is through navigation properties. For more information, see Navigation Properties.

The Entity Framework tries to keep references, collections, and foreign key properties in sync. If you update a foreign key property of a dependent object, the reference on this object and the collection on the principal object should be synchronized. For this synchronization to occur, the entities must be attached to the object context and the entities must be tracked by the Entity Framework. For more information, see Identity Resolution, State Management, and Change Tracking and Defining and Managing Relationships.

The examples in this topic are based on the School Model. The entity types in these examples are generated by the Entity Data Model tools. The generated types are tracked by the Entity Framework. If you are working with POCO types, see Tracking Changes in POCO Entities.

The following example shows how to change the relationship between two existing objects.

Dim studentId As Integer = 6
Dim enrollmentID As Integer = 2

Using context = New SchoolEntities()
    ' Get StudentGrade. 
    Dim grade = (From g In context.StudentGrades
                 Where g.EnrollmentID = enrollmentID
                 Select g).First()

    ' Change the relationship. 
    grade.StudentID = studentId
    ' You can access Person reference object on the grade object 
    ' without loading the reference explicitly when 
    ' the lazy loading option is set to true. 
    Console.WriteLine(grade.Person.PersonID)
    ' Save the changes. 
    context.SaveChanges()
End Using

int studentId = 6;
int enrollmentID = 2;

using (var context = new SchoolEntities())
{
    // Get StudentGrade.
    var grade = (from g in context.StudentGrades
                 where g.EnrollmentID == enrollmentID
                 select g).First();

    // Change the relationship.
    grade.StudentID = studentId;
    // You can access Person reference object on the grade object
    // without loading the reference explicitly when
    // the lazy loading option is set to true.
    Console.WriteLine(grade.Person.PersonID);
    // Save the changes.
    context.SaveChanges();
}

The following example shows how to set the relationship between the new dependent object and existing principal objects by setting the foreign key properties.

' The following example creates a new StudentGrade object and associates 
' the StudentGrade with the Course and Person by 
' setting the foreign key properties. 

Using context As New SchoolEntities()
    ' The database will generate the EnrollmentID. 
    ' To create the association between the Course and StudentGrade, 
    ' and the Student and the StudentGrade, set the foreign key property 
    ' to the ID of the principal. 
    Dim newStudentGrade = New StudentGrade With
    {
        .EnrollmentID = 0,
        .Grade = CDec(4.0),
        .CourseID = 4022,
        .StudentID = 17
    }

    ' Adding the new object to the context will synchronize 
    ' the references with the foreign keys on the newStudentGrade object. 
    context.StudentGrades.AddObject(newStudentGrade)

    ' You can access Course and Student objects on the newStudentGrade object
    ' without loading the references explicitly because
    ' the lazy loading option is set to true in the constructor of SchoolEntities.
    Console.WriteLine("Student ID {0}:", newStudentGrade.Person.PersonID)
    Console.WriteLine("Course ID {0}:", newStudentGrade.Course.CourseID)

    context.SaveChanges()
End Using

// The following example creates a new StudentGrade object and associates
// the StudentGrade with the Course and Person by
// setting the foreign key properties. 

using (SchoolEntities context = new SchoolEntities())
{
    StudentGrade newStudentGrade = new StudentGrade
    {
        // The database will generate the EnrollmentID.
        EnrollmentID = 0,
        Grade = 4.0M,
        // To create the association between the Course and StudentGrade, 
        // and the Student and the StudentGrade, set the foreign key property 
        // to the ID of the principal.
        CourseID = 4022,
        StudentID = 17,
    };

    // Adding the new object to the context will synchronize
    // the references with the foreign keys on the newStudentGrade object.
    context.StudentGrades.AddObject(newStudentGrade);

    // You can access Course and Student objects on the newStudentGrade object
    // without loading the references explicitly because
    // the lazy loading option is set to true in the constructor of SchoolEntities.
    Console.WriteLine("Student ID {0}:", newStudentGrade.Person.PersonID);
    Console.WriteLine("Course ID {0}:", newStudentGrade.Course.CourseID);
    
    context.SaveChanges();
}

The following example shows how to set the relationship between the new dependent and new principal object.

Using context = New SchoolEntities()
    ' The database will generate PersonID. 
    ' The object context will get the ID 
    ' After the SaveChanges is called. 
    Dim newStudent = New Person With
    {
        .PersonID = 0,
        .LastName = "Li",
        .FirstName = "Yan"
     }
    ' The database will generate EnrollmentID. 
    ' The object context will get the ID 
    ' After the SaveChanges is called. 
    Dim newStudentGrade = New StudentGrade With
    {
        .EnrollmentID = 0,
        .Grade = CDec(4.0),
        .StudentID = 50
    }

    ' Add newStudent to object context. 
    ' The newStudent's state will change from Detached to Added. 
    context.People.AddObject(newStudent)

    ' To associate the new objects you can do one of the following: 
    ' Add the new dependent object to the principal object: newStudent.StudentGrades.Add(newStudentGrade). 
    ' Assign the reference (principal) object to the navigation property of the 
    ' dependent object: newStudentGrade.Person = newStudent. 
    ' Both of these methods will synchronize the navigation properties on both ends of the relationship. 

    ' Adding the newStudentGrade to newStudent will change newStudentGrade's status 
    ' from Detached to Added. 
    newStudent.StudentGrades.Add(newStudentGrade)
    ' Navigation properties in both directions will work immediately. 
    Console.WriteLine("Access StudentGrades navigation property to get the count: ", _
                      newStudent.StudentGrades.Count)
    Console.WriteLine("Access Person navigation property: {0} ", _
                      newStudentGrade.Person.FirstName)

    context.SaveChanges()
End Using

using (var context = new SchoolEntities())
{
    Person newStudent = new Person
    {
        // The database will generate PersonID.
        // The object context will get the ID 
        // After the SaveChanges is called.
        PersonID = 0,
        LastName = "Li",
        FirstName = "Yan"
    };
    StudentGrade newStudentGrade = new StudentGrade
    {
        // The database will generate EnrollmentID.
        // The object context will get the ID 
        // After the SaveChanges is called.
        EnrollmentID = 0,
        Grade = 4.0M,
        StudentID = 50
    };

    // Add newStudent to object context. 
    // The newStudent's state will change from Detached to Added.
    context.People.AddObject(newStudent);

    // To associate the new objects you can do one of the following:
    // Add the new dependent object to the principal object: newStudent.StudentGrades.Add(newStudentGrade).
    // Assign the reference (principal) object to the navigation property of the 
    // dependent object: newStudentGrade.Person = newStudent.
    // Both of these methods will synchronize the navigation properties on both ends of the relationship.

    // Adding the newStudentGrade to newStudent will change newStudentGrade's status
    // from Detached to Added.
    newStudent.StudentGrades.Add(newStudentGrade);
    // Navigation properties in both directions will work immediately.
    Console.WriteLine("Access StudentGrades navigation property to get the count: ",
        newStudent.StudentGrades.Count);
    Console.WriteLine("Access Person navigation property: {0} ", newStudentGrade.Person.FirstName);

    context.SaveChanges();
}

With foreign keys association, you can still use the reference to set the relationship as you would do in the version prior to the Entity Framework 4.

' The following example creates a new StudentGrade and associates 
' the StudentGrade with the Course and Person by 
' setting the navigation properties to the Course and Person objects that were returned 
' by the query. 
' You do not need to call AddObject() in order to add the grade object 
' to the context, because when you assign the reference 
' to the navigation property the objects on both ends get synchronized by the Entity Framework. 
' Note, that the Entity Framework will not synchronize the ends untill the SaveChanges method 
' is called if your objects do not meet the change tracking requirements. 
Using context = New SchoolEntities()
    Dim courseID = 4022
    Dim course = (From c In context.Courses
                 Where c.CourseID = courseID
                 Select c).First()

    Dim personID = 17
    Dim student = (From p In context.People
                  Where p.PersonID = personID
                  Select p).First()

    ' The database will generate the EnrollmentID. 
    ' Use the navigation properties to create the association between the objects. 
    Dim newStudentGrade = New StudentGrade With
    {
        .EnrollmentID = 0,
        .Grade = CDec(4.0),
        .Course = course,
        .Person = student
    }
    context.SaveChanges()
End Using

// The following example creates a new StudentGrade and associates
// the StudentGrade with the Course and Person by
// setting the navigation properties to the Course and Person objects that were returned
// by the query. 
// You do not need to call AddObject() in order to add the grade object
// to the context, because when you assign the reference 
// to the navigation property the objects on both ends get synchronized by the Entity Framework.
// Note, that the Entity Framework will not synchronize the ends untill the SaveChanges method
// is called if your objects do not meet the change tracking requirements. 
using (var context = new SchoolEntities())
{
    int courseID = 4022;
    var course = (from c in context.Courses
                 where c.CourseID == courseID
                 select c).First();

    int personID = 17;
    var student = (from p in context.People
                  where p.PersonID == personID
                  select p).First();

    StudentGrade grade = new StudentGrade
    {
        // The database will generate the EnrollmentID.
        Grade = 4.0M,
        // Use the navigation properties to create the association between the objects.
        Course = course,
        Person = student
    };
    context.SaveChanges();
}

The following demonstrates a simple communication between a client and a service, where the client requests an object from the service, the client updates the object and calls the service to save the changes to the database.

The service defines two methods:

Private Shared Function GetOriginalValue(ByVal ID As Integer) As StudentGrade
    Dim originalItem As StudentGrade
    Using context As New SchoolEntities()
        originalItem = context.StudentGrades.Where(Function(g) g.EnrollmentID = ID).FirstOrDefault()

        context.Detach(originalItem)
    End Using
    Return originalItem
End Function

Private Shared Sub SaveUpdates(ByVal updatedItem As StudentGrade)
    Using context As New SchoolEntities()
        ' Query for the StudentGrade object with the specified ID. 
        Dim original = (From o In context.StudentGrades
            Where o.EnrollmentID = updatedItem.EnrollmentID
            Select o).First()

        ' Apply changes. 
        context.StudentGrades.ApplyCurrentValues(updatedItem)

        ' Save changes. 
        context.SaveChanges()
    End Using
End Sub

static private StudentGrade GetOriginalValue(int ID)
{
    StudentGrade originalItem;
    using (SchoolEntities context
        = new SchoolEntities())
    {
        originalItem =
            context.StudentGrades.Where(g => g.EnrollmentID == ID).FirstOrDefault();

        context.Detach(originalItem);
    }
    return originalItem;
}

static private void SaveUpdates(StudentGrade updatedItem)
{
    using (SchoolEntities context
        = new SchoolEntities())
    {
        // Query for the StudentGrade object with the specified ID.
        var original = (from o in context.StudentGrades
                         where o.EnrollmentID == updatedItem.EnrollmentID
                         select o).First();

        // Apply changes.
        context.StudentGrades.ApplyCurrentValues(updatedItem);

        // Save changes.
        context.SaveChanges();
    }
}

The client updates the values of the foreign key properties and sends the updated object to the service:

' A client calls a service to get the original object. 
Dim studentGrade As StudentGrade = GetOriginalValue(3)
' Change the relationships. 
studentGrade.CourseID = 5
studentGrade.StudentID = 10
' The client calls a method on a service to save the updates.

// A client calls a service to get the original object.
StudentGrade studentGrade = GetOriginalValue(3);
// Change the relationships.
studentGrade.CourseID = 5;
studentGrade.StudentID = 10;
// The client calls a method on a service to save the updates. 
SaveUpdates(studentGrade);

Working with Foreign Keys

See Also

Concepts

Additional resources