Entity Framework 4.0 CTP5 Many to Many relation ship with a help table?

Question

i have a question about EF4 again :) sorry i can't find any thing on the net.

The question is i am using CTP5 Code Only to map (many to many), how do i do it???

But i am doing the old fashion way, i mean i have three tables:

1. Users
2. Companies.
3. UsersToCompanies -> (UserId, CompanyId)

How do i map this, it will be really appreciated if you would show me the code example to it, from POCO's to Mapping.

---

This is the error i receive...

This is my entities
public class User 
{
   public int UserId { get; set; }
   public int? UserRoleId { get; set; }
   public string UserName { get; set; }
   public string UserPassword { get; set; }
   public DateTime InsertDate { get; set; }
   public virtual UserRole UserRole { get; set; }

   //this is my many to many prop
   public virtual ICollection<Company> Companies { get; set; }
}

public class Company
{
   public int CompanyId { get; set; }
   public string CompanyNameHe { get; set; }
   public string CompanyNameEn { get; set; }
   public string CompanyParent { get; set; }
   public DateTime InsertDate { get; set; }

   //this is my many to many prop
   public virtual ICollection<User> Users { get; set; }
}

/*relationship map*/
public class UsersCompanies
{
   public int Id { get; set; } 
   public int UserId { get; set; } 
   public int CompanyId { get; set; } 
}
    //mapping
 public class CompanyMap :  BaseConfig<Company>
 {
    public CompanyMap()
    {
        /*Identity*/
        HasKey(c => c.CompanyId);
        Property(c => c.CompanyId).HasDatabaseGenerationOption(DatabaseGenerationOption.Identity).HasColumnName("COMPANY_ID");

        /*Have default values*/
        Property(c => c.InsertDate).HasDatabaseGenerationOption(DatabaseGenerationOption.Computed).HasColumnName("INSERT_DATE");

        /*simple scalars*/
        Property(c => c.CompanyNameHe).HasMaxLength(32).IsRequired().HasColumnName("COMPANY_NAME_HE");
        Property(c => c.CompanyNameEn).HasMaxLength(32).IsRequired().HasColumnName("COMPANY_NAME_EN");
        Property(c => c.CompanyParent).HasMaxLength(32).IsRequired().HasColumnName("COMPANY_PARENT");
        ToTable("CMS_COMPANY", "GMATEST");
    }
 }


public class UserMap : BaseConfig<User>
{
   public UserMap()
   {
       /*Identity*/
       HasKey(c => c.UserId);
       Property(c => c.UserId).HasDatabaseGenerationOption(DatabaseGenerationOption.Identity).HasColumnName("USER_ID");

       /*Have default values*/
       Property(c => c.InsertDate).HasDatabaseGenerationOption(DatabaseGenerationOption.Computed).HasColumnName("INSERT_DATE");

       /*simple scalars*/
       Property(c => c.UserName).HasMaxLength(25).IsRequired().HasColumnName("USER_NAME");
       Property(c => c.UserPassword).HasMaxLength(25).IsRequired().HasColumnName("USER_PASSWORD");
       Property(c => c.UserRoleId).IsRequired().HasColumnName("USER_ROLE_ID");

       /*relationship*/
       HasRequired(u => u.UserRole).WithMany().HasForeignKey(t => t.UserRoleId);


       HasMany(p => p.Companies).WithMany(c => c.Users).Map(mc =>
       {
           mc.ToTable("UsersCompanies");
           mc.MapLeftKey(p => p.UserId, "CompanyId");
           mc.MapRightKey(c => c.CompanyId, "UserId");
       });
       ToTable("CMS_USERS", "GMATEST");
   }
}

public class UsersCompaniesMap : BaseConfig<UsersCompanies>
{
   public UsersCompaniesMap()
   {
       /*Identity*/
       HasKey(k => k.Id);
       Property(c => c.Id).HasDatabaseGenerationOption(DatabaseGenerationOption.Identity).HasColumnName("ID");

       Property(c => c.UserId).IsRequired().HasColumnName("USER_ID");
       Property(c => c.CompanyId).IsRequired().HasColumnName("COMPANY_ID");

       ToTable("CMS_USERS_TO_COMPANIES", "GMATEST");
   }
}

---

Here's the error I get:

'((new System.Linq.SystemCore_EnumerableDebugView(ctx.FindAll())).Items[0]).Companies' threw an exception of type 'System.Data.EntityCommandExecutionException'

Inner exception: ORA-00942: table or view does not exist

Answer 1

You do not really need 3 tables to perform the mapping. You could simply do the following:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Company> Companies { get; set; }
}

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

And this should do it. As long as you have a virtual ICollection referencing the other entity in both POCO's, CTP5's conventions should take care of everything for you. On the other hand, if you prefer doing it manually using the Fluent API, then check this blog by the ADO.NET team (scroll a little down to the many-to-many relationship section)

Answer 2

The answer for my problem, i have to say many thanks to you all, but Andrey from Devart, have given me the solution, it's simple.

First off all i have to have, three tables Companies, Users, UsersCompanies/

Second off all i have to map the tables to original names of tables example: my users table called in Db like that: CMS_USERS i have to map it with original name. this is the example that i use and it's really works.

HasMany(c => c.Companies)
          .WithMany(u => u.Users)
          .Map(mc =>
          {
              mc.ToTable("CMS_USERS_TO_COMPANIES", "GMATEST");
              mc.MapLeftKey(c => c.UserId, "USER_ID");
              mc.MapRightKey(u => u.CompanyId, "COMPANY_ID");
          });

Thank you all.