CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresdapper
Ghazanfar Khan
Ghazanfar Khan

Reputation: 3718

Map navigation properties in Dapper.Net using stored procedure

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

Upvotes: 0

Views: 2563

Answers (1)

Gusman
Gusman

Reputation: 15161

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

Upvotes: 1

Related Questions