none
Create join in linq that use String.Contains instead of equals RRS feed

  • Question

  • i have the following linq query that create a left join between two tables:

               var joinResultRows = from leftTable in dataSet.Tables[leftTableName].AsEnumerable()
                                 join
                                      rightTable in dataSet.Tables[rightTableName].AsEnumerable()
                                        on leftTable.Field<string>(leftComparedColumnName) equals rightTable.Field<string>(rightComparedColumnName)
                                            into leftJoinedResult
                                 select new { leftTable, leftJoinedResult };
    

     


    i want to get the rows that answer this: the String value in the left column contains the string value in the right column.(not ‘equals’ like in the example)

    i tried this :

                var joinResultRows = from leftTable in dataSet.Tables[leftTableName].AsEnumerable()
                                 join
                                      rightTable in dataSet.Tables[rightTableName].AsEnumerable()
                                        on leftTable.Field<string>(leftComparedColumnName).Contains(rightTable.Field<string>(rightComparedColumnName)) equals true
                                            into leftJoinedResult
                                 select new { leftTable, leftJoinedResult };
    
    



    but it doesn't work cause rightTable isn't recognized in the left side of the join.

     

    How do i create a join that results the String.Contains, maybe i should use the contains in the 'where' clause or in the 'On' clause?

    please help

    Wednesday, September 2, 2009 1:28 PM

Answers

  • Hi rodniko,

     

    Glad to see you again!

     

    I think what you want is the DefaultIfEmpty() method.  For detail, please see the following code snippet:

    ========================================================================
    var leftOuterJoin = from row1 in dt1.AsEnumerable()

                        from row2 in dt2.AsEnumerable().Where(r2 => r2.Field<int>("ID") == row1.Field<int>("ID")).DefaultIfEmpty()

                        select new { row1, row2 };
    ========================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Thursday, September 3, 2009 2:07 AM
    Moderator

All replies

  • i tried selectmany too :

    var joinResultRows2 = from left in dataSet.Tables[leftTableName].AsEnumerable()
                                     from right in dataSet.Tables[rightTableName].AsEnumerable()
                                     where left.Field<string>(leftComparedColumnName).Contains(right.Field<string>(rightComparedColumnName))
                                     select new { left, right };
    
    but it retunes only the rows that match.
    i need a LEFT JOIN  - All the left rows of the left table with match/nulls in the right table column.
    Wednesday, September 2, 2009 3:38 PM
  • Hi rodniko,

     

    Glad to see you again!

     

    I think what you want is the DefaultIfEmpty() method.  For detail, please see the following code snippet:

    ========================================================================
    var leftOuterJoin = from row1 in dt1.AsEnumerable()

                        from row2 in dt2.AsEnumerable().Where(r2 => r2.Field<int>("ID") == row1.Field<int>("ID")).DefaultIfEmpty()

                        select new { row1, row2 };
    ========================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Thursday, September 3, 2009 2:07 AM
    Moderator
  • Thanks lingzhi, here is the answer:

    var joinResultRows = from leftDataRow in dataSet.Tables[leftTableName].AsEnumerable()
    from rightDataRow in dataSet.Tables[rightTableName].AsEnumerable()
    
    .Where(rightRow => 
    {
    
    // Dont include "" string in the Contains, because "" is always contained // in any string. if ( String.IsNullOrEmpty(rightRow.Field<string>(rightComparedColumnName))) return false; return leftDataRow.Field<string>(leftComparedColumnName).Contains(rightRow.Field<string>(rightComparedColumnName)); }).DefaultIfEmpty() // Makes the right table nulls row or the match row select new { leftDataRow, rightDataRow };
    Thursday, September 3, 2009 1:33 PM
  • Hi rodniko,


    You are welcome.  I am glad that the problem has been resolved. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     

    Thursday, September 3, 2009 1:36 PM
    Moderator
  • Thank you rodniko for this code snippet , exactly what I am looking for to left join tables and return results. I would like to know how I can exclude the null object when writing to an XML.

    XStreamingElement newXML1 =
                        new XStreamingElement("qicore",
                        from a in joinResultRows
                        select
                            new XElement("qic_case",
                                new XElement("case_id", Convert.ToString(a.leftDataRow.Field<string>("case_id"))),
                                new XElement("admission_type", a.leftDataRow.Field<decimal?>("admission_type")),
                                from o in a.rightDataRow //.Where(od => od.Field<string>("diagnosis_code").Count() != 0 )
                                            select
                                                new XElement("diagnosis",
                                                new XElement("other_diagnosis_code", o.Field<string>("diagnosis_code")),
                                               )
     )

                            );

                             Getting error here o in a.rightDataRow  rightDataRow is null. How do I add a condition to exclude when rightDataRow  is null. 

    Thanks,

    LC

    Friday, January 10, 2014 5:20 PM