title: Tricky LINQ query: return string that contain substring of the max length substring
body: <p>i have 2 datatables each datatable has 300,000 rows (datatables imported from 2 excel worksheets with OLEDB).</p>
  
  <p>the first datatable is 'dtTosearch' and the second datatable is 'sourceDt'.</p>
  
  <p>here is an example for the 2 tables: </p>
  
  <p><a href="https://i.stack.imgur.com/wjm9i.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wjm9i.png" alt="datatables example"></a></p>
  
  <p>i need to find for each row in 'untagged' column (sourceDt) a match to each row in 'token' column (dtTosearch). the match conditions are: </p>
  
  <ol>
  <li>'site' value = 'site' value</li>
  <li>'cat' value = 'category' value</li>
  <li>untagged value must contain token value</li>
  <li>if all the conditions above exist for more then one match so the query must    <strong>returen the match of the token with the maximum length</strong>.
  <strong>(and this is the tricky part that i didnt figure out how to perform with
  linq)</strong></li>
  <li>this task must run in minimum process time - because its the requirement, because that's more professional and because i made a bet with friend-colleague-JAVA enthusiastic developer that .NET will run faster (plain as day (-:)</li>
  </ol>
  
  <p>ive added the relevant parts of the code, it works fine but not in the way i want, i want to improve processing time, look at the foreach loop in linqQuery() function - i will be thankful if you help me to replace that loop by expanding my query to condition number 4, the loop operate for condition 4 because the result of <strong>the linq query ordered by 'token' length in descending order so it will exit and return the result with the maximum row length.</strong></p>
  
  <pre><code>Private Sub startScanning()
      Dim siteNum As Double
      Dim categoryNum As Double
      Dim stringToSearchin As String
  
      For i = 0 To sourceDt.Rows.Count - 1
          siteNum = sourceDt.Rows(i).Item(0)
          categoryNum = sourceDt.Rows(i).Item(1)
          stringToSearchin = sourceDt.Rows(i).Item(3)
         Debug.WriteLine( linqQuery(siteNum, categoryNum, stringToSearchin) &amp; " " &amp;
             stringToSearchin)
      Next
  End Sub
  
  Private Function linqQuery(ByVal sitenum As Double, ByVal cat As Double,
                             ByVal stringToSearchIn As String) As String
  
      Dim query = From row In dtTosearch
                  Where (row.Field(Of Double?)("site") = sitenum And
                  row.Field(Of Double?)("category") = cat)
                  Order By row.Field(Of String)("token").Length Descending
                  Select New With {
                      .token = row.Field(Of String)("token")
           }
  
  
      For Each x In query
          If stringToSearchIn.Contains(x.token) Then
              Return x.token
              Exit Function
          End If
      Next
  
      Return ""
  End Function
  </code></pre>
  
  <p>Thank you very much for your time and consideration!</p>