LinqDataSource: Filtering and binding to gridview

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

Method 2

About the problem, I don’t see any connection between your GetInquiries method and LinqDataSource. Thats first, second is that even if there will be connection it will not work if you are returning list, and not IQueriable object…

To better uderstand binding with LinqDataSource read this Scott Gu’s article

Also I want to show you that your GetInquiries method could be simplified to this form:

    public List<Reporter> GetInquiries()
    {
        using (MyDataContextDataContext dc = conn.GetContext())
        {
            return (from spName in dc.spReporter()
                    select new Reporter(spName.Id, spName.InqDate, spName.Subject)).ToList()

        }      

—- EDITED —-

Example of alternative solution:

    public List<Reporter> GetInquiries(string subject)
    {
        using (MyDataContextDataContext dc = conn.GetContext())
        {
            return (from spName in dc.spReporter()
                    select new Reporter(spName.Id, spName.InqDate, spName.Subject
                    where spName.Subject == subject)).ToList()

        }  
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {    
       GridView1.DataSource = GetInquiries(txtSubject.Text);
       GridView1.DataBind();
    }

———- EDIT ————-

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

public void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{ 
    ReporterRepository reporterRepo = new ReporterRepository();
    e.Result = reporterRepo.GetInquiries();    
}

public IQueryable<Reporter> GetInquiries()
{
    return from spName in dc.spReporter()
           select new Reporter(spName.Id, spName.InqDate, spName.Subject);
}     

<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>

Regards

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply