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:

• Basics (1)
• Override conventions (2)
• Deep Fetch vs Lazy Load (3)
• Complex Types (4)

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.