Map navigation properties in Dapper.Net using stored procedure

Question

I am using Dapper.Net to get data from SQL Server database.

Here is my POCO classes

public partial class Production
{
    public System.Guid ProductionId { get; set; }
    public System.Guid SurveyId { get; set; }
    public Nullable<int> PercentComplete { get; set; }
    public string CompletedBy { get; set; }
    public string DeliverTo { get; set; }

    public virtual SurveyJob SurveyJob { get; set; }
}

 public partial class SurveyJob
 {
        public SurveyJob()
        {
            this.Productions = new HashSet<Production>();
        }

        public System.Guid SurveyId { get; set; }
        public string JobTitle { get; set; }
        public Nullable<int> Status { get; set; }
        public Nullable<int> JobNumber { get; set; }
        public Nullable<System.DateTime> SurveyDate { get; set; }
        public Nullable<System.DateTime> RequiredBy { get; set; }
        public virtual ICollection<Production> Productions { get; set; }
}

I want to get all productions along with their SurveyJob information. Here is my SQL query in the stored procedure which returns these columns

SELECT 
    P.ProductionId, S.SurveyId, 
    P.PercentComplete, P.CompletedBy, P.DeliverTo, 
    S.JobTitle, S.JobNumber, S.RequiredBy, S.Status, S.SurveyDate
FROM 
    dbo.Production P WITH(NOLOCK)
INNER JOIN 
    dbo.SurveyJob S WITH(NOLOCK) ON S.SurveyId = P.SurveyId

Problem is that I am getting Production data but SurveyJob object is null.

Here is my c# code

var result = await Connection.QueryAsync<Production>("[dbo].[GetAllProductions]", p, commandType: CommandType.StoredProcedure);

I am getting SurveyJob object null as shown in image.

Need help. What I am doing wrong?

check this related question http://stackoverflow.com/questions/6379155/multi-mapper-to-create-object-hierarchy or do 2 queries to fill up them both — misha130, Feb 06 '16 at 20:31

score 1 · Accepted Answer · answered Feb 06 '16 at 20:14

Your model is malformed for the query you executed, your query will return a plain object (dapper will always return plain objects), so you need a class with all the properties you're selecting.

Change your model to this:

public partial class ProductionSurvey
{
    public System.Guid ProductionId { get; set; }
    public System.Guid SurveyId { get; set; }
    public Nullable<int> PercentComplete { get; set; }
    public string CompletedBy { get; set; }
    public string DeliverTo { get; set; }
    public System.Guid SurveyId { get; set; }
    public string JobTitle { get; set; }
    public Nullable<int> Status { get; set; }
    public Nullable<int> JobNumber { get; set; }
    public Nullable<System.DateTime> SurveyDate { get; set; }
    public Nullable<System.DateTime> RequiredBy { get; set; }
}

Nope, not with dapper, you need a model for each set of data you have. — Gusman, Feb 06 '16 at 20:20

Map navigation properties in Dapper.Net using stored procedure

1 Answers1