asp.net mvc 3 EF query from one to many relationship

I am new to asp.net mvc and i have problems that i think i would solve them very easy with asp.net web forms. However the project have to be in mvc, so here is the problem.

I got X tables

table1 Users int user_ID string username

table2 Friends int friendshipID int user_ID int friend_ID

In table 2, user_ID represents the current user that is logged in. friend_ID represents ids from his friends. Its one to many relationship. Now what i want to do, is, in user/details/ID view, show all friends of that user. The query that i want to make is: first select the friend_IDs from table2 where user_ID=id(from querystring), then select every username from table1 where user_ID = friend_ID.

I think this is really easy in SQL, but dont know how to do it with the mvc syntax.

The controller:

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

    public ViewResult Details(int id)
    {
        User user = db.Users.Find(id);
        return View(user);
    }

The view:

@model Social2.Models.User

<div class="display-label">Friends</div>
<div class="display-field">

    @foreach (var friend in @Model.Friends)
    {
        @friend.User.username;
    }

</div>

The view returns wrong results.

Models

public partial class User
{
    public User()
    {
        this.Albums = new HashSet<Album>();
        this.Friends = new HashSet<Friend>();
        this.Messages = new HashSet<Message>();
        this.Posts = new HashSet<Post>();
        this.Groups = new HashSet<Group>();
    }

    public int user_ID { get; set; }
    public System.Guid user_UniqueID { get; set; }
    public string username { get; set; }

    public virtual ICollection<Album> Albums { get; set; }
    public virtual aspnet_Users aspnet_Users { get; set; }
    public virtual ICollection<Friend> Friends { get; set; }
    public virtual ICollection<Message> Messages { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
    public virtual ICollection<Group> Groups { get; set; }
}

and from friends table

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; }
}

also the context

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

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

    public DbSet<Album> Albums { get; set; }
    public DbSet<aspnet_Users> aspnet_Users { get; set; }
    public DbSet<Friend> Friends { get; set; }
    public DbSet<Group> Groups { get; set; }
    public DbSet<Message> Messages { get; set; }
    public DbSet<Picture> Pictures { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<sysdiagram> sysdiagrams { get; set; }
    public DbSet<User> Users { get; set; }
}

Answers


As the Friends list property is virtual it will not be included with your query. Try using below query to include the Friends.

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);
    }

View:

<table>
@foreach (var friend in @Model.Friends)
    {

        <tr>

            @Html.DisplayFor(modelItem => friend.User.username)
        </tr>
    } 
</table>

Your model classes doesn't appear to be following the convention for the Entity keys. The fields "user_ID" and "friendship_ID" should be UserId and FriendId. Or if you want to key them like that annotate them with [key] attribute.


Make the ViewModel class of your own. Retrieve the data from database and build the model class object. Pass this model class to view i.e. create your view based on this model class.


Need Your Help

Haskell Most Efficient Mutable Data Structure

arrays performance haskell data-structures functional-programming

I am writing a game program in Haskell that currently has a data type like this

How to stop playback using MediaProtocolMessageStream

google-cast chromecast

I'm using MediaProtocolMessageStream and I'm able to pause (stop method call) and resume just fine however I do not know how to stop playback. Essentially I want the user to be able to stop the video