How to create Query syntax for multiple DataTable for implementing IN operator of Sql Server

Question

I have fetched 3-4 tables by executing my stored procedure. Now they resides on my dataset.

I have to maintain this dataset for multiple forms and I am not doing any DML operation on this dataset.

Now this dataset contains 4 tables out of which i have to fetch some records to display data. Data stored in tables are in form of one to many relationship.

i.e. In case of transactions. N records per record. Then these N records are further mapped to M records of 3rd table.

Table 1

MAP_ID               GUEST_ID             DEPARTMENT_ID        PARENT_ID            PREFERENCE_ID
-------------------- -------------------- -------------------- -------------------- --------------------
19                   61                   1                    1                    5
14                   61                   1                    5                    15
15                   61                   2                    4                    10
18                   61                   2                    13                   23
17                   61                   2                    20                   26
16                   61                   40                   40                   41
20                   62                   1                    5                    14
21                   62                   1                    5                    15
22                   62                   1                    6                    16
24                   62                   2                    3                    4
23                   62                   2                    4                    9
27                   62                   2                    13                   23
25                   62                   2                    20                   24
26                   62                   2                    20                   25
28                   63                   1                    1                    5
29                   63                   1                    1                    8
34                   63                   1                    5                    15
30                   63                   2                    4                    10
33                   63                   2                    4                    11
31                   63                   2                    13                   23
32                   63                   40                   40                   41
35                   65                   1                    NULL                 1
36                   65                   1                    NULL                 1
38                   68                   2                    13                   22
37                   68                   2                    20                   25
39                   68                   2                    23                   27
40                   92                   1                    NULL                 1

Table 2

Department_ID        Department_Name         Parent_Id             Parent_Name                                                                                                                                                                                                                                              
-------------------- ----------------------- ---------------   ----------------------------------------------------------------------------------
1                    Food                    1, 5, 6               Food, North Indian, South Indian                                                                                                                                                                                                                                           
2                    Lodging                 3, 4, 13, 20, 23      Room, Floor, Non Air Conditioned, With Balcony, Without Balcony                                                                                                                                                                                                                                               
40                   New                     40                    SubNew

TABLE 3

Parent_Id            Parent_Name                                        Preference_ID             Preference_Name
-------------------- -----------------------------------------------  -----------------------  -------------------
NULL                 NULL                                               NULL                     NULL
1                    Food                                               5, 8                     North Indian, Italian
3                    Room                                               4                        Floor
4                    Floor                                              9, 10, 11                First, Second, Third
5                    North Indian                                       14, 15                   X, Y
6                    South Indian                                       16                       Dosa
13                   Non Air Conditioned                                22, 23                   With Balcony, Without Balcony
20                   With Balcony                                       24, 25, 26               Mountain View, Ocean View, Garden View
23                   Without Balcony                                    27                       Mountain View
40                   New                                                41           SubNew 

I have these 3 tables that are related in some fashion like this.

Table 1 will be the master for these 2 tables i.e. table 2 and table 3.

I need to query on them as

        SELECT Department_Id, Department_Name, Parent_Name FROM Table2 WHERE Department_Id in 
            (
                SELECT Department_Id FROM Table1 WHERE guest_id=65
            )


        SELECT Parent_Id, Parent_Name, Preference_Name FROM Table3 WHERE PARENT_ID in 
            (
                SELECT parent_id FROM Table1 WHERE guest_id=65 
            )

Now I need to use these queries on DataTables.

So I am using Query Syntax for this and reached up to this point.

        var dept_list=  from dept in DtMapGuestDepartment.AsEnumerable()
                        where dept.Field<long>("PK_GUEST_ID")==long.Parse(63)
                        select dept;

This should give me the list of all departments that has guest id =63

Now I want to select all departments_name and parent_name from Table 2 where guest_id=63 i.e. departments that i fetched above.

This same case will be followed for Table3.

Please suggest how to do this.

Thanks for keeping up patience for reading my question.

Answer 1

Well, suppose you have 3 IEnumerable vars, one for each table. Your first query would be like this:

var records = from r1 in table1 where r1.GuestId == 63
              join r2 in table2 on r1.DepartmentId equals r2.DepartmentId
              select r2;

The second query is nearly the same as the first one, joining with table 3 instead of 2.

var records = from r1 in table1 where r1.GuestId == 63
              join r3 in table3 on r1.ParentId equals r2.ParentId
              select r3;