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

Enrico Campidoglio · Answer 1 · 2010-08-03T21:10:36.140

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

Łukasz W. · Accepted Answer · 2010-07-28T18:06:48.793

0

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

edited Jul 28 '10 at 18:06

answered Jul 28 '10 at 16:58

Łukasz W.

9,538
5
38
63

Thanks for the suggestions... what you mean returning IQueriable object, can you please show me few lines? or i was thinking should i add where clause in my GetInquiries() or it will take care automatically since i am adding where clause in the linqdatasource? – Nick Kahn Jul 28 '10 at 17:00
Right now I think, that there is no need to use LinqDataSource... Just use GetInquiries method to get list of Reporters and then assign it to your gridView as normal DataSource. Example above. – Łukasz W. Jul 28 '10 at 17:03
oops, sorry i have update (LinqDataSource_Selecting) the question – Nick Kahn Jul 28 '10 at 17:05
Haha.. now it make more sense.. try then return the GetInquiries which returns pure IQuerable (without ToList method) or if it won't work try my alternative solution :) – Łukasz W. Jul 28 '10 at 17:08
i have thought of using the alternative solution you presented before posting this question but later i thought it will be too much because i have like 7 items to filter like (by date, by subject, by status.....) – Nick Kahn Jul 28 '10 at 17:10
I like to use ObjectDataSource instead of LinqDataSource... It's quite similar to what's you're doing : I create a list of objects manually... – Julien N Jul 28 '10 at 17:11
But ObjectDataSource won't dynamicly work with database. What about returning IQuerable as a result of GetInquiries? Any results? – Łukasz W. Jul 28 '10 at 17:17
@KukaszW: i have created IQueryable (i upate my question) but how will call that? – Nick Kahn Jul 28 '10 at 17:29
Just like you do it on the beginning, return it as e.Result.. I guess it should work then ;) – Łukasz W. Jul 28 '10 at 17:36
i update my question, please see and its throwing me some errors, may be i am not calling the way it should call. – Nick Kahn Jul 28 '10 at 17:59
Excuse me, but in which line did you get it? – Łukasz W. Jul 28 '10 at 18:18
sory man this is driving me crazy now... i have update my question, getting error on IQueryable GetInquiries() – Nick Kahn Jul 28 '10 at 18:23
I was just about to suggest it.. And what, now it works okay? – Łukasz W. Jul 28 '10 at 18:30
oh boy, new error now: "The query results cannot be enumerated more than once" – Nick Kahn Jul 28 '10 at 18:51
k so i fixed this by returning reporterRepo.GetInquiries().ToList(); and it run the app but when i search by txtSubject, no change at all shows the same amount of data...egggrrrrrr – Nick Kahn Jul 28 '10 at 18:57
I'm not sure if we doing it right :P Imo it's not prepared to be used this way. It will be better if you will use alternative solution I've presented at the begining... – Łukasz W. Jul 29 '10 at 06:57

LinqDataSource: Filtering and binding to gridview

2 Answers2

Linked