[RESOLVED]How to return a object properties through a Result

I am trying to Get all the properties of a object. I am not sure how to do this with the type of relationship I am using. I am returning all of the Jobs in the db. Jobs has a one-to-many relationship with the CustomerEmployee class. I need all of the properties
in the CustomerEmployee class accessible through the Job list. I have managed to get the First & Last Name of the CustomerEmployee but I don’t see how I can access the other properties using this way. So I don’t know if I need to change the way I have the
relationship setup? Which leads me to my next question, when I make a new Job Post, the foreign key is not being inserted in the right column. The db has two different columns.example, "JobTESPMId" & "JobTESPMId_EmployeeId". I need the Id inserted in the "JobTESPMId_EmployeeId"
but it is being inserted in the other one. I got kind of lost when setting this up so I would like to understand why this is happening.

public class Job
{
    public Int64? JobId { get; set; }
    public int? JobNumber { get; set; }
    public string JobName { get; set; }
    public string JobDescription { get; set; }

    public int? GeoAreaId { get; set; }
    public virtual JobMisc.GeoArea GeoArea { get; set; }

    public int? JobClassId { get; set; }
    public virtual JobMisc.JobClass JobClass { get; set; }

    public int? JobTypeId { get; set; }
    public virtual JobMisc.JobType JobType { get; set; }

    public Int64? CustomerId { get; set; }
    public virtual Customer Customer { get; set; }

    public virtual ICollection<ChangeOrder> ChangeOrders { get; set; }
    public virtual ICollection<PurchaseOrder> PurchaseOrders { get; set; }
    public virtual ICollection<JobItem> JobItems { get; set; }

    public int? CustomerEmployeePMId { get; set; }
    public virtual CustomerEmployee CustomerEmployeePM { get; set; }

    public int? CustomerEmployeeAdminId { get; set; }
    public virtual CustomerEmployee CustomerEmployeeAdmin { get; set; }

    public int? CustomerEmployeeAccountantId { get; set; }
    public virtual CustomerEmployee CustomerEmployeeAccountant { get; set; }

    public int? CustomerEmployeeSuperintendentId { get; set; }
    public virtual CustomerEmployee CustomerEmployeeSuperintendent { get; set; }

    public int? JobTESPMId { get; set; }
    public virtual Employee JobTESPM { get; set; }

    public int? JobTESSuperintendentId { get; set; }
    public virtual Employee JobTESSuperintendent { get; set; }
}
public class CustomerEmployee
{
    [Key]
    public int CustomerEmployeeId { get; set; }
    public string CustomerEmployeeFirstName { get; set; }
    public string CustomerEmployeeLastName { get; set; }
    public string CustomerEmployeeEmail { get; set; }
    public Int64? CustomerEmployeePhoneNumber { get; set; }
    public Int64? CustomerEmployeeCellNumber { get; set; }
    public Int64? CustomerEmployeeFaxNumber { get; set; }
    public bool? CustomerEmployeeIsHidden { get; set; }
    public string CustomerEmployeeRole { get; set; }

    public Int64? CustomerId { get; set; }
    public virtual Customer Customer { get; set; }

}
 public class JobViewModel
  {
    public Int64? JobId { get; set; }
    public int? JobNumber { get; set; }
    public string JobName { get; set; }
    public decimal? JobOriginalContract { get; set; }
    public DateTime? JobContractDate { get; set; }
    public decimal? JobTotalCO { get; set; }
    public decimal? JobRevisedContract { get; set; }
    public decimal? JobOriginalBudget { get; set; }
    public string JobStatus { get; set; }
    public bool? JobTaxExempt { get; set; }
    public bool? JobCertPayroll { get; set; }
    public decimal? JobCost { get; set; }
    public decimal? JobRemainingBudget { get; set; }
    public decimal? JobProfit { get; set; }
    public decimal? JobPercentage { get; set; }
    public decimal? JobTotalBilled { get; set; }
    public decimal? JobBalanceToBill { get; set; }
    public decimal? JobPaidToDate { get; set; }
    public decimal? JobBalanceDue { get; set; }
    public bool? JobIsHidden { get; set; }


    public string Customer { get; set; }
    public string CustomerEmployeePM { get; set; }
    public string JobTESPM { get; set; }
    public string JobTESSuperintendent { get; set; }
    public IEnumerable<ChangeOrder> ChangeOrders { get; set; }
    public IEnumerable<PurchaseOrder> PurchaseOrders { get; set; }

  }

 public class JobResult
  {
    public Int64? JobId { get; set; }
    public int? JobNumber { get; set; }
    public string JobName { get; set; }
    public decimal? JobOriginalContract { get; set; }
    public DateTime? JobContractDate { get; set; }
    public decimal? JobTotalCO { get; set; }
    public decimal? JobRevisedContract { get; set; }
    public decimal? JobOriginalBudget { get; set; }
    public string JobStatus { get; set; }
    public bool? JobTaxExempt { get; set; }
    public bool? JobCertPayroll { get; set; }
    public decimal? JobCost { get; set; }
    public decimal? JobRemainingBudget { get; set; }
    public decimal? JobProfit { get; set; }
    public decimal? JobPercentage { get; set; }
    public decimal? JobTotalBilled { get; set; }
    public decimal? JobBalanceToBill { get; set; }
    public decimal? JobPaidToDate { get; set; }
    public decimal? JobBalanceDue { get; set; }
    public bool? JobIsHidden { get; set; }


    public string Customer { get; set; }
    public string CustomerEmployeePM { get; set; }
    public string JobTESPM { get; set; }
    public string JobTESSuperintendent { get; set; }
    public IEnumerable<ChangeOrder> ChangeOrders { get; set; }
    public IEnumerable<PurchaseOrder> PurchaseOrders { get; set; }
 }
// GET api/<controller>
    public IEnumerable<JobResult> Get()
    {
        using (var context = new ApplicationDbContext())
        {
            return context.Jobs
                .Include(x => x.Customer)
                .Include(x => x.ChangeOrders)
                .Include(x => x.PurchaseOrders)
                .Include(x => x.CustomerEmployeePM)
                .Include(x => x.JobTESPM)
                .Include(x => x.JobTESSuperintendent)

                .ToResults();
        }

    }

JSON

0: {$id: "1", JobId: 2, JobNumber: 3244, JobName: "Job Alpha", JobOriginalContract: 34343443,…}
$id: "1"
ChangeOrders: [,…]
Customer: "Twin Peaks"
CustomerEmployeePM: "Kelly Young"
JobId: 2
JobName: "Job Alpha"
JobNumber: 3244
JobTESPM: "Laura Mince"
JobTESSuperintendent: "Scott Willis"
JobTaxExempt: true
JobTotalBilled: null
PurchaseOrders: []
1: {$id: "3", JobId: 9, JobNumber: 342, JobName: "sad", JobOriginalContract: 323232,…}

Hi,

To get the related entity’s properties, you could refer to this code below:

var jobsdata=context.Jobs.Include(x => x.Customer)
                .Include(x => x.ChangeOrders)
                .Include(x => x.PurchaseOrders)
                .Include(x => x.CustomerEmployeePM)
                .Include(x => x.JobTESPM)
                .Include(x => x.JobTESSuperintendent);
string email=jbosdata.First().CustomerEmployeePM.CustomerEmployeeEmail;

texas697

The db has two different columns.example, "JobTESPMId" & "JobTESPMId_EmployeeId"

You could use Fluent API to map foreign key.

# Configuring Relationships with the Fluent API

http://msdn.microsoft.com/en-in/data/jj591620.aspx

Best Regards

Starain

thanks for the reply, here is what I did.

 // GET api/<controller>
        public IEnumerable<JobResult> Get()
        {
            using (var context = new ApplicationDbContext())
            {

                var jbosdata = context.Jobs
                
                .Include(x => x.Customer)
                .Include(x => x.ChangeOrders)
                .Include(x => x.PurchaseOrders)
                .Include(x => x.JobItems)
                .Include(x => x.CustomerEmployeePM)
                .Include(x => x.JobTESPM)
                .Include(x => x.JobTESSuperintendent);
                     string email = jbosdata.First().CustomerEmployeePM.CustomerEmployeeEmail;
                     Int64? fax = jbosdata.First().CustomerEmployeePM.CustomerEmployeeFaxNumber;
                   
                return jbosdata.ToResults();
            }

        }

Here is the Json. I know I did something wrong because the JobItems JSON is not correct. Wouldn’t I need to add something to the JobViewModel for the email and fax properties to show up?

0: {$id: "1", JobId: 95872692, JobNumber: 3247, JobName: "Job Delta", JobOriginalContract: null,…}
1: {$id: "2", JobId: 363328725, JobNumber: 3246, JobName: "Job Charlie", JobOriginalContract: 354345,…}
2: {$id: "3", JobId: 2, JobNumber: 3244, JobName: "Job Alpha", JobOriginalContract: 34343443,…}
$id: "3"
ChangeOrders: [{$id: "4", COId: 751579348, JobId: 2,…}]
Customer: "Twin Peaks"
CustomerEmployeePM: "Kelly Young"
JobBalanceDue: null
JobBalanceToBill: null
JobCertPayroll: true
JobContractDate: "2014-08-02T00:00:00"
JobCost: null
JobId: 2
JobIsHidden: null
JobItems: [{$ref: "8"}, {$ref: "9"}, {$ref: "10"}, {$ref: "11"}, {$ref: "12"}]
JobName: "Job Alpha"
JobNumber: 3244
JobOriginalBudget: 29706734.15
JobOriginalContract: 34343443
JobPaidToDate: null
JobPercentage: null
JobProfit: null
JobRemainingBudget: null
JobRevisedContract: 34949099
JobStatus: "Active"
JobTESPM: "Laura Mince"
JobTESSuperintendent: "Scott Willis"
JobTaxExempt: true
JobTotalBilled: null
JobTotalCO: 605656
PurchaseOrders: []
3: {$id: "15", JobId: 9, JobNumber: 3245, JobName: "Job Bravo", JobOriginalContract: 323232,…}

I also forgot to put this in the original Post. A while ago I was having the same issuse with the Jobitems coming back incorrectly, I was advised to return a result which corrected it, but I didn’t quite understand the Extensions class below.

 public static partial class Extensions
    {
        public static IEnumerable<JobResult> ToResults(this IQueryable<Job> jobs)
        {
            return jobs.Select(x => new JobResult
            {
                JobId = x.JobId,
                JobNumber = x.JobNumber,
                JobName = x.JobName,
                JobOriginalContract = x.JobOriginalContract,
                JobContractDate = x.JobContractDate,
                JobTotalCO = x.JobTotalCO,
                JobRevisedContract = x.JobRevisedContract,
                JobOriginalBudget = x.JobOriginalBudget,
                JobStatus = x.JobStatus,
                JobTaxExempt = x.JobTaxExempt,
                JobCertPayroll = x.JobCertPayroll,
                JobCost = x.JobCost,
                JobRemainingBudget = x.JobRemainingBudget,
                JobProfit = x.JobProfit,
                JobPercentage = x.JobPercentage,
                JobTotalBilled = x.JobTotalBilled,
                JobBalanceToBill = x.JobBalanceToBill,
                JobBalanceDue = x.JobBalanceDue,
                Customer = x.Customer.CustomerName,
                CustomerEmployeePM = x.CustomerEmployeePM.CustomerEmployeeFirstName + " " + x.CustomerEmployeePM.CustomerEmployeeLastName,
                JobTESPM = x.JobTESPM.EmployeeFirstName + " " +  x.JobTESPM.EmployeeLastName,
                JobTESSuperintendent = x.JobTESSuperintendent.EmployeeFirstName + " " + x.JobTESSuperintendent.EmployeeLastName,
                ChangeOrders = x.ChangeOrders.ToList(),
                JobItems = x.JobItems.ToList(),
                PurchaseOrders = x.PurchaseOrders.ToList()
            }).ToList();
        }

Leave a Reply