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 stackoverflow.com/questions/6379155/… or do 2 queries to fill up them both — misha130
– misha130, Commented Feb 6, 2016 at 20:31

Gusman · Accepted Answer · 2016-02-06 20:14:28Z

1

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

answered Feb 6, 2016 at 20:14

Gusman

15.2k2 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gusman Over a year ago

Nope, not with dapper, you need a model for each set of data you have.

Collectives™ on Stack Overflow

Map navigation properties in Dapper.Net using stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related