title: How can I display data from multiple tables using a code-first approach
body: <p>I'm using a code-first approach to display data from multiple tables using SQL Server. For my <a href="http://en.wikipedia.org/wiki/C_Sharp_%28programming_language%29" rel="nofollow">C#</a> semester project, I'm building a social networking website. In my index view I want to display data from two tables, that is, from Posts and Comments.</p>
  
  <p>I'm trying a lot but nothing below is in my Index action.</p>
  
  <p>First, I was using this but later I think it performs only a simple join between tables.</p>
  
  <pre><code>public ActionResult Index()
  {
      var myobj = DB.Comments.Include("Post").ToList();
      return View(myobj);
  }
  </code></pre>
  
  <p>If I use the SQL query below instead of above inside my Index action and pass them to the view, it generates an exception. How do I avoid that?</p>
  
  <pre><code>public ActionResult Index()
  {
       var myobj = from u in DB.Posts
                       join b in DB.Comments
                       on u.UserID equals b.UserID
                       into obj
                       from ua in obj.DefaultIfEmpty()
                       select new { userComments = (ua == null) ? "" : ua.UserComments, UserPosts = u.UserPosts };
  
       return View(myobj);
   }
  </code></pre>
  
  <p>where inside Index.cshtml I'm using a strongly typed View:</p>
  
  <pre><code>@model IEnumerable&lt;myApp.Models.DB.Comment&gt;
  </code></pre>
  
  <p>and then display all posts and comments using a <code>foreach</code> loop.</p>
  
  <p>But it displays only those posts which postID is present in the Comments table (foreign key from post is in the comment table).</p>
  
  <p>How would I display all posts along with their comments (if any)?</p>
  
  <hr>
  
  <p>I'm using an .edmx file for the backend while for retrieving/adding data from the database I'm using a code-first approach.</p>
  
  <p>Here's a code example:</p>
  
  <pre><code>private SocialNetworkingEntities net = new SocialNetworkingEntities();
  
      public void Add(RegisterView user)
      {
          DB.UserInfo User = new DB.UserInfo();
          User.Name = user.UserName;
          User.Password = user.UserPassword;
          User.Email = user.UserEmail;
          User.Birthday = user.UserDOB;
          User.Sex = user.UserSex;
          net.UserInfoes.Add(User);
          net.SaveChanges();
      }
  </code></pre>