CRUD Many-to-Many Entity Framework

• Download source - 3.3 MB

Introduction

In many systems, it’s common modeling tables that have many-to-many relationships, this structure usually has three tables, two have primary keys and the third has two columns to relate these primary keys. The columns in the third table are, at the same time, primary key and foreign key. When mapping in Entity Framework designer, three tables becomes two entities.

Using the Code

The project has two layers to maintain as simple as possible:

• Model: Layer that connects with database
• UI.Web: ASP.NET WebForm project that references to Model and presents one aspx page with controls to demonstrate operations in database

Model and Mapping

I use just three tables in the example, Supplier, Product and ProductSupplier. In that structure, one supplier can supply one or many products and one product can be supplied by one or many suppliers.

The database diagram has three tables:

But when mapping to EntityFramework, only two entities are present.

Mapping entities add navigation property to represent that relation.

namespace ManyToMany.Model
{
    using System;
    using System.Collections.Generic;
    
    public partial class Product
    {
        public Product()
        {
            this.Supplier = new HashSet<supplier>();
        }
    
        public long ProductID { get; set; }
        public string ProductName { get; set; }
		
		//navigation property to Supplier
        public virtual ICollection<supplier> Supplier { get; set; }
    }
}

namespace ManyToMany.Model
{
    using System;
    using System.Collections.Generic;
    
    public partial class Supplier
    {
        public Supplier()
        {
            this.Product = new HashSet<product>();
        }
    
        public long SupplierID { get; set; }
        public string SupplierName { get; set; }
    
		// navigation property to Product
        public virtual ICollection<product> Product { get; set; }
    }
}	

In the next sections, I will explain how to insert, delete, update and select this association table using LINQ to Entities.

Insert

There are two situations to insert data in tables that has a relationship many-to-many.

First, when data do not exist in tables, add instances to context, add an instance to navigation property and call SaveChanges method from context. That is possible because Entity Framework, at the time of insert, puts primary key value (if Identity, AutoIncrement) in correspondent entity’s property inserted.

public void InsertWithoutData(Product prod, Supplier sup) 
{
	using (ManyToManyEntities conn = new ManyToManyEntities())
	{
		//add instances to context
		conn.Product.Add(prod);
		conn.Supplier.Add(sup);

		// add instance to navigation property
		prod.Supplier.Add(sup);

		//call SaveChanges from context to confirm inserts
		conn.SaveChanges();
	}
}	

Second case, data already exists in tables and it's necessary to relate them, pass the primary key to two tables/entity, add and attach to context object, add instance to entity navigation property and finally call SaveChanges method.

public void InsertWithData(long productID, long supplierID) 
{
	using (ManyToManyEntities conn = new ManyToManyEntities())
	{

		/*
			* this steps follow to both entities
			* 
			* 1 - create instance of entity with relative primary key
			* 
			* 2 - add instance to context
			* 
			* 3 - attach instance to context
			*/

		// 1
		Product p = new Product { ProductID = productID };
		// 2
		conn.Product.Add(p);
		// 3
		conn.Product.Attach(p);

		// 1
		Supplier s = new Supplier { SupplierID = supplierID };
		// 2
		conn.Supplier.Add(s);
		// 3
		conn.Supplier.Attach(s);

		// like previous method add instance to navigation property
		p.Supplier.Add(s);

		// call SaveChanges
		conn.SaveChanges();
	}
}	

Delete

To delete relationship, instead of call Remove from context, we need to call it from navigation property.

public void DeleteRelationship(long productID, long supplierID) 
{
	using (ManyToManyEntities conn = new ManyToManyEntities())
	{
		// return one instance each entity by primary key
		var product = conn.Product.FirstOrDefault(p => p.ProductID == productID);
		var supplier = conn.Supplier.FirstOrDefault(s => s.SupplierID == supplierID);

		// call Remove method from navigation property for any instance
		// supplier.Product.Remove(product);
		// also works
		product.Supplier.Remove(supplier);

		// call SaveChanges from context
		conn.SaveChanges();
	}
}

Update

The update statement compounds two statements, delete and insert, call delete and insert methods then.

public void UpdateRelationship(long oldProductID, long oldSupplierID, 
	long newProductID, long newSupplierID) 
{
	DeleteRelationship(oldProductID, oldSupplierID);
	InsertWithData(newProductID, newSupplierID);
}	

Select

The select statement is a little bit different than normal, at first from use context to returns the first entity, then entity instance accesses the navigation property at the second from, this identifies that data comes from relationship table, but only ids do not bring useful information in this case, it’s necessary join to return useful data, or product or supplier.

DTOGenericObject is just an object to transport data from database to objects in webforms.

public List<dtogenericobject> GetProductBySupplier(long supplierID)
{
	using (ManyToManyEntities conn = new ManyToManyEntities())
	{
		var result = (
			// instance from context
			from a in conn.Supplier
			// instance from navigation property
			from b in a.Product
			//join to bring useful data
			join c in conn.Product on b.ProductID equals c.ProductID
			where a.SupplierID == supplierID
			select new DTOGenericObject
			{
				ID = c.ProductID,
				Name = c.ProductName
			}).ToList();

		return result;
	}
}	

Points of Interest

When mapping association tables using Entity Framework, three tables involved become two entities. All operations should be with navigation property that was added in entities.