Optimize LINQ Query for use with jQuery Autocomplete
- by rockinthesixstring
I'm working on building an HTTPHandler that will serve up plain text for use with jQuery Autocomplete. I have it working now except for when I insert the first bit of text it does not take me to the right portion of the alphabet.
Example: If I enter Ne
my drop down returns
Nlabama
Arkansas
Notice the "N" from Ne and the "labama" from "Alabama"
As I type the third character New, then the jQuery returns the "N" section of the results.
My current code looks like this
Public Sub ProcessRequest(ByVal context As System.Web.HttpContext) Implements System.Web.IHttpHandler.ProcessRequest
' the page contenttype is plain text'
HttpContext.Current.Response.ContentType = "text/plain"
' store the querystring as a variable'
Dim qs As Nullable(Of Integer) = Integer.TryParse(HttpContext.Current.Request.QueryString("ID"), Nothing)
' use the RegionsDataContext'
Using RegionDC As New DAL.RegionsDataContext
'create a (q)uery variable'
Dim q As Object
' if the querystring PID is not blank'
' then we want to return results based on the PID'
If Not qs Is Nothing Then
' that fit within the Parent ID'
q = (From r In RegionDC.bt_Regions _
Where r.PID = qs _
Select r.Region).ToArray
' now we loop through the array'
' and write out the ressults'
For Each item In q
HttpContext.Current.Response.Write(item & vbCrLf)
Next
End If
End Using
End Sub
So where I'm at now is the fact that I stumbled on the "Part" portion of the Autocomplete method whereby I should only return information that is contained within the Part.
My question is, how would I implement this concept into my HTTPHandler without doing a fresh SQLQuery on every character change? IE: I do the SQL Query on the QueryString("ID"), and then on every subsequent load of the same ID, we just filter down the "Part".
http://www.example.com/ReturnRegions.axd?ID=[someID]&Part=[string]
