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

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

Answer 2

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