Map navigation properties in Dapper.Net using stored procedure

c# dapper sql sql-server stored-procedures

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?

enter image description here

Accepted Answer

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


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why