none
Many to many self referencing table with payload RRS feed

  • Question

  • I'm developing an Entity Framework Code First 6.1.1. library with .NET Framework 4.5.1 and C#.

    I need to create a join table with payload. In this SO question I have found an example about how to do it. And in this MSDN article there are another example.

    The join table is about Users and Friends. I need another column, `Status` to indicate friendship status.

    These are my classes:
    public class User
        {
            public int UserId { get; set; }
            public string UserName { get; set; }
        
            public virtual ICollection<UserFriend> Friends { get; set; }
        }
    UserFriend:
        public class UserFriend
        {
            public long UserId { get; set; }
            public long FriendId { get; set; }
        
            public byte Status { get; set; }
        
            public virtual User User { get; set; }
            public virtual User Friend { get; set; }
        }
    UserFriendConfiguration:
        class UserFriendConfiguration : EntityTypeConfiguration<UserFriend>
        {
            public UserFriendConfiguration()
            {
                ToTable("UserFriend");
        
                HasKey(uf => new {uf.UserId, uf.FriendId});
        
                HasRequired(uf => uf.User).
                    WithMany(u => u.Friends).
                    HasForeignKey(uf => uf.UserId);
        
                HasRequired(uf => uf.Friend).
                    WithMany(u => u.Friends).
                    HasForeignKey(uf => uf.FriendId);
            }
        }
    But I get this error:
        "ExceptionMessage": "Schema specified is not valid. Errors:
           The relationship 'MyProject.Data.SqlServer.Concrete.UserFriend_User'
           was not loaded because the type 'MyProject.Data.SqlServer.Concrete.User'
           is not available.",
        
        "ExceptionType": "System.Data.Entity.Core.MetadataException".
    But UserFriend class and `User` class are on MyProject.Data.Models namespace. DbContext is on MyProject.Data.SqlServer.Concrete namespace.

    Do you know how can I solve this problem? Do I need two navigation properties in User class?
    Friday, September 19, 2014 10:55 AM

Answers

  • Hello VansFannel,

    >>Do you know how can I solve this problem? Do I need two navigation properties in User class?

    I am afraid that you need to also add the additional navigation property. With your description, I made an example:

    class Sample20140922 : DbContext
    
        {
    
            public DbSet<User> Users { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
                modelBuilder.Entity<User>().Property(u => u.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    
                modelBuilder.Entity<UserFriend>().HasKey(u => new { u.UserId, u.FriendId });
    
                modelBuilder.Entity<UserFriend>().Property(u => u.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    
                modelBuilder.Entity<UserFriend>().Property(u => u.FriendId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    
                modelBuilder.Entity<User>().HasMany(u => u.Friends).WithRequired(f => f.User).HasForeignKey(f => f.UserId).WillCascadeOnDelete(false);
    
                modelBuilder.Entity<User>().HasMany(u => u.Users).WithRequired(f => f.Friend).HasForeignKey(f => f.FriendId).WillCascadeOnDelete(false);
    
            }
    
        }
    
    
        public class User
    
        {
    
            public int UserId { get; set; }
    
            public string UserName { get; set; }
    
            public virtual ICollection<UserFriend> Users { get; set; }
    
            public virtual ICollection<UserFriend> Friends { get; set; }
    
        }
    
    
        public class UserFriend
    
        {
    
            public int UserId { get; set; }
    
            public int FriendId { get; set; }
    
    
            public byte Status { get; set; }
    
    
            public virtual User User { get; set; }
    
            public virtual User Friend { get; set; }
    
        }
    

    You could try it. And since the table is self-referenced, we need to set the WillCascadeOnDelete to false.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 22, 2014 2:27 AM
    Moderator