Entity Framework many to many relationship.

In this post I will be demonstrating how you can create many to many relationship between classes by using EF code first approach with DataAnnotation attributes. You can use the EF fluent API as well for many to many configuration which I have not covered here. I will be using the classes post and tag in my model which has many to many relationship between them i.e a single post can have many tags and a single tag can have many posts.

To get started with many to many configuration between the post and the tag you have to create the classes first. The EF will automatically create the third table when the application will execute, so you will not be creating the third table manually in the model. Let's get started with the code.

   public class Post
   {
     public long PostID { get; set; }

     [Required]
     [MaxLength(255)]
     public string Title { get; set; }

     [Required]
     [MaxLength(4000)]
     [DataType(DataType.MultilineText)]
     public string Question { get; set; }
      
     public virtual List<Tag> Tags { get; set; }
   }

  
   public class Tag
   {
     public long TagID { get; set; }

     [Required]
     [Display(Name = "Tag Name")]
     [MaxLength(30)]
     public string TagName { get; set; }

     public bool IsActive { get; set; }

     public virtual List<Post> Posts { get; set; }
   }

The Dbcontext class is below, without it there is no code first approach.

  
    public class ForumContext : DbContext
    {
      public DbSet<Post> Posts { get; set; }
      public DbSet<Tag> Tags { get; set; }
    }
   

Specify the connection string here in the web.comfig file otherwise the database will be created in the express edition of the sql server.

 <add name="ForumContext" connectionString="Data Source=localhost;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=xxxx"providerName="System.Data.SqlClient" /> 
  

After that Initialize the test data for the model.

public class foruminitializer : DropCreateDatabaseIfModelChanges<ForumContext>
   {
        protected override void Seed(ForumContext context)
        {
            var Posts = new List<Post>
            {
             new Post { Question = "abc" , Title = "abctitle" ,  Tags = new List<Tag>()},
             new Post { Question = "abc1", Title = "abctitle1",  Tags = new List<Tag>()},
             new Post { Question = "abc2", Title = "abctitle2",  Tags = new List<Tag>()}
             
            };
            Posts.ForEach(s => context.Posts.Add(s));
            var Tags = new List<Tag>
            {
                new Tag { TagName = "tag1",  IsActive=true,  Posts= new List<Post>()},
                new Tag { TagName = "tag2",  IsActive=true,  Posts= new List<Post>()},
                new Tag { TagName = "tag3",  IsActive=true,  Posts= new List<Post>()}
            };

              Tags.ForEach(s => context.Tags.Add(s));
              Posts[0].Tags.Add(Tags[0]);
              Posts[1].Tags.Add(Tags[0]);
              Posts[1].Tags.Add(Tags[1]);
              Posts[1].Tags.Add(Tags[2]);
              Posts[2].Tags.Add(Tags[0]);
              Posts[2].Tags.Add(Tags[1]);
              context.SaveChanges();
        }
    }

Next step is to inform EF to execute the above foruminitializer class to populate the data when the application runs. This will all be done in global.asax.

   
    protected void Application_Start()
    {
       AreaRegistration.RegisterAllAreas();          
       Database.SetInitializer<ForumContext>(new foruminitializer());
       RegisterGlobalFilters(GlobalFilters.Filters);
       RegisterRoutes(RouteTable.Routes);           
    }

Final step.

Finally after running the application the EF will recognize that it is many to many relationship and apart from creating the two tables post and tag it will create a third table tagpost by joing the name of the two parent tables and both table's primary key going there as the foreign key in the table.

Note If you want to create extra fields in the tagpost table then this approach is not for you and for that there is another approach which I will not be covering here.

Querying the database.

Now after creating the database and filling it with data the next natural step is to query it. I have covered here the most common scenarios. Lot of other scenarious can be derived from them. You have to reference System.Data.Entity so that you can specify lambda expression in Include.

1) Get all posts with their tags.

     
    var allposts = context.Posts
                   .Include(p => p.Tags)
                   .ToList();

    foreach (var acc in allposts)
    {
       long postid = acc.PostID;           
       foreach (var accchild in acc.Tags)
       {
          long tagid = accchild.TagID;                        
       }                        
    } 
  

2) Get all tags of postID = 1.

 
    var posts =context.Posts.Where(p => p.PostID==1)
               .Include(p => p.Tags)
               .FirstOrDefault();                            
            
    foreach (var acc in posts.Tags)
    {
       long tagid = acc.TagID;                       
    }

3) Get all posts of tagID = 1.

   

    var tags =context.Tags.Where(p => p.TagID==2)
              .Include(p => p.Posts)
              .FirstOrDefault();                            
                  
    foreach (var acc in tags.Posts)
    {
       long postid = acc.PostID;                       
    }

4) Check to see if that tag exists in that particular post.

     
    var postsexists = from s in context.Posts 
                      from c in s.Tags
                      where s.PostID == 1 && c.TagName == "tag1"
                      select s;

    if (postsexists!=null)
    {

    }