Using parameterised LIKE clauses in an EntityDataSource

Question

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" 
    ConnectionString="name=SalesEntities" 
    DefaultContainerName="SalesEntities" EntitySetName="Names"
    OrderBy="it.Name" Where="it.Name LIKE '%@p1%'">
    <WhereParameters>
        <asp:ControlParameter ControlID="txtFilter" Name="p1" 
            PropertyName="Text" Type="String" DefaultValue="" />
    </WhereParameters>
</asp:EntityDataSource>

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?

Answer 1

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

Answer 2

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.

Answer 3

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:EntityDataSource>
<asp:QueryExtender ID="QueryExtender1" runat="server" TargetControlID="edsEmployeeSearch">
    <asp:SearchExpression SearchType="Contains" DataFields="LastName">
        <asp:ControlParameter ControlID="txtSearch" />
    </asp:SearchExpression>
</asp:QueryExtender>