How to perform a Like type query using WhereParameters and EntityDataSource

Question

In the code-behind I want to apply a dynamic where clause for the entitydatasource, but I want this where to be like and not equal. I have this code working which is equal I want an equivalence code that somehow translates this into a Like statement.

EntityDataSource1.WhereParameters.Add("Name", TypeCode.String, tbxSearch.Text);

Solution after reading Jupaol comment :

Xaml:

<WhereParameters>
   <asp:ControlParameter ControlID="tbxSearch" Name="Name" Type="String" />
</WhereParameters>

Code Behind: (on load event)

if (string.IsNullOrEmpty(tbxSearch.Text))
{
   this.EntityDataSource1.Where = "1=1";  //fetch all data if empty
}
else
{
   this.EntityDataSource1.Where = "it.Name like '%' + @Name + '%'"; //filter
}

Jupaol · Accepted Answer · 2012-07-19T09:31:52.420

2

In that code you are only adding a parameter, the place where you need to define your like comparison, is in the where clause

The code you posted can be translated into:

    <asp:EntityDataSource runat="server" ID="eds"
        .....
        Where="it.fname like '%' + @Name + '%'"
        <WhereParameters>
            <asp:ControlParameter ControlID="tbxSearch" Name="Name" DefaultValue="" />
        </WhereParameters>

To Add the where in code behind:

this.eds.Where = "it.fname like '%' + @Name + '%'";

Edit1:

For some reason, if I place the parameter declaration like yours (in code), it is not working, however if I place the parameter in the markup like this:

        <WhereParameters>
            <asp:ControlParameter ControlID="tbxSearch" Name="Name" Type="String" DefaultValue="" />
        </WhereParameters>

And in Page_Load

this.eds.Where = "it.fname like '%' + @Name + '%'";

It works

edited Jul 19 '12 at 09:31

answered Jul 19 '12 at 09:01

Jupaol

21,107
8
68
100

well i want to do it in the code behind... since i need to filter the datasource on a certain event... – rtp Jul 19 '12 at 09:04
Applies the same logic, since the `Where` property is a `string` you can assign the where criteria in the code behind, Check my updated response – Jupaol Jul 19 '12 at 09:08
i got this error: The query syntax is not valid. Near identifier 'abcd' – rtp Jul 19 '12 at 09:11
I just tested, it is working, remember that you should replace `fname` in the above code for your field. It should look like `it.YourField` – Jupaol Jul 19 '12 at 09:13
1

btw do i need to add anything in the xaml? like where parameter or something? i have this in the code behind. this.EntityDataSource1.Where = "it.Name like '%'" + tbxSearch.Text + "'%'"; hmmm :( – rtp Jul 19 '12 at 09:13

How to perform a Like type query using WhereParameters and EntityDataSource

1 Answers1