title: writing efficient query to search a dataset index record with linq
body: <p>i am replacing old binary search functions with linq queries in my project.
  i cant figure out why linq queries is so slow compare to the binary search method (must have been doing something wrong).</p>
  
  <p><strong>the execute of this part of the program takes 2-3 seconds with binary search compare to almost 60 sec with the linq searches i have written.</strong></p>
  
  <p>basically what i am trying to do is return index of dataset record.</p>
  
  <p>i am looping  trough datatable with 5000 records and passing the primary key of the datatable to the linq query \ binary search function in order to search that primary key in a global dataset and return the index of the record.</p>
  
  <p>i must have been doing something wrong i will be greatful for your advice how to write this query more efficent.</p>
  
  <p><strong>the 2 functions of the linq query:</strong></p>
  
  <pre><code> Public Function existInDs(ByVal spcall As String) As Boolean
              'check if exist
              Dim spIsExist As Boolean = db.ReadDataSet.Tables(0).AsEnumerable().Any(Function(x) x.Field(Of String)("SpCall") = spcall)
              Return spIsExist
          End Function
  
          Private Function getIndexDsWithLinq(ByVal sp As Integer) As Integer
  
              Dim SpCall = CType(sp, String)
  
              If existInDs(SpCall) = False Then
                  Return -1
              Else
                  Dim index As Integer = db.ReadDataSet.Tables(0).AsEnumerable().Where(Function(x) x.Field(Of String)("SpCall") = SpCall) _
                             .Select(Function(x) db.ReadDataSet.Tables(0).Rows.IndexOf(x)).Distinct.SingleOrDefault()
                  Return index
              End If
  
          End Function
  </code></pre>
  
  <p><strong>i have also tried that:</strong></p>
  
  <pre><code>  Private Function getIndexDsWithLinq(ByVal sp As Integer) As Integer
      Dim SpCall = CType(sp, String)
      Dim columnIndex As Integer
      Dim dr() As DataRow
      dr = Me.db.ReadDataSet.Tables(0).Select("SpCall='" &amp; SpCall &amp; "'")
      If dr.Length &gt; 0 Then
          columnIndex = Me.db.ReadDataSet.Tables(0).AsEnumerable().Where(Function(x) x.Field(Of String)("SpCall") = sp AndAlso x.Field(Of Date?)("Changed").HasValue).Select(Function(x) Me.db.ReadDataSet.Tables(0).Rows.IndexOf(x)).Distinct.SingleOrDefault()
          Return columnIndex
      Else
          Return -1
      End If
  End Function
  </code></pre>
  
  <p><strong>that is the(classic) binary search function:</strong></p>
  
  <pre><code>  Private Function Searchbinary(ByVal SpCall As Integer) As Integer
  
      Dim lower As Long = 0
      Dim upper As Long = db.ReadDataSet.Tables(0).Rows.Count - 1
      Dim middle As Long
      Dim rowNum As Integer
  
      Do
          middle = (lower + upper) / 2
          If SpCall &lt; CType(db.ReadDataSet.Tables(0).Rows(middle).Item("SpCall"), Integer) Then
              upper = middle - 1
          Else
              lower = middle + 1
          End If
  
      Loop While CType(db.ReadDataSet.Tables(0).Rows(middle).Item("SpCall"), Integer) &lt;&gt; SpCall And lower &lt;= upper
  
      If CType(db.ReadDataSet.Tables(0).Rows(middle).Item("SpCall"), Integer) = SpCall Then
          rowNum = middle
  
      Else
          rowNum = -1
      End If
  
      Return rowNum
  
  End Function
  </code></pre>