Loading multiple related table data into MVC view. (Linq to SQL)

Question

I seem to be having a problem passing related data to my view. I'm creating a simple blog, which has a database architecture of three tables. These are Users, Posts and Comments. For my user profile page I wish to pass all the data from these tables into the user profile view. I used Linq to SQl to create my database model. Here is my attempt at passing in the data using Linq:

public ActionResult NewProfile()
    {
        var users = from u in db.UserModels
                    join p in db.PostModels on u.UserId equals p.UserId
                    where u.UserId == WebSecurity.CurrentUserId
                    select new Profile { UserModel = u, PostModel = p };

        return View(users);
    }

This is the code in the receiving view:

@model IEnumerable<MvcBlog.Models.Profile>

@{
    ViewBag.Title = "NewProfile";
 }

 <h2>NewProfile</h2>

 @foreach (var item in Model)
 {
    @item.UserModel.Username
 }

Before I tell you what the result was, I think it's important to give you an idea about the data in my database. Currently, I only have three users. Only one of these users (gb201) has created any blog posts. The user gb201 has created a total of two blog posts.

When I run the program, and log in as user gb201, the data that is displayed on the NewProfile page is:

New Profile

gb201
gb201

The view is being passed the data from two tables, however, when I wish to query the user table, it is duplicating the data based on the number of posts for that user. So if a user only had one post, then it would only display once. For the two other users, who haven't posted anything, there is no information displayed on this page.

I'm assuming that my linq code which is querying the database is wrong, however, I can't seem to think what i'm doing wrong. Sorry for the long question, and any help would be grateful.

I'd just use navigation properties (I'm assuming you're using EF) instead of explicit join... :) — Patryk Ćwiek, Apr 29 '13 at 07:09
Hi @Trustme-I'maDoctor, I used Linq to SQl to map the database tables to my objects, so I assumed i'm not using EF. — CinnamonBun, Apr 29 '13 at 07:19

NinjaNye · Accepted Answer · 2013-04-29T07:50:50.357

1

What you see is correct as you are building a new Profile per post.

If you simply want one user to be returned you should not join to Post and include the post data afterwards.

var users = db.UserModels.Where(u => u.UserId == WebSecurity.CurrentUserId )
                         .Include(u => u.Posts)
                         .ToList()

You can then read each user and then each post in a nested loop.

UPDATE: Now at pc

In your view

@model IEnumerable<MvcBlog.Data.UserModel>

@{
    ViewBag.Title = "NewProfile";
 }

 <h2>NewProfile</h2>

 @foreach (var item in Model)
 {
    @item.Username

    //if you wanted the postes per user
    foreach(var post in user.Posts)
    {
        @post.Title
    }        
 }

It would be good practice to create a Model for your view, instead of using your data clas as I have.

UPDATE: To work with Linq to SQL

The above will build a viewmodel that you can use in your view.

var users = db.UserModels.Where(u => u.UserId == WebSecurity.CurrentUserId )
                       //.Include(u => u.Posts)
                         .Select(u => UserViewModel{ UserModel = u, Posts => u.Posts})
                         .ToList()

edited Apr 29 '13 at 07:50

answered Apr 29 '13 at 07:20

NinjaNye

7,046
1
32
46

Hi NinjaNye, thanks for the reply. Unfortunately, I don't seem to have access to the 'Include' method for linq. Is this because I used linq to SQL, instead of EF? – CinnamonBun Apr 29 '13 at 07:41
See this post and use `LoadWith`. http://stackoverflow.com/a/5783252/611288 It should give you the same result. You can always remove the `.Include` as it simply makes sure it brings related data back in one query, which may not be a problem for small data sets – NinjaNye Apr 29 '13 at 07:47
@gb1986 I have updated my answer to give you a second option. – NinjaNye Apr 29 '13 at 07:51
Apologies for the constant questions. I tried adding the code you provided, however, I ran into some problems. Specifically this line: .Select(u => UserViewModel{ UserModel = u, Posts => u.Posts}) I made a UserViewModel model, and had the two fields: public UserModel UserModel { get; set; } public PostModel PostModel { get; set; } However, i'm now getting multiple errors relating to the one line. Thanks again for your help. – CinnamonBun Apr 29 '13 at 08:54
1

Vote People. Jeeze, I don't understand how people can be this stingy with upvotes. +1 Thank you! – Dan Beaulieu Sep 22 '15 at 16:51

score 1 · Answer 2 · answered Oct 27 '15 at 12:18

The answer of NinjaNye is right, but it needs to have the 'new' before the viewmodel call, like this:

var users = db.UserModels.Where(u => u.UserId == WebSecurity.CurrentUserId)
                     .Select(u => new UserViewModel{ UserModel = u, Posts => u.Posts })
                     .ToList();

Loading multiple related table data into MVC view. (Linq to SQL)

2 Answers2