rgfirefly24, on 09 November 2012 - 08:44 PM, said:

I'm a little loss at what you mean.

h4nnib4l, on 09 November 2012 - 08:54 AM, said:

I have the joins setup like you said before, they just return on position. I need it to return multiple positions if an employee has one.

This is the XML structure that I want it to follow.

<ArrayOfEmployees>
   <employee>
      <id></id>
      <title></title>
      <first></first>
      <middle></middle>
      <last></last>
      <suffix></suffix>
      <email></email>
      <positions>
         <position>
            <id></id>
            <room></room>
            <hours></hours>
            <title></title>
            <location></location>
            <college></college>
         </position>
         <position>
            <id></id>
            <room></room>
            <hours></hours>
            <title></title>
            <location></location>
            <college></college>
         </position>
      </positions>
      <picture></picture>
   </employee>
</ArrayOfEmployees>

The site is having issues with the replies. It keeps clearing my reply before I can post it. WOOHOO! I finally got it posted. Anyways...

I'm needing to read the employee db and the read the positions for each employee in the positions table. 1 employee can have multiple or no jobs.

Position.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;

namespace hello_services.Models {
    public class Position 
    {
        public int id { get; set; }
        public string room { get; set; }
        public string off_hours { get; set; }
        public string title { get; set; }
        //public string primarydept { get; set; }
        public string phone { get; set; }
        public string fax { get; set; }
        public string location { get; set; }
        public string college { get; set; }
    }
}

Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;

namespace hello_services.Models {
    public class Employee {

        public int Id { get; set; }
        public string First { get; set; }
        public string Middle { get; set; }
        public string Last { get; set; }
        public string Title { get; set; }
        public string Suffix { get; set; }
        public string email { get; set; }
        public string dept { get; set; }
        public string building { get; set; }
        public string room { get; set; }
        public List<string> Job { get; set; }
        public string photo { get; set; }
        public List<string> Websites { get; set; }
        // public string phone { get; set; }
        // public string fax { get; set; }

    }
}

LINQ statement

var employees = from e in _context.tbl_peoples
                            join pos in _context.tbl_positions on e.ID equals pos.people_ID
                            join dep in _context.tbl_departments on pos.dept_ID equals dep.ID
                            join col in _context.tbl_colleges on dep.college_ID equals col.id
                            select new Models.Employee
                            {
                                Id = e.ID,
                                Title = e.Name_Title,
                                First = e.Name_First,
                                Middle = e.Name_Middle,
                                Last = e.Name_Last,
                                Suffix = e.Name_Suffix,
                                email = e.email_1,
                                Job = from d in _context.tbl_positions
                                      join depart in _context.tbl_departments on d.dept_ID equals depart.ID
                                      join college in _context.tbl_colleges on d.college_ID equals college.id
                                      join location in _context.tbl_locations on d.location_ID equals location.id
                                      where d.id == e.ID
                                      select new
                                      {
                                          //id = d.id,
                                          //room = d.room,
                                          //off_hours = d.office_hours,
                                          //title = d.title,
                                          //phone = d.public3 == null ? string.Empty : "111-222-" + d.public3,
                                          //fax = d.fax3 == null ? string.Empty : "111-222-" + d.fax3, 
                                          //location = location.Name,
                                          //college = college.college
                                          d.id,
                                          d.room,
                                          d.office_hours,
                                          d.title,
                                          //d.public3 == null ? string.Empty : "111-222-" + d.public3,
                                          //d.fax3 == null ? string.Empty : "111-222-" + d.fax3, 
                                          location.Name,
                                          college.college
                                      }.ToString(),
                                photo = "https://hi.mywebsite.com/profiles/uploads/photos/" + e.PhotoFileName
                            };

Okay, I see how that works but the jobs has to be populated by 3 tables.

Okay so I didn't realize that I was using a LINQ database query. I just found that out yesterday. So with that said, I'm needing some serious help in writing a LINQ statement that will return an individual info along with all their positions that they currently hold. This data is scattered over multiple tables. I have my basic info showing just fine, just don't know how to also query all the positions they have also.