Home Ask Login Register

Developers Planet

Your answer is one click away!

Ghazanfar February 2016

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 i        </div>
      </div>
    </div>
    
    <div class=

Answers


Gusman February 2016

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

Post Status

Asked in February 2016
Viewed 1,833 times
Voted 14
Answered 1 times

Search




Leave an answer


Quote of the day: live life

Devs Planet ®

2014-2016 www.devsplanet.com

Devs Planet © all rights reserved

Search