LinqDataSource - filtering for null values

Question

I am writing an ASP.Net web application. I have listview, it's datasource is a LinqDataSource. In my database, I have a staff table and I am trying filter for records by their team using a dropdownlist. This works fine, until I select "All" in the dropdownlist. It returns all staff except for the ones where the teamID is null. How can I return the records where teamID is null?

This is my code:

<asp:ListView ID="ListView1" runat="server" DataSourceID="ldsStaff" DataKeyNames="staffID">

    <LayoutTemplate>
<table>
  <tr>
     <th>Name</th>
     <th>Team</th>
  </tr>
  <tr>
     <td>&nbsp</td>
     <td><asp:DropDownList ID="ddlTeamFilter" runat="server" DataSourceID="ldsTeams" DataTextField="Team" DataValueField="TeamID" AppendDataBoundItems="true" AutoPostBack="true">
        <asp:ListItem Text="[All]" Value=""></asp:ListItem>
         </asp:DropDownList>
     </td>
  </tr>
  <tr ID="itemPlaceHolder" runat="server"></tr>
</table>
</LayoutTemplate>
....
</asp:Listview>

<asp:LinqDataSource ID="ldsStaff" runat="server" 
    ContextTypeName="ProjectDatabase.ProjectDatabaseUsersDataContext" OrderBy="name" 
    TableName="Staffs" EnableUpdate="True" Where="inService == @inService &amp;&amp; TeamID == @TeamID">
    <WhereParameters>
        <asp:Parameter DefaultValue="true" Name="inService" Type="Boolean" />
        <asp:ControlParameter ControlID="ctl00$ContentPlaceHolder1$ListView1$ddlTeamFilter" Name="TeamID" Type="Int32" PropertyName="SelectedValue"/>
     </WhereParameters>
</asp:LinqDataSource>

In my code behind I handle the LinqDataSource selecting event:

Private Sub ldsStaff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles ldsStaff.Selecting
    Dim ddl As DropDownList = CType(ListView1.FindControlRecursive("ddlTeamFilter"), DropDownList)
    If ddl.SelectedValue = "" Then
        e.WhereParameters.Remove("TeamID")
    End If

I thought that if I removed the whereparameter when the dropdownlist is "All" it would force it to return all the records, but it doesn't work.

Please help!

Thanks, Emma

Answer 1

If you are handling the selecting event anyway I would remove the Where clause in your datasource all together and handle everything in the selecting event setting the e.Result value to your linq query.

Then in your ddlTeamFilter rebind the listview on change.

Hope this helps.