Insert operation with many-to-many relationship using EF

Question

I've two model classes:

public class Candidate
{
  public int Id { get; set; }
  public string Name { get; set; }
  public ICollection<Job> Jobs { get; set; }
}

public class Job
{
  public int Id { get; set; }
  public string Name { get; set; }
  public ICollection<Candidate> Candidates { get; set; }
}

My DbContext name is JobsContext.

The above code generates me 3 tables Candidates, Jobs & CandidatesJobs(autogenerated by EF)

Now I've records in Jobs table : Id = 1, Name = "Sales" : Id = 2, Name = "Engineer".

I want to associate a new Candidate which I'll be inserting into Candidates table with the 2 records from Jobs table.

Before inserting the Candidate I know the Id's of the Jobs table & I don't wish to make a call to the database to get more details from Jobs table.

How do I do this using Entity Framework 5?

Answer 1

How about this?

Job salesJob; // already fetched from db
Job engineerJob; // already fetched from db

Candidate candidate = new Candidate();
candidate.Name = "John Doe";
candidate.Jobs = new List<Job>(); // you could also do this in the constructor of Candidate
candidate.Jobs.Add(salesJob);
candidate.Jobs.Add(engineerJob);

context.SaveChanges();

This only works if you already fetched the jobs from the database within the same instance of the DbContext, else EF will think that the jobs are 'new' and tries to insert them. If you only have the ids, you could try the following:

var salesJob = new Job { Id = salesJobId };
var engineerJob = new Job { Id = engineerJobId };

context.Jobs.Attach(salesJob);
context.Jobs.Attach(engineerJob);

candiate.Jobs.Add(salesJob);
candiate.Jobs.Add(engineerJob);
context.SaveChanges();

Answer 2

There are two options.

If you are going to have the same context and candidate objects laying around, you can simply add the existing candidate objects to the job. For example: Create your candidates and save them to the database:

JobsContext context = new JobsContext();
var candidate1 = new Candidate() { Name = "John Smith" }; //id 1
var candidate2 = new Candidate() { Name = "Jane Smith" }; //id 2
var candidate3 = new Candidate() { Name = "John Doe" }; //id 3
context.Candidates.Add(candidate1);
context.Candidates.Add(candidate2);
context.Candidates.Add(candidate3);
context.SaveChanges();

Then, create your job:

var job = new Job() { Name = "Good Job" }; //id 1

Finally, add your candidates to the new job variable, add the job to the context and save changes.

job.Candidates.Add(candidate1);
job.Candidates.Add(candidate2);
context.Jobs.Add(job);
context.SaveChanges();

OR

If you are using a DIFFERENT context from the one you created the candidates with, then you can create a new candidate object and attach it to the context prior to adding it to the job.

//different context from above example
JobsContext newContext = new JobsContext();
//this can be a new or existing job, using the job from the example above here
var existingJob = newContext.Jobs.FirstOrDefault(j => j.Id == 1);

Create our candidate object by setting only the ID

var existingCandidate3 = new Candidate() { Id = 3 };

Attach the object to the new context. Note: if the context from the example above is still around, it will not let you do this since it is already tracking the candidate.

newContext.Candidates.Attach(existingCandidate3);

Set the state to Unchanged since we don't want to create a new candidate, just use the existing one.

newContext.Entry(existingCandidate3).State = System.Data.EntityState.Unchanged;

Add it and save the changes.

existingJob.Candidates.Add(existingCandidate3);
newContext.SaveChanges();

Done!