Elides junction table, how do I insert a row without querying the database for the entities referenced by the row?

Question

Let's say we have a simple schema:

Employee
--------
Id
EmployeeName


Project
-------
Id
ProjectName


EmployeeProject
---------------
EmployeeId
ProjectId

In a previous version of EF, I remember the junction table getting added to the model (or maybe it was always elided and I'm thinking of a table that had additional columns). In EF 6, the table is elided and the model looks like this:

EF Model

Is there any way to add rows to the junction table without first querying the database to get the appropriate entity? E.g., if I want to create a new Project, I might get a list of Employee Ids from the front-end; I would have to query the database to get the Employees, and then add them to the Project's Employee collection, and then hit the database again to save. Is there a way to do that with only one call to the database?

Update

Here's an example of what I'm trying to solve (pseudocode):

CreateProject (string name, List<int> employeeIds)
{
    var proj = new Project;
    proj.ProjectName = name;
    context.Projects.Add(proj);

    foreach(var id in employeeIds)
    {
        // we have the id, but we need to get the actual Employee entity by hitting the database
        var employee = context.Employees.First(e => e.Id == id); 
        proj.Employees.Add(employee);
    }

    context.SaveChanges();
}

If the junction table existed in the model, I could simply do:

CreateProject (string name, List<int> employeeIds)
{
    var proj = new Project;
    proj.ProjectName = name;
    context.Projects.Add(proj);

    foreach(var id in employeeIds)
    {
        var empProj = new EmployeeProject();
        empProj.Project = proj;

        // we don't have the Employee entity, but we can set the Id and everything works.
        empProj.EmployeeId = id;  

        context.EmployeeProjects.Add(empProj);
    }

    context.SaveChanges();  // only need to hit database once, after all entities have been added
}

score 4 · Accepted Answer · answered Aug 11 '14 at 18:04

You don't have to retrieve the items first. When working with disconnected object, you can just attach the existing entity or change the state to Unchanged.

Entity Framework takes care of tracking the state of entities while they are connected to a context, but in disconnected or N-Tier scenarios you can let EF know what state your entities should be in.

More: http://msdn.microsoft.com/en-us/data/jj592676

You can just attach the employee to reduce database round trip.

foreach(var id in employeeIds)
{
    var employee = new Employe { Id = id }; 
    db.Set<Employee>().Attach(employee); // context.Entry(employee).State = EntityState.Unchanged;
    proj.Employees.Add(employee);
}

simon at rcl · Answer 2 · 2014-08-11T17:21:28.607

1

EF takes care of it all for you; you can ignore it. Just add the employee to the Project.Employees collection and/or the Project to the Employee.Projects collection and it will take cate of the junction table for you. The same when deleting: just remove objects from the collections and the junction table rows will be deleted.

EDIT after clarification: If you're working with EF objects, an instance of an existing EF object has to be got via EF. When using EF you don't keep a list of Employee Ids around; you keep a list of Enployees. EF tracks things it knows about: if you just create a blank object and fill in stuff, EF will think its a new one.

edited Aug 11 '14 at 17:21

answered Aug 11 '14 at 16:35

simon at rcl

7,326
1
17
24

That's the thing though, I have to make a call to the database to get the "Employee" entity from the id. If the multiplicity was 1 on the Project side (that is, Project had a column EmployeeId), I could set either Project.Employee or Project.EmployeeId, depending on which I had available. I've added some sample code to the question that shows the problem more clearly. – Turch Aug 11 '14 at 16:53
Your pseudo-code looks about right. What problem is it giving you? – simon at rcl Aug 11 '14 at 16:56
Sorry: I was looking at trhe second example with the EmployeeProject object6: that on'e wrong and won't work as the model doesn't have an EmployeeProject class (but knows that the table exists). The first set on pseudo-code does look fine. What problem does it give you? – simon at rcl Aug 11 '14 at 17:03
This line `var employee = context.Employees.First(e => e.Id == id); ` makes a call to the database. The question asks "how do I insert a row **without** querying the database for the entities referenced by the row?". The second sample would accomplish that, but as you said that class doesn't exist. – Turch Aug 11 '14 at 17:04
OK. That wasn't clear to me. If you're working with EF objects, an instance of an existing EF object has to be got via EF. When using EF you don't keep a list of Employee Ids around; you keep a list of Enployees. – simon at rcl Aug 11 '14 at 17:07
Ok cool, so the answer is "not possible". If you modify yours to say something along those lines I'll accept :) Also, now I see the relevant documentation: "You can choose to use one or both types of associations in your model. However, if you have a pure many-to-many relationship that is connected by a join table that contains only foreign keys, the EF will use an independent association to manage such many-to-many relationship" ([src](http://msdn.microsoft.com/en-us/data/jj713564.aspx)). Seems odd that they would give you the choice in one case but not in the other. – Turch Aug 11 '14 at 17:17

Elides junction table, how do I insert a row without querying the database for the entities referenced by the row?

2 Answers2