Entity Framework database-first - table without primary key

Question

I have database first EF and I and create the model classes manually for Entity Framework - I didn’t generate any models.

I get this error :

ModelValidationException was unhandled HResult=-2146233088 Message=One or more validation errors were detected during model generation: UserToGroup EntityType 'UserToGroup' has no key defined. Define the key for this EntityType. UserToGroup EntityType EntitySet 'UserToGroup' is based on type 'UserToGroup' that has no keys defined.

Model classes:

public class Group
{
   [key]
   public int ID { get; set; }
   public string Name { get; set; }
   public int ClubId { get; set; }
}

public class User
{
   [key]
   public int ID { get; set; }
   public string Name { get; set; }
   public string email { get; set; }
}

public class UserToGroup
{
   public int GroupID { get; set; }
   public int UserID { get; set; }
}

In database script table UserToGroup:

CREATE TABLE [dbo].[usertogroup] 
( 
     [groupid] [INT] NOT NULL, 
     [userid]  [INT] NOT NULL, 
     CONSTRAINT [IX_UserToGroup] 
         UNIQUE NONCLUSTERED ([groupid] ASC, [userid] ASC)
) 
ON [PRIMARY] 
GO

ALTER TABLE [dbo].[usertogroup] WITH CHECK 
    ADD CONSTRAINT [FK_UserToGroup_Group] 
        FOREIGN KEY([groupid]) REFERENCES [dbo].[group] ([id]) 
GO    

ALTER TABLE [dbo].[usertogroup] CHECK CONSTRAINT [FK_UserToGroup_Group] 
GO

ALTER TABLE [dbo].[usertogroup] WITH CHECK 
    ADD CONSTRAINT [FK_UserToGroup_User] 
        FOREIGN KEY([userid]) REFERENCES [dbo].[user] ([id]) 
GO

ALTER TABLE [dbo].[usertogroup] CHECK CONSTRAINT [FK_UserToGroup_User] 
GO

My class context :

public class myContext : DbContextBase
{
   public const string ConnectionStringName = @"xxxxxx";

   public myContext() : base(ConnectionStringName)
   { }

   public DbSet<Group> Group { get; set; }
   public DbSet<User> User { get; set; }
   public DbSet<User> UserToGroup { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

      base.OnModelCreating(modelBuilder);
   }
}

Thanks in advance for any help

Answer 1

EF needs a key definition in order to track entities, even if the table does not have a PK. In your case you have a couple options if the joining table only contains the 2 FKs.

Option 1: Many-to-many without joining entity. To accomplish this you map a collection of Groups within user (and optionally users within group) then set up a HasMany.WithMany relationship. You tell EF about the joining table/columns and you can then access the collections without a joining entity. I.e.

public class User
{
  // User columns...

  public ICollection<Group> Groups { get; set; } = new List<Group>();
}

public class UserConfiguration : EntityTypeConfiguration<User>
{
  public UserConfiguration()
  {
    ToTable("user");
    HasKey(x => x.UserId)
      .Property(x => x.UserId)
      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

   HasMany(x => x/Groups)
     .WithMany() // if Group has no Users collection, otherwise .WithMany(x => x.Users)
     .Map(x => 
     {
       x.ToTable("usertogroup");
       x.MapLeftKey("userId");
       x.MapRightKey("groupId");
     });
 }
}

To get groups for a user: user.Groups;

Option 2: Map a joining entity (usertogroup). This is needed if the joining table has additional columns, such as modified tracking columns. In this case you map a key on the table for the combination of the two FKs.

public class User
{
  // User columns...

  public virtual ICollection<UserToGroup> UserGroups { get; set; } = new List<UserToGroup>();
}
public class UserToGroup
{
  public int UserId { get; set; }
  public int GroupId { get; set; }

  public virtual User User { get; set; }
  public virtual Group Group { get; set; }
}

public class UserConfiguration : EntityTypeConfiguration<User>
{
  public UserConfiguration()
  {
    ToTable("user");
    HasKey(x => x.UserId)
      .Property(x => x.UserId)
      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

   HasMany(x => x.UserGroups)
     .WithRequired(x => x.User); 
}

public class UserGroupConfiguration : EntityTypeConfiguration<UserGroup>
{
  public UserGroupConfiguration()
  {
    ToTable("usertogroup");
    HasKey(x => new { x.UserId, x.GroupId });
  }
}

This tells EF how to map the user to group association. There may be times that you want to map a table that has no PK defined. EF requires a Key to track entities, so in these cases you use the same technique to designate all of the required columns that allow it to differentiate one row from any, and all others. If you create a key with a set of columns, they set must be guaranteed to be unique in the table, otherwise you will get unexpected results. (such as reading or writing the wrong row in the table.)

To get the groups for a user: user.UserGroups.Select(x => x.Group);