This is part of a series of blog post about Entity Framework 4.1. The past blog entries are:
In this article, I’ll cover the many to many relationships.
Let’s start with the easiest case, we’ll let EF infer the table mapping. I model a many-to-many relationship between order and employee:
public class Order
{
public int OrderID { get; set; }
[Required]
[StringLength(32, MinimumLength = 2)]
public string OrderTitle { get; set; }
[Required]
[StringLength(64, MinimumLength=5)]
public string CustomerName { get; set; }
public DateTime TransactionDate { get; set; }
public byte[] TimeStamp { get; set; }public virtual List<OrderDetail> OrderDetails { get; set; }
public virtual List<Employee> InvolvedEmployees { get; set; }
}public class Employee
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }public virtual List<Order> Orders { get; set; }
}
I simply put a list of employees in order and a list of orders in employee. Voila! Here are the mapped tables:
Now, we might want to control two things:
- The name of the relation table
- The name of the two columns in the relation table
This can all be done with the following code:
modelBuilder.Entity<Employee>()
.HasMany(e => e.Orders)
.WithMany(e => e.InvolvedEmployees)
.Map(m =>
{
m.ToTable("EmployeeOrder");
m.MapLeftKey("EmployeeID");
m.MapRightKey("OrderID");
});
Basically, we say that an employee as many orders, that each employee has many orders (hence we have a many-to-many relationship). We then go and say that the relation table should be named EmployeeOrder, the left key (from employee perspective, so the employee key) should be named employee-id and the right key order-id:
So there you go, you can control a table that doesn’t directly map to a class.
In terms of using such a model, it is quite straightforward and natural:
private static void ManyToMany()
{
using (var context = new MyDomainContext())
{
var order = new Order
{
OrderTitle = "Pens",
CustomerName = "Mcdo’s",
TransactionDate = DateTime.Now,
InvolvedEmployees = new List<Employee>()
};
var employee1 = new Employee { EmployeeName = "Joe", Orders = new List<Order>() };
var employee2 = new Employee { EmployeeName = "Black", Orders = new List<Order>() };context.Orders.Add(order);
order.InvolvedEmployees.Add(employee1);
order.InvolvedEmployees.Add(employee2);context.SaveChanges();
}
In this example I didn’t even bother to add the employees in the employees collection of the data context: EF took care of that for me since they were referenced in a collection.
Thanks for the advice. How would you query that data? Say I want to get all orders by a specific employee?
True I did not cover querying for that specific topic. For your specific example, it’s pretty simple (for a given employee id):
var orders = from e in context.Employees
where e.EmployeeID==theEmployeeID
select e.Orders;
or if you already have a reference to an employee ‘e’:
e.Orders
Is that what you meant?
We could do something more complicated, for instance:
var names = from o in context.Orders
from e in o.InvolvedEmployees
where o.OrderID == 2
select e.EmployeeName;
This would fetch, for the orderID ‘2’ all the employee names related to it.
Basically, as usual with EF, you can walk up and down relationships.
here i have order entity and employee entity. Now i want to check the employees in the order number 1. So i will go and search in orderemployee table. But i do not have orderemployee entity. How can i retrieve the data of this specific query
Something like:
var q = from o in context.Orders
where o.OrderID==1
select o.InvolvedEmployees;
Despite ‘o.OrderID’ appearing in the query, it will never be fetched and materialized. It will only be translated in the SQL query, doing what you suggested, i.e. querying the relation table. Only employees will be materialized and returned.
I hope that answered your question.
If the EmployeeOrder has some attributes of its own . Then will the conceptual model still be a many to many Relation or would it act like TWO one to many relation. Also will the conceptual model will have the entity EmployeeOrder .
When you have more than a pure relation table, you need to make it into a first-class entity and have a one-to-many relationships from each related entity to it.
Thank you Vincent . I got the point.
Can someone explains how a many to many relationship should be used on a form view?
Typically you won’t be able to directly bind your model to a view in that case. You’ll probably want to have something where you show an instance of type A (in a many-to-many relationship with type B) and a list of instances of type B and you let the user ‘add’ B-instances into A.
In the model you then simply add B instances in the virtual list property of A.
Thanks for this example. How would you delete such relations with join models? In your example, when i want to remove the relationship between an Order and an Employee, how would i do that? i basically want to delete only the OrderEmployees join model.
You simply remove the employee from the employee list in the order class.
I noticed in your example that you created the employees. What if the same employee gets a new order? Won’t that wreck your employee table if you create the same employee again?
Hi Ed,
In your example, i.e. an existing employee gets a new oder, you need to load (select) the existing employee and add the new order on it.
Another exemple… let say we have an existing employee & an existing order that have no relationship up to this point but we want to put them in relationship. We load both the employee and the order and add the employee in the Employees collection of the order. Or… alternatively, we add the order in the Orders collection of the employee.
Basically, we don’t recreate object (record). We just add them to respective collections. Remember that those collections are not db-set collections on the db context, in which case that might actually recreate a record in the DB.
Vincent, thank you for explaining. I guess the first thing I need to do is get away from my ADO.Net mindset.
Ed
I wanted to implement same relation with additional column (Count) in EmployeeOrder table.
That i am able to create but dont find a way to get and update “Count” column.
Relation table (e.g. EmployeeOrder) can only contain primary keys from their related tables in order to be represented ‘implicitely’ in the object model. If we want more fields / columns, we need to represent them explicitely, e.g. as an EmployeeOrder object having two properties, Employee & Order (then Employee & Order objects could have a collection of Employee Orders) and the others (e.g. Count in your example).
Sorry but I’m new to MVC, is there anywhere I can find an example of a view for creating the the many to many relationships.
Your example illustrates new Order and new Employee. How about the scenario where you already have existing Order and Employee? How can you insert into the junction table when that happens? Can you give me an example? Thanks
Mu question is how to Bind a Combo Box so that it retrieves and display content corresponding to the Id in a link table and populates itself with the data in the main table?
I am developing a desktop application in Wpf using MVVM and Entity Frameworks. I have the following tables:
1. Party (PartyId, Name)
2. Case (CaseId, CaseNo)
3. Petitioner (CaseId, PartyId) …………. Link Table
I am completely new to .Net and to begin with I downloaded Microsoft’s sample application (Employee Tracker) and following the pattern I have been successful in replicating it for my specific use. The problem started when I wanted to implement many-to-many relationship. The sample application has not covered the scenario where there can be a many-to-many relationship. However with the help of MSDN forum I came to know about a link table and managed to solve entity framework issues pertaining to many-to-many relationship.
In my application when I select a Case Number in the Grid, the details corresponding to the selected case are displayed in the user control on the right. Further, depending upon the number of records in the Petitioner (the link table) the corresponding viewmodel loads that much numbers of the ComboBoxes where I want Name of petitioner stored in the Party table to be displayed as selected Item. I also want these ComboBox/es to be populated with the names stored in the Party table. Despite Viewmodel loads correct numbers of ComboBoxes, the ComboBoxes neither display name of the associated Petitioners nor they get populated with the Party name.
Please help.