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:

  • 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:

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.

About these ads

24 thoughts on “Entity Framework 4.1: Many to Many Relationships (5)

  1. Paul says:

    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.

  2. jit says:

    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.

  3. Neetin Narendra says:

    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 .

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

  4. zomgman says:

    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.

  5. Ed says:

    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.

  6. akhilesh says:

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

  7. Dave Brennan says:

    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.

  8. Hung Nguyen says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s