One to Many with a join table and an optional relationship in Entity Framework 4.1 Fluent API

Question

Again with a legacy database that cannot be changed and using Entity Framework 4.1 with the Fluent API to read data only.

public class Client
{
  [Key]
  public int ClientID { get; set; }
  public string Name { get; set ;}

  public virtual ICollection<Phone> Phones { get; set; }
}

public class Phone
{
  [Key]
  public int PhoneID { get; set; }
  public string Number { get; set; }

  public virtual Client Client { get; set; }
}

public class ClientPhone
{
  [Key]
  [Column(Order=0)]
  public int ClientID { get; set; }

  [Key]
  [Column(Order=1)]
  public int PhoneID { get; set; }
}

I want the Client to have many Phones, but the Phones should have only an optional Client. Note: Phones should have only 0|1 Client. I do NOT want a many to many. So I've tried the following:

modelBuilder.Entity<Client>()
  .HasMany(c => c.Phones)
  .WithOptional(p => p.Client)
  .Map(m =>
    {
      m.MapKey("ClientID");
      m.ToTable("ClientPhone");
    });

modelBuilder.Entity<Phone>()
  .HasOptional(p => p.Client)
  .WithMany(c => c.Phones)
  .Map(m =>
    {
      m.MapKey("PhoneID");
      m.ToTable("ClientPhone");
    });

I've tried a couple of permutations usually getting an error about "Each property name in a type must be unique."

Thanks for the help.

EDIT WITH ANSWER

Here are the modifications I made to the entity classes. It is possible to navigate from one Client to many Phones and from one Phone to one Client, but you must go through the ClientPhone join table.

[Table("Client")]
public class Client
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int ClientID { get; set; }

  public string Name { get; set ;}

  public virtual ICollection<Phone> Phones { get; set; } // Client has * Phones
}

[Table("Phone")]
public class Phone
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int PhoneID { get; set; }

  public string Number { get; set; }

  public virtual Client Client { get; set; } // Phone has 0|1 Client
}

[Table("ClientPhone")]
public class ClientPhone
{
  // Removed the Key attribute
  public int ClientID { get; set; }

  [Key] // Left the Key on the 0|1 side
  [ForeignKey("Phone")]
  public int PhoneID { get; set; }

  public virtual Client Client { get; set; } // One Client
  public virtual Phone Phone { get; set; } // One Phone
}

Answer 1

you don't need to specify the relationship between phone and client two times here. Just use only one like this.

modelBuilder.Entity<Client>().HasMany(c => c.Phones).WithMany(i => i.Clients)
                .Map(t => t.MapLeftKey("PhoneID")
                    .MapRightKey("ClientID")
                    .ToTable("ClientPhone"));

you do not need to have models to map relationship tables like "ClientPhone" because EF will take care of it .If you have a legacy database you can customize your mappings as in my answer. And as @Ladislav Mrnka said for one to many rel.ationship in database level you do not want to keep a separate table for the relationship. For that ,

public class Client
{
  [Key]
  public int ClientID { get; set; }
  public string Name { get; set ;}

  public virtual ICollection<Phone> Phones { get; set; }
}

public class Phone
{
  [Key]
  public int PhoneID { get; set; }
  public string Number { get; set; }

  public virtual Client Client { get; set; }
}

this will be enough with EF. It will create two tables with clientId column in Phone table to keep the relationship.

The problem is you have separate table for relationship in your legacy database.I think legacy database is defined for many-to-many relationship.Think twice your domain logic again.

If you want to keep one-to-many relationship what I think is you can define in domain level many-to-many relationship (phone have many clients and client have many phones in model clases) and in you business layer add validations to keep only one client for a Phone.

Answer 2

One to many relation doesn't have join (junction) table. If your database uses junction table to connect Client to Phone you must use many-to-many relation (Phone can have many clients) as @Jayantha described.