Dapper - Object reference not set to an instance of an object

Question

I've recently been playing around with dapper, but I've run into a bit of a problem regarding getting data from other tables.

My database has two tables, Users and Post. I've created two classes for these tables.

My User class

 public class User
 {
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime JoinDate { get; set; }
 }  

My Post class

  public class Post
 {
    public int PostId { get; set; }
    public string Title { get; set; }
    public DateTime PublishDate { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
 }

This is my query to fetch all the post data from my database, and also get the Username who created the post.

    public IEnumerable<Post> GetPosts()
    {
        string myQuery = "SELECT p.Title, p.PublishDate, p.PostId, p.UserId, u.Username FROM Post p INNER JOIN Users u ON p.UserId = u.UserId";

        sqlConnection.Open();
        var posts = sqlConnection.Query<Post>(myQuery);

        return posts;
    }

Here is my view code where I want to display the information:

@model IEnumerable<MvcDapperIntro.Models.Post>

@{
    ViewBag.Title = "Posts";
}

@foreach (var post in Model)
{
    <h3>@post.Title</h3>
    <p>@post.PublishDate.ToString("dd/MM/yyyy")</p>
    <p>@post.User.Username</p>
}

However, when I run my code, I get the 'Object reference not set to an instance of an object' error. Is this because I shouldn't be using:

public User User { get; set; }

to access the username property, or should I create a new class with a username field. Any input would be appreciated.

Answer 1

I get the 'Object reference not set to an instance of an object' error

I bet the issue is on this line: <p>@post.User.Username</p> and that is because you did not map the User object. You should change this code:

var posts = sqlConnection.Query<Post>(myQuery);

to this:

var posts = sqlConnection.Query<Post, User, Post>(
                myQuery,
                (p, u) => { p.User = u; return p; }
                );