Alex Rivera | Logout

Entity Framework 4.1 Code First Self-Referencing One-to-Many and Many-to-Many Associations

Asked 2011-06-17T09:04:11.083
21

I have a User that can have collection of users he likes...

Another user can have collection of users he likes....

If User A likes User B and if User B likes User A, then they get to hang out. I need to send each other their contact info. How do we represent such a model in Entity Framework Code First?

public class User
{
    public int UserId { get; set; }

    public int? UserLikeId { get; set; }
    public virtual UserLike UserLike { get; set; }
}

public class UserLike
{
    public int UserLikeId { get; set; }

    public int UserId { get; set; }
    public virtual User User { get; set; }

    public virtual ICollection<User> LikeUsers { get; set; }
}

Is this model correct? I can't get this to work.

I've tried another way but that doesn't work too...

I tried to add collection of user to user table.

For ex : 

public virtual ICollection<User> userlike { get; set; }

public class User
{
    public int UserId { get; set; }
    public virtual ICollection<UserLike> UserLikes { get; set; }
}

public class UserLike
{
    public int UserLikeId { get; set; }

    public int UserId { get; set; }
    public virtual User User { get; set; }

    public int LikeUserId { get; set; }
    public virtual User LikeUser { get; set; }
}

I get this error when I try to add user and who they like:

Conflicting changes to the role 'UserLike_LikeUser_Target' of the relationship 'UserLike_LikeUser' have been detected.

What's the best way to represent such a model?

entity-framework ef-code-first entity-framework-4.1 self-join self-reference
Edit
Report

1 Answer

27

You don't really need a separate entity to describe the relationship, the object model below will do the trick:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }

    public int? ThisUserLikesId { get; set; }
    public virtual User ThisUserLikes { get; set; }
    public virtual ICollection<User> LikeThisUser { get; set; }
}

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
                    .HasOptional(u => u.ThisUserLikes)
                    .WithMany(u => u.LikeThisUser)
                    .HasForeignKey(u => u.ThisUserLikesId);
    }
}

Now let's say you have a UserId in your hand and want to find the other User who likes this user which this user also like him:

using (var context = new Context())
{
    // For a given user id = 1
    var friends = (from u in context.Users
                   where u.UserId == 1
                   from v in u.LikeThisUser
                   where v.UserId == u.ThisUserLikesId
                   select new 
                   { 
                       OurUser = u, 
                       HerFriend = v 
                   })
                   .SingleOrDefault();

    ExchangeContactInfo(friends.OurUser, friends.HerFriend);
}


Update 1:

A self referencing many-to-many association will be mapped to database using a join table which require a different object model and fluent API altogether:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<User> ThisUserLikes { get; set; }
    public virtual ICollection<User> UsersLikeThisUser { get; set; }
}

public class Context : DbContext
{
    public DbSet<User> Us
answered 2011-06-17T14:16:02.890

Your Answer