Take the 2-minute tour ×

I have a basic EntityDataSource bound to a GridView. I have TextBox above the GridView for searching.

My goal: a) User types "jon" b) GridView is filtered, e.g. "Jonathan","Enjona","Jonas".

I have seen several examples of how to add a parameterised LIKE clause to the Where property of my data source, however they all need the user to use a wildcard in the search string (e.g. %Jon instead of Jon). This is not really acceptable for lay users, so I want to put the wildcard in the Where clause instead.

The syntax in SQL is obvious: SELECT Name FROM Names WHERE Name LIKE N'%@p1%'

In other words, if @p1='Jon', my WHERE clause is LIKE N'%Jon%'.

Frustratingly, the Where clause in the EntityDataSource doesn't seem to work this way. In other words, the following does not work:

<asp:EntityDataSource ID="edsNames" runat="server" 
    DefaultContainerName="SalesEntities" EntitySetName="Names"
    OrderBy="it.Name" Where="it.Name LIKE '%@p1%'">
        <asp:ControlParameter ControlID="txtFilter" Name="p1" 
            PropertyName="Text" Type="String" DefaultValue="" />

I would happily expect the default value of "" to provide me with my "get everything" clause, i.e. LIKE '%%', but nothing is returned in my GridView.

Frustratingly, if I hard-code a search result, e.g. Where="it.Name LIKE '%Jon%'", it works perfectly.

Does anyone know a way to do this?

share|improve this question
add comment

3 Answers

I got it working like this:

Where="it.Name like ('%' + @p1 + '%')"

This works with .NET 3.5 - I think QueryExtender plus OnQueryCreated (this would also have been an option for adapting the query before it gets run) are only available in .NET >= 4.0.

share|improve this answer
add comment

You can accomplish this using a QueryExtender. Something along these lines:

  <asp:EntityDataSource ID="edsEmployeeSearch" runat="server" 
    ConnectionString="name=HrProtoEntities" DefaultContainerName="HrProtoEntities" 
    EnableFlattening="False" EntitySetName="People" Include="Employee" 
    AutoGenerateWhereClause="True" >
<asp:QueryExtender ID="QueryExtender1" runat="server" TargetControlID="edsEmployeeSearch">
    <asp:SearchExpression SearchType="Contains" DataFields="LastName">
        <asp:ControlParameter ControlID="txtSearch" />
share|improve this answer
add comment

you could try writing the where condition as follows

Where="@p1 IS NULL OR it.Name LIKE '%@p1%'"  

just in case it is seen as a null value

share|improve this answer
add comment

Your Answer


