LINQ query for values in the Dropdownlist(for all items)

[K V Sekhar]

Hi all, I have a dropdownlist, in that i have items All item1 item2 item2 . ..... How can i write linq query if user is selects option'All' from dropdownlist. Like for all items in the dropdownlist. var query = from temp in DataContext1.TableName where temp.colName.Equals(ddl_items.Items[1].Text)|| temp.colName.Equals(ddl_items.Items[2].Text) || temp.colName.Equals(ddl_items.Items[3].Text) || .....|| temp.colName.Equals(ddl_items.Items[last item].Text) select temp; //I know this query was wrong. But it is easy for you to understand my requirement. So. Please suggest me how can i write the query for values in dropdownlist Thanks in advance.

[Howard Richards]

I assume you are using LinqDataSource for the select? I override the LinqDataSource.Selecting event and write the query there, e.g. if the ALL value has a value of 'all' and others have numeric value..

Sub LinqDataSource1_Selecting(...)
'Get value from dropdownlist
Dim value = Me.DropdownList.SelectedValue

'Build query for ALL
Dim db = New MyDataContext()
Dim result = from x in db.MyTable _
Select x

'Is value set?
If value<>"all" Then
'convert to int
dim valueInt = Convert.ToInt32(value)
result = from x in result _
where x.MyField = valueInt _
select x
End if

e.Result = result
End Sub

'Howard

[K V Sekhar]

Thanks Howard Richards, I am not using any LinqDataSource. Also the Items in Dropdown_Ads are populated at runtime. I am populating The dropdown_Ads based on Diffrent DropDownControls(ddl_Country,ddl_State,txt_FromDate,txt_ToDate,ddl_district,ddl_specialization...) So the items in the Dropdown_Ads changes based on seleced country,state,district,specialization,fromdate,todate. Finally i am binding 'All' option to Dropdown_Ads. All itm1 itm2 itm3 itm4 If user selects 'All' option from Dropdown_Ads then I have to display Data from table_Ads in which column of table contains values in (itm1,itm2,itm3,itm4) . This my requirement.I hope you understand the requirement.

[Howard Richards]

No, sorry I don't understand. You are doing a query with ALL values of itm1,itm2.. ? Are they always querying the same table? the same columns?

'Howard

[K V Sekhar]

No Problem. I got it. Here is the query. string str = "SELECT name,location FROM users WHERE user = 'sekhar' AND location IN("; //For dropdownlist for(int i=1; i{ if( i== ddl_location.Items.Count-1) //last item str = str+ddl_location.Items[i].Value +")"; else str = str+ddl_location.Items[i].Value +","; } var query = DataContext1.ExecuteQuery(str).ToList(); gridview1.DataSource = query; gridview1.DataBind(); /////////// Above all works fine/////////////// but when i try to use that 'query' again i am getting error :The Query Result can't be enumerated more than once. Here is what i did : var query = DataContext1.ExecuteQuery(str).tolist(); if(query.Count()>0) { DataTable dt = ConvertIntoDataTable(DataContext1,query);//Converting into DataTable, user defined method. Here in param 'query' i am getting 'Zero records' MSG:The Query Result can't be enumerated more than once. gridview1.DataSource = dt; gridview1.DataBind(); } ////////////////// I tried by using var sample = query.GetResults<>();//Actually i am not getting option 'GetResults' when i am typing. Copilation Error.

modified on Friday, August 22, 2008 6:13 AM

[ChandraRam]

If by "ALL" you mean you want to actually select all the rows, why not omit that portion of the query? Like: string str = "SELECT name,location FROM users WHERE user = 'sekhar'"; You add the "IN" clause only when it is not "ALL"... HTH

[Howard Richards]

OK I think I understand.. user can select multiple elements? The IN(...) clause in SQL can be created using an array or list and .Contains method

// list of locations to match - use your dropdown for this
var locations = new String() {"aa", "bb", "cc"};

// query
var query = from x in db.Table
where items.Contains(x.Location)
&& name = "Sekhar"
select x.Name, x.Item

'Howard