none
Variable Join Conditions in LINQ on Datatable RRS feed

  • Question

  • Hello
    
    I am trying to build a class for joining two tables. I would like to pass in a string array representing the columns frome each table to join. The length of this array could vary. Below is the code I'm starting with. Notice I have a single 'Equals' join condition (one column).
    
    
            Dim oEMsub As IEnumerable(Of DataRow) = From oMainTable As DataRow In table1_
                                Join oSubTable As DataRow In table2_
                                On oMainTable.Item("ID") Equals oSubTable.Item("ID") _
                                Select extendedExplicitMemberTable.DataTable.Rows.Add( _
                                oMainTable.Item("ID"), _
                                oSubTable.Item("Name"), _
                                oSubTable.Item("Description"), _
                                oSubTable.Item("Label") _
                                )
    

    For my purposes, I need to have the ability to vary the number of conditions for the Equals (with AND) as below:

     

    Dim oEMsub As IEnumerable(Of DataRow) = From oMainTable As DataRow In table1_
                        Join oSubTable As DataRow In table2_
                        On <table1.column1> Equals <table2.column1> And _
                        <table1.column2> Equals <table2.column2> And .... etc
    
    T.I.A
    
    Rick
    
    
    • Moved by Liliane Teng Friday, December 10, 2010 5:41 AM more appropriate forum (From:Visual Basic Language)
    Wednesday, December 8, 2010 7:38 AM

Answers

  • Hello Rick,

    As far as I know, you can join the tables on multiple columns like this in linq to entities (more comments):
           var query = from t1 in context.T1
                             join t2 in context.T2
                             on new {t1.Column1, t1.Column2} equals new {t2.Column3, t2.Column4}
                             select new {….};

    But essentially, it generates the same sql statement with multiple where clause. In Linq to DataSet, I think you can try with the following query:
           var query = from r1 in ds1.Tables[0].AsEnumerable()
                             from r2 in ds2.Tables[0].AsEnumerable()
                             where r1.Field<Type>(“field1”).Equals(r2.Field<Type>(“field1”)  &&  r1.Field<Type>(“Field2”).Equals(r2.Field<Type>(“Field2”))
                             select {….};

    Please have a try and let me know if it does not work.

    Best regards,


    Roahn Luo [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by liurong luo Friday, December 17, 2010 6:51 AM
    Friday, December 10, 2010 11:09 AM

All replies

  • Hello Rick,

    Thanks for your post.

    I have moved your thread to ADO.NET DataSet forum where you may get more better support because more this aspect experts live there.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 10, 2010 5:43 AM
  • Hello Rick,

    As far as I know, you can join the tables on multiple columns like this in linq to entities (more comments):
           var query = from t1 in context.T1
                             join t2 in context.T2
                             on new {t1.Column1, t1.Column2} equals new {t2.Column3, t2.Column4}
                             select new {….};

    But essentially, it generates the same sql statement with multiple where clause. In Linq to DataSet, I think you can try with the following query:
           var query = from r1 in ds1.Tables[0].AsEnumerable()
                             from r2 in ds2.Tables[0].AsEnumerable()
                             where r1.Field<Type>(“field1”).Equals(r2.Field<Type>(“field1”)  &&  r1.Field<Type>(“Field2”).Equals(r2.Field<Type>(“Field2”))
                             select {….};

    Please have a try and let me know if it does not work.

    Best regards,


    Roahn Luo [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by liurong luo Friday, December 17, 2010 6:51 AM
    Friday, December 10, 2010 11:09 AM
  • Hello,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day!


    Roahn Luo [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 15, 2010 7:42 AM