你不需要一个单独的实体来描述关系,下面的对象模型就可以达到效果:
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);
}
}
现在假设你手头有一个UserId,想要找到喜欢这个用户的其他用户,并且这些用户也被该用户所喜欢:
using (var context = new Context())
{
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);
}
更新 1:
一个自引用的多对多关联将使用联接表映射到数据库中,这需要完全不同的对象模型和流畅的 API:
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> Users { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<User>()
.HasMany(u => u.ThisUserLikes)
.WithMany(u => u.UsersLikeThisUser)
.Map(c =>
{
c.MapLeftKey("UserId");
c.MapRightKey("OtherUserId");
c.ToTable("UserLikes");
});
}
}
更新2:
如我在这篇文章中所解释的,多对多关联不能有有效载荷(例如EventId),如果确实需要,则需要将其拆分为两个一对多关联到一个介入类,并且我可以看到您已经正确创建了该类(UserLike)来表示附加到自身引用的多对多关联的额外信息,但是从该中间类到User的关联不正确,因为我们需要从UserLike到User定义恰好2个多对一关联,就像我在下面的对象模型中展示的那样:
public class User
{
public int UserId { get; set; }
public string Email { get; set; }
public virtual ICollection ThisUserLikes { get; set; }
public virtual ICollection UsersLikeThisUser { get; set; }
}
public class UserLike
{
public int UserLikeId { get; set; }
public int LikerId { get; set; }
public int LikeeId { get; set; }
public int EventId { get; set; }
public User Liker { get; set; }
public User Likee { get; set; }
public virtual Event Event { get; set; }
}
public class Event
{
public int EventId { get; set; }
public string Name { get; set; }
}
public class Context : DbContext
{
public DbSet Users { get; set; }
public DbSet Events { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(u => u.ThisUserLikes)
.WithRequired(ul => ul.Liker)
.HasForeignKey(ul => ul.LikerId);
modelBuilder.Entity()
.HasMany(u => u.UsersLikeThisUser)
.WithRequired(ul => ul.Likee)
.HasForeignKey(ul => ul.LikeeId)
.WillCascadeOnDelete(false);
}
}
现在,您可以使用以下 LINQ 查询来检索彼此喜欢的所有用户:
using (var context = new Context())
{
var friends = (from u1 in context.Users
from likers in u1.UsersLikeThisUser
from u2 in u1.ThisUserLikes
where u2.LikeeId == likers.LikerId
select new
{
OurUser = u1.UserId,
HerFriend = u2.LikeeId
})
.ToList();
}
希望这可以帮助到你。