LINQ to SQL building dynamic query depending on selected checkboxlist selection

I have a checkboxlist with items in it populated by an SQL datasource.

In my code behind I am looping through the selected values of the check box list, and if the item is checked trying to add a 'like' clause to my linq query.

Please see attached code.

I am finding that the like clause instead of taking on the value of the selected item is taking the last value in the checkbox list. If I loop through this with out the query and just do a response.write li.value i get thr correct value from the checkboxlist  items.

Please someone help!

//Front end code:
 
    <asp:CheckBoxList ID="chkListProjects" runat="server" 
        DataSourceID="SqlDataSource1" DataTextField="ProjectName" 
        DataValueField="ProjectID" ></asp:CheckBoxList>
    
    
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT [ProjectID], [ProjectName] FROM [tbl_projects] WHERE ([ProjectCompanyID] = @ProjectCompanyID) ORDER BY [ProjectName]">
        <SelectParameters>
            <asp:QueryStringParameter Name="ProjectCompanyID" QueryStringField="CompanyID" 
                Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
 
//code behind:
 
var query = db.Tasks.Select(t => new
                                        {   t.TaskTime,
                                            t.TaskProjectIDs,
                                            t.TaskStatus,
                                            t.TaskBillingStatus });
 
        foreach (ListItem li in chkListProjects.Items)
        {
            if (li.Selected)
            {
                query = query.Where(t => t.TaskProjectIDs.Contains(li.Value));
            }
        }
 
        Response.Write(query.Sum(t => t.TaskTime));
 
// This produces the following SQL:
 
exec sp_executesql N'SELECT SUM([t0].[TaskTime]) AS [value]
FROM [dbo].[tasks] AS [t0]
WHERE ([t0].[TaskProjectIDs] LIKE @p0) AND ([t0].[TaskProjectIDs] LIKE @p1)',N'@p0 varchar(4),@p1 varchar(4)',@p0='%63%',@p1='%63%'
 
//However, the values of the selected items are 62 and 60, not 63/63...

Open in new window

iziiziAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

deadlyDevCommented:
I've had a good look at your code, and can't see anything obvious... Sometimes there may be site execution issues... try changing the line

query = query.Where(t => t.TaskProjectIDs.Contains(li.Value));

to this:

var val = li.Value;
query = query.Where(t => t.TaskProjectIDs.Contains(val));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iziiziAuthor Commented:
Amazing. I can't beleive that was it!

Just starting out .NET, and that is one annoyance I am not going to foget!

Thanks a million!
0
deadlyDevCommented:
It has to do with when your LINQ query is executed. Because it does not get executed when you are assigning the variable, it may not have access to the variable when it does get executed. By adding the local variable and copying the data, the local variable (as far as I am aware :P) gets included with the execution target site, and therefore is accessible when the query gets executed.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

iziiziAuthor Commented:
Thats good to know, thanks.
0
iziiziAuthor Commented:
deadlyDev, I have another question for you re this.

Do you know a way of changing this:

 foreach (ListItem li in chkListProjects.Items)
        {
            if (li.Selected)
            {
                query = query.Where(t => t.TaskProjectIDs.Contains(li.Value));
            }
        }

So that instead of producing and 'AND' oporator, it produces and 'OR' oporator in SQL?

I need it to be taskprojectid = <id1> OR taskprojectid = <id2> etc
0
deadlyDevCommented:
I do.... its quite complicated and I am about to go on lunch... when I get back will throw some code together for your... If you find LINQ at all interesting, I'm sure you'll enjoy the code that follows :)
0
iziiziAuthor Commented:
cool!

I did find this workaround:

http://talentgrouplabs.com/blog/archive/2007/11/25/dynamic-linq-queries--dynamic-where-clause-part-1.aspx

I just wonder if there is anything more elegant.

Thanks again
0
deadlyDevCommented:
Could I as you to ask this as a related question, it is quite complex and deals with new issues entirely...
0
iziiziAuthor Commented:
have added a related question.

cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.