Entity Framework 4.1: Many to Many Relationships (5)

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:

image

Now, we might want to control two things:

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:

image

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.


19 responses

  1. Paul 2011-07-08 at 02:01

    Thanks for the advice. How would you query that data? Say I want to get all orders by a specific employee?

  2. Vincent-Philippe Lauzon 2011-07-08 at 10:00

    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.

  3. jit 2011-12-29 at 08:48

    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

  4. Vincent-Philippe Lauzon 2012-01-06 at 18:27

    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.

  5. Neetin Narendra 2012-02-06 at 01:36

    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 .

  6. Vincent-Philippe Lauzon 2012-02-07 at 08:18

    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.

  7. Neetin Narendra 2012-02-07 at 20:58

    Thank you Vincent . I got the point.

  8. Daniel 2012-04-11 at 21:25

    Can someone explains how a many to many relationship should be used on a form view?

  9. zomgman 2012-09-07 at 02:41

    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.

  10. Vincent-Philippe Lauzon 2012-09-10 at 08:54

    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.

  11. Vincent-Philippe Lauzon 2012-09-10 at 08:55

    You simply remove the employee from the employee list in the order class.

  12. Ed 2012-09-13 at 19:32

    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?

  13. Vincent-Philippe Lauzon 2012-09-16 at 12:04

    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.

  14. Edward 2012-09-17 at 15:09

    Vincent, thank you for explaining. I guess the first thing I need to do is get away from my ADO.Net mindset. Ed

  15. akhilesh 2012-10-19 at 08:15

    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.

  16. Vincent-Philippe Lauzon 2012-10-23 at 09:19

    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).

  17. Dave Brennan 2012-11-20 at 12:01

    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.

  18. Hung Nguyen 2013-04-16 at 16:38

    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

  19. Arun Khatri 2015-05-20 at 23:52

    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.

Leave a comment