Linq Query inside Query

[EDN Admin]

Im not sure if this is the correct way to do this so Im posting here to get some help. Im new to C# and LINQ queries. Below you will find my LINQ statement that I have written. I have 6 tables to query data from at one time. Two
of my tables have data that can have multiple relationships back to one employee in the main table. I would like it to list those out. I appreciate any help. The XML output I would like it below also.
LINQ Query Statement<br/>

<pre class="prettyprint // WebAPI will respond to an HTTP GET with this method
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 = 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 Models.Position
{
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
}.ToString(),
}[/code]
<br/>
XML Layout<br/>

<pre class="prettyprint <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>[/code]
<br/>
Everything but the Website and Positions part of the XML layout already works.


View the full article