Merge connection with String.Contains in Linq query

Question

I have the following linq query that creates 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 rows that respond to this: the String value in the left column contains the string value in the right column.

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 this does not work because rightTable is not recognized on the left side of the connection.

How to create a connection that leads to String.Contains, do I make the contents in the 'where' clause or in the 'On' clause?

Answer 1

I solved this by creating SelectMany (join), that all records are stored in the left table, and on the right it is null if there is no match:

 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 }; 

Thanks for the help :)

Answer 2

Have you tried SelectMany?

 var result = 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 }; 

Edit:

The following should have the desired effect:

 class ContainsEqualityComparer: IEqualityComparer<string> { public bool Equals(string right, string left) { return left.Contains(right); } public int GetHashCode(string obj) { return 0; } } var result = dataSet.Tables[leftTableName].AsEnumerable().GroupJoin( dataSet.Tables[rightTableName].AsEnumerable(), left => left, right => right, (left, leftJoinedResult) => new { left = left, leftJoinedResult = leftJoinedResult }, new ContainsEqualityComparer()); 

Key comparison is done through the custom IEqualityComparer. Two lines will only be joined when GetHashCode () on the left and on the right matches, and Equals returns true.

Hope this helps.