locked
Linq Query inside Query RRS feed

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

    Wednesday, November 7, 2012 2:58 PM

Answers

  • 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

    • Proposed as answer by Jason Dot Wang Friday, November 9, 2012 4:04 AM
    • Marked as answer by Jason Dot Wang Thursday, November 15, 2012 3:30 AM
    Thursday, November 8, 2012 1:49 PM

All replies

  • 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);

    Thursday, November 8, 2012 1:41 PM
  • 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

    • Proposed as answer by Jason Dot Wang Friday, November 9, 2012 4:04 AM
    • Marked as answer by Jason Dot Wang Thursday, November 15, 2012 3:30 AM
    Thursday, November 8, 2012 1:49 PM
  • 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.

    Thursday, November 8, 2012 3:08 PM