Entity Framework query returns one record, but its supposed to return more

Question

As title say, i got problem with a query in Asp.net mvc 3 EF. I got 2 tables with 1 to many relationship.

table1 Users int user_ID string username

table2 Friends int friendshipID int user_ID int friend_ID

The controller:

// // GET: /User/Details/5

public ViewResult Details(int id)
    {
        User user = db.Users.Include("Friends").FirstOrDefault(u => u.user_ID == id);
        //Also for each friend get the User:
        foreach (var friend in user.Friends.ToList())
        {
            friend.User = db.Users.Find(friend.friend_ID);
        }
        return View(user);
    }

The view "details":

@model Social2.Models.User

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>
    <div class="display-field">
        @foreach (var friend in @Model.Friends)
    {
           @friend.User.username;
    }   
    </div>

Context:

public partial class ASPNETDBEntities : DbContext
{
    public ASPNETDBEntities()
        : base("name=ASPNETDBEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public DbSet<aspnet_Users> aspnet_Users { get; set; }
    public DbSet<Friend> Friends { get; set; }
    public DbSet<User> Users { get; set; }
}

user model:

public partial class User
{
    public User()
    {
        this.Friends = new HashSet<Friend>();
    }
    [Key]
    public int user_ID { get; set; }
    public System.Guid user_UniqueID { get; set; }
    public string username { get; set; }

    public virtual aspnet_Users aspnet_Users { get; set; }
    public virtual ICollection<Friend> Friends { get; set; }
}

friend model

public partial class Friend
{
    public int friendship_ID { get; set; }
    public int user_fr_ID { get; set; }
    public int friend_ID { get; set; }

    public virtual User User { get; set; }
}

The problem is, when i go to ~/user/details/1, the view show only one(the last one) friend.For every user it shows their last friend. How to show them all ?

Answer 1

Your database must have two relationships defined like so:

If you create an Entity Model from this schema you also get two one-to-many relationships. And when you apply the DBContext T4 template to this model you should get POCO classes like so:

public partial class Users
{
    public Users()
    {
        this.Friends = new HashSet<Friends>();
        this.Friends1 = new HashSet<Friends>();
    }

    public int user_ID { get; set; }
    public string username { get; set; }

    public virtual ICollection<Friends> Friends { get; set; }
    public virtual ICollection<Friends> Friends1 { get; set; }
}

public partial class Friends
{
    public int friendship_ID { get; set; }
    public int user_fr_ID { get; set; }
    public int friend_ID { get; set; }

    public virtual Users Users { get; set; }
    public virtual Users Users1 { get; set; }
}

Users.Friends and Friends.Users form a pair for the first relationship and Users.Friends1 and Friends.Users1 are a pair for the second relationship. (You can rename the navigation properties in the model designer to make the names more meaningful.) Your query would look like this then (you can include a "second level" and don't need the loop as you did in your example):

public ViewResult Details(int id)
{
    // important to use User1 in Include, not User
    User user = db.Users.Include("Friends.User1")
        .FirstOrDefault(u => u.user_ID == id);
    return View(user);
}

With DbContext you can also use the strongly typed version of Include:

Include(u => u.Friends.Select(f => f.User1))