EntityDataSource replace * with % wildcard on queries

Question

I have an application that uses EntityDataSource in many places.

In the EDS, I manually build the Where clause based on user input from TextBox'es.

I would like the user to be able to enter "*" (asterisks) instead of "%" when querying data.

Is there an easy as using Entity SQL or the EDS itself to do a search/replace? I know I could actually change the TextBox after the data is entered, but when the user sees his text was changed from an * to a % I don't think he will understand.

I have tried using the T-SQL Replace command and doing something like this:

<asp:EntityDataSource ID="EDSParts" runat="server" 
    ConnectionString="name=TTEntities" DefaultContainerName="TTEntities"
    EnableFlattening="False" EntitySetName="Parts"
    OrderBy="it.ID DESC"
    Where ="(CASE
                WHEN (@PartNumber IS NOT NULL) THEN
                    it.[Number] LIKE REPLACE(@PartNumber, "*", "%")
                ELSE
                    it.[ID] IS NOT NULL
            END)">
    <WhereParameters>
        <asp:ControlParameter Name="PartNumber" Type="String"
            ControlID="txtPartNumberQuery" PropertyName="Text" />
    </WhereParameters>
</asp:EntityDataSource>

But I get a "Server tag is not well formed" message. I can't find an equivalent "replace" function in the Entity SQL reference....

Any ideas?

Answer 1

You can handle page postback and modify content of txtPartNumberQuery. EntityDataSource can work only with % (because it builds ESQL query) so you have to change * to % in your codebehind before you execute databinding.