Dynamically query a collection

scottsanpedro

Member
Joined
Jan 24, 2009
Messages
21
Programming Experience
1-3
Hi all, I hope you can help.
I have an object called tblTask (From linq query to the object) which has fields such as T3_1, T3_2, T3_3, T3_4. These are questions that need to be filled in by the user.

I want to run a dynamic query to find out if any information is in that field or not.

The idea is that there are x questions and I need to know which ones have been completed. i.e. 6/10 complete.

I have other tables called tblTask1, tblTask2, tblTask3 etc. with different amount of questions.

I want to run a query such as

Code:
Dim res = FROM x IN tblTask{Dynamic Number}
WHERE T{Dynamic Number}_{Dynamic Number} IS NOT NULL
If res.Count > 0 THEN
myCount += 1
End If
OR
Via a collection of some sort
Any ideas?

Thanks in advance. Scott

BTW I have already done it BUT this way contacts the database each time. I want it to only query the object in memory. This was 18 questions, and 33 different reocrds. Over 590 calls to the DB. A tad slow!

Code:
For t = 1 To totTaskInRecord.Count
  Dim sql As String
  sql = String.Format("Select T{0}_UniqueID FROM tblTask{0} WHERE T{0}_{1} IS NOT NULL AND T{0}_ItemNo = {2}", iTaskNo, t, rec.T3_ItemNo)
  Dim ret = dbx.ExecuteStoreQuery(Of Integer)(sql)
  If ret.Count > 0 Then
  iTotalComplete += 1
  End If
 Next
 

ss7thirty

Well-known member
Joined
Jun 14, 2005
Messages
455
Location
New Jersey, US
Programming Experience
5-10
Perhaps you should try using a dataset. It pulls all those tables into memory and you can work with an offline copy of the database.
 

scottsanpedro

Member
Joined
Jan 24, 2009
Messages
21
Programming Experience
1-3
I scratch and scratch my head over this. My final solution, which I'm not overcome with joy over, was to create a computed column in SQL Server that checked each field and created a score.
Its pretty ugly (in fact way too ugly) but actually works.

(((((((((((((((((case isnull([T3_1],'') when '' then (0) else (1) end+case isnull([T3_2],'') when '' then (0) else (1) end)+case isnull([T3_3],'') when '' then (0) else (1) end)+case isnull([T3_4],'') when '' then (0) else (1) end)+case isnull([T3_5],'') when '' then (0) else (1) end)+case isnull([T3_6],'') when '' then (0) else (1) end)+case isnull([T3_7],'') when '' then (0) else (1) end)+case isnull([T3_8],'') when '' then (0) else (1) end)+case isnull([T3_9],'') when '' then (0) else (1) end)+case isnull([T3_10],'') when '' then (0) else (1) end)+case isnull([T3_11],'') when '' then (0) else (1) end)+case isnull([T3_12],'') when '' then (0) else (1) end)+case isnull([T3_13],'') when '' then (0) else (1) end)+case isnull([T3_14],'') when '' then (0) else (1) end)+case isnull([T3_15],'') when '' then (0) else (1) end)+case isnull([T3_16],'') when '' then (0) else (1) end)+case isnull([T3_17],'') when '' then (0) else (1) end)+case isnull([T3_18],'') when '' then (0) else (1) end)

I then used the column in a normal LINQ query and added it to the result.
I might well go down the dataset route to get the data in and may be I'm able to run a dynamic query on it.
Thanks for your comments.
Scott
 
Top Bottom