Asp.net-mvc – How to Join two table in Code first approach

asp.net-mvcc#-4.0ef-code-firstentity-framework

i have two table, User and Friendship. i want to join this two tables so that the column TheirFriends which have UserID of all friends will have some attribute that is in User. that is i want to get each friend with all their attribute.
Output will look like this
Theirfriends firstname LastName UserPicture

i need this linq and lambda expression.

User:

public class User
{

    public String UserID { get; set; }
    public string FirstName { get; set; }
    public String LastName { get; set; }

    public string Description { get; set; }
    public string UserPicture { get; set; }
    public string Gender { get; set; }
    public String Interest { get; set; }
    public DateTime DateOfBirth { get; set; }
    public String Email { get; set; }

FriendShip

 public class FriendShip
{

    public int FriendShipID { get; set; }
    public string TheirFriends { get; set; }

    public String UserID { get; set; }
}

Best Answer

You can use the following:

var usersWithFriends = context.Users
    .GroupJoin(context.FriendShips, u => u.UserID, f => f.UserID, (u, f) => new
    {
        User = u,
        Friends = f.Join(context.Users, f1 => f1.TheirFriends, u1 => u1.UserID,
            (f1, u1) => u1)
    })
    .ToList();

The result is a collection of anonymous objects and each element has a User property with a user and a Friends collection which contains all users which are friends of the given user. Because of the GroupJoin the result also contains users without friends (Friends collection is empty in this case). Roughly like so:

Element[0]:
    User -> "Jim"
    Friends -> "John" + "Diana"
Element[1]:
    User -> "John"
    Friends -> empty
Element[2]:
    User -> "Diana"
    Friends -> "John"

But this is not the Entity-Framework way to work with such a model. You should actually have a navigation property in your User class:

public class User
{
    [Key]
    public string UserID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    //...

    public ICollection<User> Friends  { get; set; }
}

And a many-to-many relationship between the users:

modelBuilder.Entity<User>()
    .HasMany(u => u.Friends)
    .WithMany()
    .Map(x =>
    {
        x.MapLeftKey("UserID");
        x.MapRightKey("TheirFriends");
        x.ToTable("FriendShips");
    });

FriendShips would be just a join table in the database (without a FriendShipID column, UserID and TheirFriends build a composite key instead) and not an entity in your model. User is your only entity. Then you could achieve the same result a lot easier:

var usersWithFriends = context.Users.Include(u => u.Friends).ToList();

Not only less lines of code but also a lot easier to read and understand. EF will care about the complex joining and grouping in the database when the query is translated into SQL.

Edit

You can add relationships (= rows in the link table FriendShips) by adding an existing user to the Friends collection:

using (var context = new MyContext())
{
    var user = context.Users.Single(u => u.UserID == "John");
    var friend = context.Users.Single(u => u.UserID == "Diana");

    user.Friends = new HashSet<User>();
    user.Friends.Add(friend);

    context.SaveChanges(); // will write a new row into the join table
}

Likewise you can remove a relationship (remove row from join table):

using (var context = new MyContext())
{
    var user = context.Users.Include(u => u.Friends)
        .Single(u => u.UserID == "John");
    var friend = user.Friends.Single(u => u.UserID == "Diana");

    user.Friends.Remove(friend);

    context.SaveChanges(); // will delete a row from the join table
}

It's also possible without querying the users from the database since you have the primary key values:

using (var context = new MyContext())
{
    var user = new User { UserID = "John" };
    var friend = new User { UserID = "Diana" };

    context.Users.Attach(user);
    context.Users.Attach(friend);

    user.Friends = new HashSet<User>();
    user.Friends.Add(friend);

    context.SaveChanges(); // will write a new row into the join table
}

using (var context = new MyContext())
{
    var user = new User { UserID = "John" };
    var friend = new User { UserID = "Diana" };

    user.Friends = new HashSet<User>();
    user.Friends.Add(friend);

    context.Users.Attach(user);
    // attaching friend is not necessary, it is already attached with user

    user.Friends.Remove(friend);

    context.SaveChanges(); // will delete row from the join table
}
Related Topic