Entity Framework CTP 4.0 – Many To Many Mappings

Posted: October 23, 2010 in .NET, ADO.NET Entity Framework, C#
Tags: ,

In my last post I had discussed about entity relationships,navigational properties and how we can fetch data from database into the entities.In this post we will examine the many to many relationships and how this can be modeled using POCO and the CTP 4.0 Code First Approach.We will consider three tables Person,Club and ClubMember where each Person can belong to multiple Clubs as shown below:

club

These tables are modeled using the following POCO entities:

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<Club> Memberships { get; set; }
}
public class Club
{
    public int ClubId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Person> Members { get; set; }
}  

We have modelled a bidirectional many to many relationship between Club and Person using the Members and Memberships properties respectively.We need to define these relationships in the Entity Configuration classes as shown below:

public class PersonConfig : EntityConfiguration<Person>
    {
        public PersonConfig()
        {
            MapSingleType(p => new { p.PersonId ,p.FirstName,p.LastName})
                    .ToTable("dbo.Person");
            HasKey(p => p.PersonId);
            Property(p => p.PersonId).IsIdentity();
            Property(p => p.FirstName)
                            .IsRequired();
            Property(p => p.LastName)
                            .IsRequired();
            HasMany(p => p.Memberships).
                                 WithMany(c=>c.Members).
                                        Map("ClubMember",
                                                ((p, c) =>
                                                        new
                                                        {
                                                            ClubId = c.ClubId,
                                                            PersonId = p.PersonId
                                                        }
                                                 )
                                             );
        }
    } 

In this context of many to many mappings the three methods HasMany, WithMany and Map deserves special discussion.

The method HasMany has the following signature:

public System.Data.Entity.ModelConfiguration.ManyNavigationPropertyConfiguration<TEntity,TRelated> HasMany<TRelated>(System.Linq.Expressions.Expression<Func<TEntity,ICollection<TRelated>>> propertyExpression)
  

Here, TEntity is the entity under consideration, Person in this case and TRelated is the entity to which relationship needs to be established.This method accepts a lambda expression which has TEntity as parameter and returns ICollection<TRelated> i.e p => p.Memberships where p is of type Person.

This method returns an instance of  System.Data.Entity.ModelConfiguration.ManyNavigationPropertyConfiguration<TEntity,TRelated> which exposes the method WithMany with the following signature:

public System.Data.Entity.ModelConfiguration.ManyNavigationPropertyConfiguration<TEntity,TRelated>.WithManyEndConfiguration WithMany(System.Linq.Expressions.Expression<Func<TRelated,ICollection<TEntity>>> propertyExpression) 

This method accepts a lambda expression which has TRelated as parameter and returns ICollection<TEntity> i.e c => c.Members where c is of type Club.This method returns an instance of System.Data.Entity.ModelConfiguration.ManyNavigationPropertyConfiguration<TEntity,TRelated>.WithManyEndConfiguration.  This class inherits from RelationshipPropertyConfiguration which exposes the method Map with the following signature:

public void Map(System.Data.Entity.ModelConfiguration.StoreTableName joinTable, System.Linq.Expressions.Expression<Func<TEntity,TRelated,object>> keyPropertyMap)  

This methods accepts the name of the join or mapping table(ClubMember in this case) and a lambda expression which accepts both TEntity,TRelated instances as parameters and returns an object representing the POCO entity for the mapping table.i.e

Map("ClubMember",
                                                ((p, c) =>
                                                        new
                                                        {
                                                            ClubId = c.ClubId,
                                                            PersonId = p.PersonId
                                                        }
                                                 )
                                             );

The relationship between key classes discussed above is shown below:

m2m

Coming back to our example, the similar configuration is done for the entity Club as shown below:

public class ClubConfig:EntityConfiguration<Club>
    {
        public ClubConfig()
        {
            MapSingleType(c => new { c.ClubId,c.Name})
                    .ToTable("dbo.Club");
            HasKey(c => c.ClubId);
            Property(c => c.ClubId).IsIdentity();
            Property(c => c.Name)
                            .IsRequired();
            HasMany(c => c.Members).
                                 WithMany(p=>p.Memberships).
                                        Map("ClubMember",
                                                ((c, p) => 

                                                        new
                                                        {
                                                            ClubId = c.ClubId,
                                                            PersonId = p.PersonId
                                                        } 

                                                 )
                                             );
        }
    } 

The next step is to define the DbContext as shown below:

public class ClubContext:DbContext
{
    public ClubContext(DbModel model, string name) : base(name, model) { }
    public DbSet<Person> Persons { get; set; }
    public DbSet<Club>   Clubs { get; set; }
} 

Finally we configure the model, initialize the context, insert records in database and display them as shown below:

static void Main(string[] args)
       {
           Database.DefaultConnectionFactory = new
               SqlConnectionFactory("Server=SANKARSAN;Database=test;Trusted_Connection=True;MultipleActiveResultSets=True"); 

           ModelBuilder builder = new ModelBuilder();
           builder.Configurations.Add(new PersonConfig());
           builder.Configurations.Add(new ClubConfig());
           DbModel model = builder.CreateModel(); 

           using (var context = new ClubContext(model, "test"))
           {
               //To use an existing database schema...
               Database.SetInitializer<ClubContext>(null); 

               Person p1 = new Person() {FirstName="Sankarsan",LastName ="Bose" };
               Person p2 = new Person() { FirstName = "Debjit", LastName = "Kar" }; 

               Club c1 = new Club() { Name = "Coding Club" };
               Club c2 = new Club() { Name = "Driving Club" }; 

               p1.Memberships = new List<Club>() { c1};
               p2.Memberships = new List<Club>() { c1,c2 }; 

               context.Persons.Add(p1);
               context.Persons.Add(p2);
               context.SaveChanges(); 

               foreach (var p in context.Persons)
               {
                   Console.WriteLine("{0} {1} is member of:" ,p.FirstName , p.LastName);
                   foreach (var c in p.Memberships)
                   {
                       Console.WriteLine("\t\t{0}",c.Name);
                   }
               }
               Console.Read();
           }
       } 


The program prints out:

Sankarsan Bose is member of:
                Coding Club
Debjit Kar is member of:
                Coding Club
                Driving Club

 

About these ads
Comments
  1. Entity Framework CTP 4.0 – Many To Many Mappings « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Martin Nyborg says:

    Thank for you example. I got so close but I was missing this

    ((p, c) =>
    ((c, p) =>

    I was doing this

    ((c, p) =>
    ((c, p) =>

    But I can help wounder if it’s necessary to write the mapping on both sides? I got it to work with only mapping on one side.

    Now I have another problem I can not delete an entity. Have you tried to delete a Person? I don’t think you can do it if you let the code create the database for you.

    I need to set the delete rule to cascade on the sql server to be able to delete.

    Do you know how to do this in code?

  3. agog says:

    Thanks again! I wondered by reading this post.

  4. agog says:

    If we want to bind a POCO object to a sql-server stored procedural, what configs needed? is it possible at all?
    If sp has parameters, what?

  5. Delaani says:

    is EntityConfiguration kind of obsolete?
    I cannot find it in CTP 5

  6. Joshua Ramirez says:

    Yes, use EntityTypeConfiguration

  7. Roland says:

    I don’t think the example is valid any more. I can not find the method profile for Map that you used. I get a “No overload for method ‘Map’ takes 2 arguments” message.

    HasMany(p => p.TaxCreditPropertyExtensions)
    .WithMany(c => c.ReportComments)
    .Map(“TaxCreditPropertyWatchlistXref”, ((p, c) => new { TaxCreditPropertyExtensionId = p.Id, ReportCommentsId = c.Id }));

  8. Rodd says:

    Thanks, this was a nice example of many to many mapping. I was wondering what you would do, though, if you tracked a membership date … the date when a person joined a club. It seems like the date would naturally go into the join table but it also seems as though the date would not be available as the join table isn’t actually mapped to an object.

    Any ideas on how you would handle that?

  9. Subash Anand says:

    Author Can you please explain me CRUD operation for the association table with the extra fields?

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