EntityFramework Same Table Many to Many Relationship
In order to create a many-to-many relationship with Database-First approach you need to setup a database schema that follows certain rules:
- Create a
Products
table with a columnProductID
as primary key - Create a
ProductRelations
table with a columnProductID
and a columnRelatedID
and mark both columns as primary key (composite key) - Don't add any other column to the
ProductRelations
table. The two key columns must be the only columns in the table to let EF recognize this table as a link table for a many-to-many relationship - Create two foreign key relationships between the two tables:
- The first relationship has the
Products
table as primary-key-table with theProductID
as primary key and theProductRelations
table as foreign-key-table with only theProductID
as foreign key - The second relationship also has the
Products
table as primary-key-table with theProductID
as primary key and theProductRelations
table as foreign-key-table with only theRelatedID
as foreign key
- The first relationship has the
- Enable cascading delete for the first of the two relationships. (You can't do it for both. SQL Server won't allow this because it would result in multiple cascading delete paths.)
If you generate an entity data model from those two tables now you will get only one entity, namely a Product
entity (or maybe Products
if you disable singularization). The link table ProductRelations
won't be exposed as an entity.
The Product
entity will have two navigation properties:
public EntityCollection<Product> Products { get { ... } set { ... } }public EntityCollection<Product> Products1 { get { ... } set { ... } }
These navigation collections are the two endpoints of the same many-to-many relationship. (If you had two different tables you wanted to link by a many-to-many relationship, say table A
and B
, one navigation collection (Bs
) would be in entity A
and the other (As
) would be in entity B
. But because your relationship is "self-referencing" both navigation properties are in entity Product
.)
The meaning of the two properties are: Products
are the products related to the given product, Products1
are the products that refer to the given product. For example: If the relationship means that a product needs other products as parts to be manufactured and you have the products "Notebook", "Processor", "Silicon chips" then the "Processor" is made of "Silicon chips" ("Silicon chips" is an element in the Products
collection of the Processor
product entity) and is used by a "Notebook" ("Notebook" is an element in the Products1
collection of the Processor
product entity). Instead of Products
and Products1
the names MadeOf
and UsedBy
would be more appropriate then.
You can safely delete one of the collections from the generated model if you are only interested in one side of the relationship. Just delete for example Products1
in the model designer surface. You can also rename the properties. The relationship will still be many-to-many.
Edit
As asked in a comment the model and mapping with a Code-First approach would be:
Model:
public class Product{ public int ProductID { get; set; } public ICollection<Product> RelatedProducts { get; set; }}
Mapping:
public class MyContext : DbContext{ public DbSet<Product> Products { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Product>() .HasMany(p => RelatedProducts) .WithMany() .Map(m => { m.MapLeftKey("ProductID"); m.MapRightKey("RelatedID"); m.ToTable("product_related"); }); }}
Lets take your Example:
Related table
Related_id PK Related_name Date
Product Table
Product_id PK Related_id FK Product_Name Date
How to Represent it in EF
Related Model Class named as RelatedModel
[Key] public int Related_id { get; set; } public string Related_name {get;set} public Datetime Date{get;set;}
Product Model Class named as ProductModel
[Key] public int Product_id { get; set; } public string Product_name {get;set} public string Related_id {get;set} public Datetime Date{get;set;} [ForeignKey("Related_id ")] //We can also specify here Foreign key public virtual RelatedModel Related { get; set; }
In this way we can Create Relations between Two table
Now In Case of Many to Many Relation I would like to take another Example here
Suppose I have a Model Class Enrollment.cs
public class Enrollment{ public int EnrollmentID { get; set; } public int CourseID { get; set; } public int StudentID { get; set; } public decimal? Grade { get; set; } public virtual Course Course { get; set; } public virtual Student Student { get; set; }}
Here CourseID and StudentId are the two foreign Keys
Now I Have another Class Course.cs where we will create Many to Many Relation.
public class Course{ public int CourseID { get; set; } public string Title { get; set; } public int Credits { get; set; } public virtual ICollection<Enrollment> Enrollments { get; set; }}
Hope This will help!!!