Adding LinqDataSource Where Parameters in Code-Behind

Question

Reading simple table using LinqDataSource. Table name is ZipCode with three columns: ZipCode, City, State). Read works fine with no filter, but when I add "Where" parameters to LinqDataSource, it fails in the GridView databind with an error, "No property or field "CityValue" exists in the type "ZipCode".

ASPX:

<asp:GridView ID="grdLDS" runat="server" AllowPaging="True" AllowSorting="True" 
    DataSourceID="ldsData" AutoGenerateColumns="False" >
    <Columns>
        <asp:BoundField DataField="ZIPCODE1" HeaderText="ZipCode" SortExpression="ZIPCODE1" />
        <asp:BoundField DataField="CITY" HeaderText="City" SortExpression="CITY" />
        <asp:BoundField DataField="STATE" HeaderText="State" SortExpression="STATE" />
    </Columns>
</asp:GridView>
<asp:LinqDataSource ID="ldsData" runat="server" ContextTypeName="LinqLayer.CommonDataDataContext"
        TableName="ZipCodes" OnSelecting="ldsData_Selecting" OnSelected="ldsData_Selected" >
</asp:LinqDataSource>

Code-Behind:

protected void ldsData_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    if (!cbxLDS.Checked)
    {
        e.Cancel = true;
        return;
    }

    ldsData.WhereParameters.Clear();
    StringBuilder where = new StringBuilder();
    if (!string.IsNullOrEmpty(txtFilterByZip.Text))
    {
        where.Append("ZIPCODE1.StartsWith(@ZipValue)");
        ldsData.WhereParameters.Add("@ZipValue", txtFilterByZip.Text);
    }
    if (!string.IsNullOrEmpty(txtFilterByCity.Text))
    {
        if (where.Length > 0) where.Append(" & ");
        where.Append("CITY.StartsWith(@CityValue)");
        ldsData.WhereParameters.Add("@CityValue", txtFilterByCity.Text);
    }

    if (!string.IsNullOrEmpty(txtFilterByState.Text))
    {
        if (where.Length > 0) where.Append(" & ");
        where.Append("STATE.StartsWith(@StateValue)");
        ldsData.WhereParameters.Add("@StateValue", txtFilterByState.Text);
    }

    ldsData.Where = where.ToString();
}

protected void ldsData_Selected(object sender, LinqDataSourceStatusEventArgs e)
{
    LDSRowCount = e.TotalRowCount;
}

private int RefreshLDSData()
{
    grdLDS.DataBind(); // <== CODE FAILS ON THIS LINE
    return LDSRowCount;     
}

private IEnumerable<ZipCode> FilterLDSData(IEnumerable<ZipCode> rows)
{
    return rows;
}

Answer 1

Thanks to Andy Robinson, here is the solution:

  protected void ldsData_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        if (!cbxLDS.Checked)
        {
            e.Cancel = true;
            return;
        }
        var dc = new CommonDataDataContext();
        var query = dc.ZipCodes.Select(r => new ZipData()
        {
            ZipCode = r.ZIPCODE1,
            City = r.CITY,
            State = r.STATE,
        });

        e.Result = ldsFilter(query);
    }

    private IQueryable<ZipData> ldsFilter(IQueryable<ZipData> rows)
    {
        if (!string.IsNullOrEmpty(txtFilterByZip.Text))
        {
            rows = rows.Where(r => r.ZipCode.StartsWith(txtFilterByZip.Text));
        }

        if (!string.IsNullOrEmpty(txtFilterByCity.Text))
        {
            rows = rows.Where(r => r.City.StartsWith(txtFilterByCity.Text));
        }

        if (!string.IsNullOrEmpty(txtFilterByState.Text))
        {
            rows = rows.Where(r => r.State.StartsWith(txtFilterByState.Text));
        }

        return rows;
    }

lds_Data_Selecting event provides the structure of the query, and the ldsFilter method does the dynamic filtering. This method must accept and return an iQueryable.

Answer 2

you can try like this in where parameter with linqdatasource..

NOte :this is just example how to use the where parameter using linqdatasource...

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

pls go through this link for more info