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

Question

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

Please add the correct tag to your question for the database access technology that you are using. It is absolutely unclear what `db.Users` is. I guess Entity Framework but please be precise. Also show your model classes. Your problem is more related to the database access technology that you are using and has nothing to do with ASP.NET MVC. — Darin Dimitrov, Jun 12 '12 at 07:02
From the pattern and the Find method looks like you are using EntityFramework DbContext to access the database. Kindly specify. If so, try query like `db.Users.Include("Friends").FirstOrDefault(u => u.UserId == id);` Also you have not mentioned your model class hence the query has some assumptions. — Abhijit-K, Jun 12 '12 at 07:07

score 0 · Answer 1 · answered Jun 12 '12 at 07:07

0

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.

answered Jun 12 '12 at 07:07

Sagar

454
10
22

Create strongly typed view of the User class. – Sagar Jun 12 '12 at 07:26

Abhijit-K · Accepted Answer · 2012-06-12T08:15:33.953

0

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.

edited Jun 12 '12 at 08:15

answered Jun 12 '12 at 07:09

Abhijit-K

3,569
1
23
31

Ok and how to display them in the view ? And yes its EF, i edited and added models – Milanix Jun 12 '12 at 07:15
I have edited the post, haven't checked for typo. I would prefer to load the user details in the view and then get the friends list through ajax with preferably json data after the page is loaded. This will give user feeling that the site is fast. OR load friends through ajax when the browser user clicks some button like show friends. – Abhijit-K Jun 12 '12 at 07:36
well i`ve done exactly like u said. First user got 2 friends. When i go to details, it displays 2 times his name instead of friend names – Milanix Jun 12 '12 at 07:43
I believe It should be `friend.User = db.Users.Find(friend.friend_ID );` – Abhijit-K Jun 12 '12 at 07:50
Collection was modified; enumeration operation may not execute. got this error, when i change to :friend.User = db.Users.Find(friend.friend_ID ); – Milanix Jun 12 '12 at 07:55
Use ToList() for the friends collection. `foreach (var friend in user.Friends.ToList())` – Abhijit-K Jun 12 '12 at 08:04
Can anyone tell me any online tutorial where i can find more about queries like this ? – Milanix Jun 12 '12 at 08:06
Hmm now displays only one (the last in table) friend – Milanix Jun 12 '12 at 08:22
Can u help me with the last comment ? The view displays only one friend (the last one in every friendship). I got the same code u write above. – Milanix Jun 12 '12 at 11:34
I did not compile the code. Can you debug and find out the User object? How many friends are in the Friends collection? If only one is returned then you may have not configured the relationship between User and Friends and EF is not able to get that. Actually I would not have manually managed the Friend Entity to maintain many to many relationship. Here is a example of many many relationship for the same model http://stackoverflow.com/questions/10421351/many-to-many-relationship-between-entities-of-same-type-in-mvc3 – Abhijit-K Jun 12 '12 at 13:18

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

2 Answers2