Efficient Search function with Linq to SQL
- by Bayonian
Hi,
I'm using VB.NET and Linq to SQL. I have a table with thousands of rows and growing. Right now I'm using .Contains() in the Where clause to perform the query. Below is my search function :
Public Shared Function DemoSearchFunction(ByVal keyword As String) As DataTable
Dim db As New BibleDataClassesDataContext()
Dim query = From b In db.khmer_books _
From ch In db.khmer_chapters _
From v In db.testing_khmers _
Where v.t_v.Contains(keyword) And ch.kh_book_id = b.kh_b_id And v.t_chid = ch.kh_ch_id _
Select b.kh_b_id, b.kh_b_title, ch.kh_ch_id, ch.kh_ch_number, v.t_id, v.t_vn, v.t_v
Dim dtDataTableOne = New DataTable("dtOne")
dtDataTableOne.Columns.Add("bid", GetType(Integer))
dtDataTableOne.Columns.Add("btitle", GetType(String))
dtDataTableOne.Columns.Add("chid", GetType(Integer))
dtDataTableOne.Columns.Add("chn", GetType(Integer))
dtDataTableOne.Columns.Add("vid", GetType(Integer))
dtDataTableOne.Columns.Add("vn", GetType(Integer))
dtDataTableOne.Columns.Add("verse", GetType(String))
For Each r In query
dtDataTableOne.Rows.Add(New Object() {r.kh_b_id, r.kh_b_title, r.kh_ch_id, r.kh_ch_number, r.t_id, r.t_vn, r.t_v})
Next
Return dtDataTableOne
End Function
I would like to know other methods for doing efficient search using Linq to SQL. Thanks.
