Using parameterized LIKE clauses in EntityDataSource

Question

I have a basic EntityDataSource related to a GridView. I have a TextBox over a GridView for search.

My goal: a) Custom "jon" types b) GridView filters, for example. Jonathan, Enjona, Jonas.

I saw several examples of how to add a parameterized LIKE clause to the Where property of my data source, however they all need the user to use a wildcard in the search bar (for example,% Jon instead of Jon). This is not acceptable for casual users, so I want to put the template 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 suggestion is WHERE LIKE N '% Jon%'.

Frustrated, the Where clause in EntityDataSource does not 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 gladly expect the default "to provide me with my" get everything "suggestion, that is, LIKE" %% ", but nothing is returned in my GridView.

Frustrated if I hard-coded the search result, for example. Where = "it.Name LIKE"% Jon% '", it works great.

Does anyone know how to do this?

Answer 1

I started working like this:

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

.NET 3.5 - , QueryExtender OnQueryCreated ( ) .NET >= 4.0.

Answer 2

where

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

,

Answer 3

You can accomplish this with QueryExtender. Something like that:

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