Visual Studio 2008 SP1: EntityDataSource Where Clause
This post in one of a series of blog posts about SP1 of Visual Studio 2008 and .Net Framework 3.5.
In the previous post about the EntityDataSource I've showed how to use it in a very basic way, which meant that I didn't customize the EntityDataSource at all. In this post I'll show how to filter the displayed entities by adding a where clause expressed by an Entity SQL statement.
Taking from where I finished in the last post, I now want to filter the customers by their city, according to the city that was selected in a listbox control. To so that:
1. Add a listbox control that contains items for filtering the list of entities. For example, if we want to filter customers by city, we will add a listbox similar to:
<asp:ListBox ID="list" runat="server" AutoPostBack="true">
<asp:ListItem Text="Tel Aviv" Value="Tel Aviv" />
<asp:ListItem Text="Haifa" Value="Haifa" />
2. Go to the property grid of the EntityDataSource and locate the Where property. In this property, write an Entity SQL statement that may have parameters that start with @, or use the Expression Editor to do this. For example: it.City == @city
3. Using the Add Parameter button in the Expression Editor, add a new parameter with the same name as you used in the where clause, and choose the parameter source. You can choose to take the parameter value from a session value, from another control's value, and some other options. For this sample, choose Parameter Source = Control, and from the ControlID dropdown, select the listbox control ID.
Note: In the Beta of SP1 of Visual Studio 2008 and .Net Framework 3.5 there is a known bug that prevents you from using this Expression Editor and choose the parameter source, which will be fixed when we RTM. For now, after specifying the Where Expression, switch to the source view and add the parameters manually:
<asp:EntityDataSource ID="EntityDataSource" runat="server" ConnectionString="name=BankEntities"
ContextTypeName="" DefaultContainerName="BankEntities" EnableDelete="True" EnableInsert="True"
EnableUpdate="True" EntitySetName="Customers" Where="it.City == @city">
<asp:ControlParameter Name="city" Type="String" ControlID="list" PropertyName="SelectedValue" />
4. Run and browse to the page, and select one of the values in the listbox. The data shown in the gridview will be refreshed according to the where clause and the selected value of the listbox.