LINQ Statement Help : The Official Microsoft ASP.NET Forums

Reply

maddtechwf

Member

17 Points

LINQ Statement Help

Nov 06, 2012 08:00 PM|LINK

I have a linq statement that is below. It works great when returning all employees. I'm needing it to also return the multiple jobs that people have from a position table and then also their multiple websites from a website table that I have. Like I said before, I have it returning the employees info just fine, just can't get it to return the nested XML items for positions and websites. I have an examl of what my xml looks like and what I want it to look like.

LINQ statement

public List<Models.Employee> Get() {

            // get all of the records from the employees table in the
            // northwind database.  return them in a collection of user
            // defined model objects for easy serialization.
            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 = Positions(e.ID),
                                 phone = pos.public3 == null ? string.Empty : "111-222-" + pos.public3,
                                 dept = dep.dept,
                                 building = col.college,
                                 fax = pos.fax3 == null ? string.Empty : "111-222-" + pos.fax3,
                                 room = pos.room,
                                 photo = "https://test.mysite.com/profiles/uploads/photos/" + e.PhotoFileName
                            };

            // returns the employees as a list, which is converted
            // to an array during serialization to JSON
            return employees.ToList();
        }

Current XML Structure

<ArrayOfEmployees>
    <employee>
       <ID></ID>
       <TN></TN>
       <FN></FN>
       <LN></LN>
       <SN></SN>
       <email></email>
       <photo></photo>
     </employee>
</ArrayOfEmployees>

What I want it to look like

<ArrayOfEmployees>
    <employee>
       <ID></ID>
       <TN></TN>
       <FN></FN>
       <LN></LN>
       <SN></SN>
       <positions>
          <position>
             <title></title>
             <buildling></building>
          </position>
          <position>
             <title></title>
             <buildling></building>
          </position>
       </positions>
       <websites>
          <website>
             <web_title></web_title>
             <web_url></web_url>
          </website>
          <website>
             <web_title></web_title>
             <web_url></web_url>
          </website>
          <website>
             <web_title></web_title>
             <web_url></web_url>
          </website>
       </websites>
       <email></email>
       <photo></photo>
     </employee>
</ArrayOfEmployees>