Home > dotnet 4, Entity Framework > EF Code First: Many to Many Self referencing relationship

EF Code First: Many to Many Self referencing relationship

I have been doing a series of post about Entity Framework Code First and in one of the post I explained various types of relationships.One particular section was about Many to Many relationship and in today’s post I am going to show you how to map a self referencing many to many relationships as it’s a very interesting scenario.

Generally I start with a class diagram and some code but since it’s about an entity referencing to itself, there is not much one can explain through a class diagram. Instead I’ll try to explain it from a database diagram point of view.

Article Table Diagram

Suppose we are creating a domain model for an article which has a title and published date and with that article we want to associate “Related articles” and “Other Related articles”. To keep things simple I will not delve into domain specific rules or algorithms on how an article gets associated as a “related article” and for “Other related article”.

The idea is that when a reader reads an article he gets a list of related article which are “tightly” related to the article and list of “other related article” which are “loosely” related. But as you can see an article can have many “related” and “other related” articles but they all are of the same type i.e. article.

Hmmm at this point we can see how we are building up this conceptual design of article, related articles and other articles and thinking in terms of collection of objects.

Lets write some code …

Article.cs

public class Article
{
   public int Id { get; set; }
   public string Title { get; set; }
   public DateTime PublishedDate { get; set; }

   public virtual ICollection<Article> RelatedArticles { get; set; }
   public virtual ICollection<Article> OtherRelatedArticles { get; set; }

   public Article()
   {
      RelatedArticles = new HashSet<Article>();
      OtherRelatedArticles = new HashSet<Article>();
   }
}

ArticleContent.cs

public class ArticleContent
{
   public int Id { get; set; }
   public virtual Article Article { get; set; }
   public string Content { get; set; }
}

As you can see in Article.cs I have declared collection of article entity and named them RelatedArticles and OtherRelatedArticles. Also I’m thinking from a performance point of view and have separated the content field to another table as I’ll be Lazy loading these entities when building up the object hierarchies.

This way I’ll load the article entity and it’s many related entities but not loading the content field as this will be a huge text field and can impact my lazy loading.

Lets write the configuration class which will map all this together.

ArticleConfiguration.cs

public class ArticleConfiguration : EntityTypeConfiguration<Article>
{
   public ArticleConfiguration()
   {
      this.HasMany(x=>x.RelatedArticles)
         .WithMany(x=>x.OtherRelatedArticles)
         .Map(x=>x.ToTable("RelatedArticles"));
   }
}

Nothing complicated so far and let’s put together our context class

POCOContext.cs

public class POCOContext : DbContext
{
   public DbSet<Product> Product { get; set; }
   public DbSet<Article> Article { get; set; }
   public DbSet<ArticleContent> ArticleContent { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
      modelBuilder.Configurations.Add(new ArticleConfiguration());
   }
}

Lets write a simple unit test to add some dummy data and set up the relationship before we persist the data in the database.

[TestMethod]
public void Should_be_able_to_Add_Articles()
{
   var context = new POCOContext();

   var article1 = new Article
                             {
                                Title = "Article 1",
                                PublishedDate = DateTime.Now,
                             };

   var article2 = new Article
                             {
                                Title = "Related to Article 1",
                                PublishedDate = DateTime.Now,
                             };

   var article3 = new Article
                           {
                              Title = "Related to Article 1",
                              PublishedDate = DateTime.Now,
                           };

   var article4 = new Article
                           {
                              Title = "Other: Related to Article1",
                              PublishedDate = DateTime.Now,
                           };

   var article5 = new Article
                           {
                              Title = "Other: Related to Article1",
                              PublishedDate = DateTime.Now,
                           };

   article1.RelatedArticles.Add(article2);
   article1.RelatedArticles.Add(article3);
   article1.OtherRelatedArticles.Add(article4);
   article1.OtherRelatedArticles.Add(article5);

   context.Article.Add(article1);
   context.SaveChanges();
}

As you can see article2 and article3 are “tightly” related to article 1 and article4 and 5 are “loosely” related to article1.

Lets write a unit test and see how Entity Framework retrieves this object and its entire hierarchy.

Unit Test

[TestMethod]
public void Should_be_able_get_article_by_id()
{
   var context = new POCOContext();
   var article = context.Article
                        .Include("RelatedArticles")
                        .Include("OtherRelatedArticles")
                        .Where(x => x.Id == 1).SingleOrDefault();

   Assert.IsNotNull(article);
}

And the unit test passes with flying colours and now I’ll show the “article” object in watch window to show you that results are exactly what we expected.

Article object in watch window

And no surprises there we got the related articles and other related article for article id 1.

With this post and myprevious post on managing relationship, I have covered almost all major possible scenario for a real life production system and as you can see Entity Framework 4.1 handles it quite well.

Advertisements
  1. halia
    September 28, 2011 at 3:43 pm

    Very nice. Helped me alot!

  2. Ali Hatam
    September 30, 2011 at 8:23 pm

    hello
    this a good article and thanks for it.
    i have an other scenario and i want help me :
    article2 is related to article1
    when I retrieve ralated article to article1 I can see that article2 is retrieved.
    the problem is here : when i try to retrieve article2 related articles , article1 is not in my list.
    article1 and article2 is two related articles but only in 1-way .(only when call related article to article 1)
    i want when i try to access to related article property of article2 , see article1 in my list
    how can I use EF to do that scenario : 2-way realtions ???

  3. MK
    December 3, 2011 at 3:51 pm

    Hello there,

    I cannot thank you enough for your article. I am new to EF and got lost on the exact same scenario (product -> related products). I have read almost every msdn blogs regards to EF code first posts they surely help me understand the EF better but your article is the BEST and most efficient way I’ve ever seen. Again, thank you very much, peace be with you.

    MK

    • December 19, 2011 at 8:29 pm

      Thanks a lot MK and its like your kind words and appreciation makes me go on and write good blog post.

      Thnx

  4. Mariusz.W
    August 10, 2012 at 2:31 pm

    Excellent! Just what I needed

    • August 10, 2012 at 4:22 pm

      Thanks Mariusz and I am glad that this is what you were looking for.

  5. ILIA
    February 12, 2014 at 12:49 pm

    Hello. thanks.
    This example in Ef 6 not worked for me. Output is:

    public partial class r1 : DbMigration
    {
    public override void Up()
    {
    CreateTable(
    “dbo.Article”,
    c => new
    {
    Id = c.Int(nullable: false, identity: true),
    Title = c.String(),
    PublishedDate = c.DateTime(nullable: false),
    })
    .PrimaryKey(t => t.Id);

    CreateTable(
    “dbo.ArticleContent”,
    c => new
    {
    Id = c.Int(nullable: false, identity: true),
    Content = c.String(),
    Article_Id = c.Int(),
    })
    .PrimaryKey(t => t.Id)
    .ForeignKey(“dbo.Article”, t => t.Article_Id)
    .Index(t => t.Article_Id);

    CreateTable(
    “dbo.RelatedArticles”,
    c => new
    {
    Article_Id = c.Int(nullable: false),
    Article_Id1 = c.Int(nullable: false),
    })
    .PrimaryKey(t => new { t.Article_Id, t.Article_Id1 })
    .ForeignKey(“dbo.Article”, t => t.Article_Id)
    .ForeignKey(“dbo.Article”, t => t.Article_Id1)
    .Index(t => t.Article_Id)
    .Index(t => t.Article_Id1);

    }

    public override void Down()
    {
    DropForeignKey(“dbo.ArticleContent”, “Article_Id”, “dbo.Article”);
    DropForeignKey(“dbo.RelatedArticles”, “Article_Id1”, “dbo.Article”);
    DropForeignKey(“dbo.RelatedArticles”, “Article_Id”, “dbo.Article”);
    DropIndex(“dbo.ArticleContent”, new[] { “Article_Id” });
    DropIndex(“dbo.RelatedArticles”, new[] { “Article_Id1” });
    DropIndex(“dbo.RelatedArticles”, new[] { “Article_Id” });
    DropTable(“dbo.RelatedArticles”);
    DropTable(“dbo.ArticleContent”);
    DropTable(“dbo.Article”);
    }
    }

    Can you help me?

  6. Eduardo
    March 12, 2014 at 3:43 pm

    Hello,

    And if we need additional information in the table X?
    How should it be done to generate the database correctly?

    Thanks,

  1. No trackbacks yet.

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

%d bloggers like this: