Linq Query inside Query

Question

I'm not sure if this is the correct way to do this so I'm posting here to get some help.  I'm 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

// 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(),
        }

XML Layout

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

Everything but the Website and Positions part of the XML layout already works.

Answer 1

Hi,

What are you trying to match in Sub Query? Matching Position? Please explain a bit.

Otherwise here is the example to use in queries.

var names = new string[] { "Alex", "Colin", "Danny", "Diego" };
var matches = from person in people 
        where names.Contains(person.Firstname) 
        select person;

First store the inner query in one variable then use that variable with where condition.

Or like this

var ids = new string[] { "3012472", "3012473", "3012474", "3012475" };
    var query = context.Employee
                       .WhereIn(e => e.EmployeeId, ids)
                       .OrderBy(e => e.EmployeeId);

Answer 2

See if this helps:

http://stackoverflow.com/questions/5839896/simple-examples-of-joining-2-and-3-table-using-lamda-expression

http://www.codeproject.com/Articles/488643/LinQ-Extended-Joins

Answer 3

How my tables are linked

tbl_people.id = tbl_position.people_ID , tbl_position.dept_id = tbl_department.id , tbl_department.college_id = tbl_college.id , tbl_people.id = tbl_websites.people_id , tbl_position.location_id

What I'm trying to match

tbl_people.id in both sub queries.