c# - Setting a foreign key to null when using entity framework code first

Question

I'm using the database first implementation of Entity Framework Code First as the data layer for a project, but I've run into a problem.

I need to be able to set a foreign key to null in order to remove an association in the database.

I have 2 objects. One is called Project.

public class Project
{
    public int ProjectId {get; set;}
    public Employee Employee {get;set;}
}

public class Employee
{
    public int EmployeeId {get; set;}
    public string EmployeeName {get;set;}
}

This matches what I have in the Database:

CREATE TABLE Project(
    ProjectId int IDENTITY(1,1) NOT NULL,
    EmployeeId int NULL
)

CREATE TABLE Project(
    EmployeeId int IDENTITY(1,1) NOT NULL,
    EmployeeName varchar(100) NULL
)

I can assign an Employee to a project. However, I want to be able to remove an employee from a project and have the Employee field be null. In my UI this will show as 'No EMployee Assigned'.

However, short of a direct sql query, I cannot seem to find a way to do this in the entity framework 4.1.

I've tried:

public void RemoveEmployeeFromProject(int projectId)
{
    var project = Context.Projects.FirstOrDefault(x => x.ProjectId == projectId);
    project.Employee = null;
    Context.SaveChanges();
}

But this doesn't do anything.

Does anyone have any ideas?

Answer 1

I think the problem is that as far as the context is concerned, you haven't actually changed anything.

You can use the lazy loading approach previously suggested by using virtual, but since you haven't requested that the Employee be loaded yet, it's still null. You could try this:

var forceLoad = project.Employee;
project.Employee = null; // Now EF knows something has changed
Context.SaveChanges();

Alternatively, explicitly include it in your original request:

var project = Context.Projects.Include(x => x.Employee).FirstOrDefault(x => x.ProjectId == projectId);
project.Employee = null;
Context.SaveChanges();

On a side note, FirstOrDefault will return null if no Project matches the given id. If you know the project exists, you can just use First. You could even use Single which will assert that there is only one such project. If you continue to use FirstOrDefault, I'd recommend checking for null before working with project.