locked
Join on two tables using Linq Query

    Question

  • Hi,

    I have two tables:

    Table A:

    TID(PK) | TName | ID(FK) (of another table) |

    1             A           112

    2             B           112

    3            C            111

    4            D            112

    Table B:

    TBID(PK) | TBName | TBFromDate | TBToDate | TBModBy | ADNo | Field | Valid From |----- TID(FK of Table A)|

    1                A               1/1/2012      2/2/2012     AB           12         S        1/1/2011       1

    2                B               3/3/2012       2/3/2012     SD          12        W        1/1/2011       1

    3                C                2/4/2011      2/1/2012      FG          13       B           2/2/2011     2

    4               D                 1/1/2010      12/12/2012   HJ         13        N          2/2/2011     2

    5               E                2/2/2013       2/1/2100       KL         15        M          3/3/2011     3

    6                F               4/4/2011       5/5/2012       NM        16         J           4/4/2011    4                                                                                                                              

    Now, User fire a query as Input parameters ( TID , ID (112 always) , TBFromDate, TBToDate, TBModBy) all are the optional parameters...

    Output parameters should be : 

    TID | Valid From | TBModBy | ADNo |

    1      1/1/2011         AB           12        

    2      2/2/2011         FG           13 

    4      4/4/2011         NM           16

    What will be the LINQ for the same?

    Thanks and Regards

    Harsh 

    Saturday, December 29, 2012 6:40 AM

Answers

  • Hi KapoorHarsh;

    The query which is stored in the variable results is NOT executed until it reaches this line of code :

       // Execute the query with its dynamic where clause
        return results.ToList();

    The manipulation of the variable results before the above line of code is just building the T-SQL which will be sent to the server. In Linq this is called deferred execution and which means that the query is NOT executed until it is enumerated over and this does not happen until the ToList() is applied to the results variable. This in fact makes the query dynamic as it is NOT executed until you have finished building the query.

       

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by KapoorHarsh Thursday, January 03, 2013 6:04 PM
    Wednesday, January 02, 2013 6:43 PM

All replies

  • if you just need TID field from table A witch is available in table B then there is no need to join two tables. you can try both below codes.

    var source = new YourDataContextName().A;


    or

    var context = new YourDataContextName();
    var source = from a in context.A select a;


    but if you need all table A and B fields together you should try joining them with this code

    var source = from a in context.A join b in context.B on a.TID equals b.TID select a, b

    good luck




    Please mark the post as an answer that helps/solves your problem.

    Saturday, December 29, 2012 7:19 AM
  • Hi

    Thanks for the reply.

    But in the query var source = from a in context.A join b in context.B on a.TID equals b.TID select a, b, it will give me all the rows of the table to according to the where clause. While i want only single-single row from the table B against TID

    Thanks and Regards

    Harsh 

    Saturday, December 29, 2012 7:25 AM
  • Hi Harsh ,

    From your description, I ‘d like to move this post to  the most related forum.

    There are more  experts in this aspect, so you will get  better support and  may have more luck getting answers.

    Thanks for your understanding.

    Regards,


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 01, 2013 2:33 AM
  • Hi;

    Please specify on which columns you want to join the two tables on and what you want to be returned from the query?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, January 01, 2013 3:53 AM
  • Hi Fernando,

    Thanks for the reply.

    Join will be on TID.

    one more thing I want to make this query as dynamic query means its for searching so out of 5 inputs user can search on any permutations and combinations.

    I have written like this:

    List<ClassName> functionName(Obj)

    {

           List<ClassName> lstName  = new List<ClassName>()

          lstName = (from t1 in table1 join t2 in table2 on t1.TID equals t2.TID where t1.TID == (!string.IsNullOrEmpty(Obj.TID) ? Obj.TID : t1.TID

                          && t2.TBFromDate >= (Obj.Fromdate!=null ) ? Obj.TBFromdate : t2.TBFromDate && t2.TBToDate <= (Obj.Todate!=null ) ? Obj.TBTodate : t2.TBToDate && t2.ModBy == (!string.IsNullOrEmpty(Obj.ModBy) ? Obj.ModBy : t2.ModBy

         select new ClassName

    {

      var1,

    var2

    });

    return lstName;

    }

    But its not working as a dynamic query :(

    Thanks and Regards

    Harsh

    Tuesday, January 01, 2013 8:05 AM
  • Hi KapoorHarsh;

    The following code will show how to accomplish what you need.

    // The class FindSelectedValues is holding the values that will be sent to the function. 
    // You should initialize all values.
    var findValues = new FindSelectedValues();
    findValues.TID = null;
    findValues.ValidFrom = null;
    findValues.TBModBy = "FG";
    findValues.ADNo = 13;
    
    // Call the function to get the results of the dynamic query.
    List<ClassName> resultList = functionName( findValues );
    
    
    
    
    // Function to run dynamic query
    List<ClassName> functionName(FindSelectedValues value)
    {
        // Base query to return all joined records from the two tables with only four columns
        var results = from ta in ObjectContextName.Table_As
                      join tb in ObjectContextName.Table_Bs on ta.TID equals tb.TID
                      select new ClassName
                      {
                          TID = ta.TID,
                          ValidFrom = tb.ValidFrom,
                          TBModBy = tb.TBModBy,
                          ADNo = tb.ADNo
                      };
        
        // You need to add one if statement for each value you want to filter on
        // You will need to modify the if statements or add more if statements as needed.
        if( value.TID.HasValue )
        {
            results = results.Where (t => t.TID == value.TID );
        }
        
        if( value.TBModBy != String.Empty )
        {
            results = results.Where (t => t.TBModBy == value.TBModBy );
        }
        
        if( value.ADNo.HasValue )
        {
            results = results.Where (t => t.ADNo == value.ADNo );
        }
        
        if( value.ValidFrom.HasValue )
        {
            results = results.Where (t => t.ValidFrom == value.ValidFrom );
        }
        
        // Execute the query with its dynamic where clause
        return results.ToList();
    }
    
    // Class that holds the result set records from query
    public class ClassName
    {
        public int TID { get; set; }
        public DateTime ValidFrom { get; set; }
        public string TBModBy { get; set; }
        public int ADNo { get; set; }
    }
    
    // Class to hold the values of what you want to filter on
    public class FindSelectedValues
    {
        public int? TID { get; set; }
        public DateTime? ValidFrom { get; set; }
        public string TBModBy { get; set; }
        public int? ADNo { get; set; }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, January 01, 2013 3:53 PM
  • Thanks Fernando

    But in this the main problem will be when we have a lots of data then instead of implement the where clause after query we should write the where clause that behaves as dynamic query

    Thanks and Regards

    Harsh

    Wednesday, January 02, 2013 5:16 PM
  • Hi KapoorHarsh;

    The query which is stored in the variable results is NOT executed until it reaches this line of code :

       // Execute the query with its dynamic where clause
        return results.ToList();

    The manipulation of the variable results before the above line of code is just building the T-SQL which will be sent to the server. In Linq this is called deferred execution and which means that the query is NOT executed until it is enumerated over and this does not happen until the ToList() is applied to the results variable. This in fact makes the query dynamic as it is NOT executed until you have finished building the query.

       

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by KapoorHarsh Thursday, January 03, 2013 6:04 PM
    Wednesday, January 02, 2013 6:43 PM
  • Thank you very much Fernando. You described it very well with code and theoritically as well.

    Thanks dude :)

    Thursday, January 03, 2013 6:04 PM
  •   

    Not a problem, glad I was able to help.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, January 03, 2013 6:11 PM