LinqDataSource: Filtering and binding to gridview

Question

the problem is not solved the way i wanted but i go ahead give the credit to : ŁukaszW.pl for his time and effort.

i am using gridview control and a linqdatasource and its all working fine and i have added the functionlity of searchingBySubject and i added WhereParameters and than binding my gridview (see the code below) but somehow its not returning any rows and i see i have number of rows based on what i am searching.

 protected void btnSearch_Click(object sender, EventArgs e)
 {    
   this.LinqDataSource1.WhereParameters["Subject"].DefaultValue = this.txtSubject.Text;
   this.GridView1.DataBind();
 }

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"  
         DataSourceID="LinqDataSource1"  
        EmptyDataText="There are no data records to display."> 
        <Columns> 
            <asp:BoundField DataField="UserID" HeaderText="UserID" ReadOnly="True"  
                SortExpression="UserID" /> 
            <asp:BoundField DataField="Username" HeaderText="Username"  
                SortExpression="Username" /> 
            <asp:BoundField DataField="FirstName" HeaderText="FirstName"  
                SortExpression="FirstName" /> 
            <asp:BoundField DataField="LastName" HeaderText="LastName"  
                SortExpression="LastName" /> 
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /> 
        </Columns> 
    </asp:GridView> 

     <asp:LinqDataSource ID="LinqDataSource1" runat="server"  
        ContextTypeName="MyDataContextDataContext" 
        onselecting="LinqDataSource_Selecting" > 
            <WhereParameters> 
               <asp:Parameter Name="Subject" />
            </WhereParameters> 
        </asp:LinqDataSource>



public List<Reporter> GetInquiries()
        {
            using (MyDataContextDataContext dc = conn.GetContext())
            {
                var loadAll = (from spName in dc.spReporter()
                               select spName);

                List<Reporter> reporterList = new List<Reporter>();

                foreach (var item in loadAll)
                {
                    reporterList.Add(new Reporter(item.Id, item.InqDate, item.Subject));
                }                

                return reporterList;
            }      

ERROR:

 The query results cannot be enumerated more than once

Answer 1

Assuming that you declare a LinqDataSource like this in your page:

<asp:LinqDataSource ID="LinqDataSource1"
                    runat="server"  
                    ContextTypeName="MyDataContext"
                    OnSelecting="LinqDataSource1_Selecting">
    <WhereParameters>
        <asp:ControlParameter Name="Subject"
                              ControlID="txtSubject"
                              PropertyName="Text"
                              Type="String" />
    </WhereParameters>
</asp:LinqDataSource>

Your LinqDataSource.Selecting event handler should roughly look like this:

public void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
     var db = new MyDataContext())
     var subjectFilter = e.WhereParameters("Subject");
     var reporters = from spName in db.spReporter()
                     where spName.Subject.Contains(subjectFilter)
                     select new Reporter(spName.Id, spName.InqDate, spName.Subject);
     e.Result = reporters;
}

Alternatively you could add 'Subject' as an input parameter to the Stored Procedure and to the filtering in the database. In that case the event handler would look like this:

public void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
     var db = new MyDataContext())
     var subjectFilter = e.WhereParameters("Subject");
     var reporters = from spName in db.spReporter(subjectFilter)
                     select new Reporter(spName.Id, spName.InqDate, spName.Subject);
     e.Result = reporters;
}

Related resources:

• LinqDataSource - Working with Stored Procedures