[rgfirefly24]

you will want to use a join(http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9) in your linq statement like so:

var output = (from i in table1
				join b in table2 on i.col = b.col into joinedTable
				select new { FirstName = i.firstName, LastName = i.lastName, Groups = joinedTable });

[maddtechwf]

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

[rgfirefly24]

Show me your table structure and the object you want them stored in. It could be as simple as using another join statement.

[maddtechwf]

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

This post has been edited by maddtechwf: 09 November 2012 - 07:53 AM

[h4nnib4l]

I posted an example of a SELECT with multiple JOINs in one of your other threads, here. Is that not still applicable to what you're trying to do?

Also, I've been having a bit of trouble with DIC in FireFox, and the Fast Reply window has been sketchy in FireFox and Chrome, but Full Reply works fine in Chrome.

EDIT: realized you're trying to do this with LINQ now, not SQL.

This post has been edited by h4nnib4l: 09 November 2012 - 07:56 AM

[maddtechwf]

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

I posted an example of a SELECT with multiple JOINs in one of your other threads, here. Is that not still applicable to what you're trying to do?

Also, I've been having a bit of trouble with DIC in FireFox, and the Fast Reply window has been sketchy in FireFox and Chrome, but Full Reply works fine in Chrome.

EDIT: realized you're trying to do this with LINQ now, not SQL.

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>

[rgfirefly24]

Have you tried changing the linq statement to use the join table?

[maddtechwf]

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

Have you tried changing the linq statement to use the join table?

I'm a little loss at what you mean.