locked
linqdatasource and the contains method

    Question

  • Hi,

    I'm trying to figure out how to insert what used to be a LIKE clause on my linqdatasource's where clause.  Basically, I have a text field that users will type in a search term that will in turn filter the results in a gridview.  I've tried doing the standard Like "%@search_term%" only to realize that this is now linq and it doesn't seem to have the LIKE keyword.

    I then came across the contains keyword, but can't seem to get it to work.  When I do the following, I get a "No applicable method 'Contains' exists in type 'String' " error.

    <asp:LinqDataSource ID="LinqDataSource1" runat="server"
                    ContextTypeName="CG.CGDataContext" OrderBy="LastName"
                    TableName="Accounts"
                    Where="RepID == @RepID && LastName.Contains(@search_text)"
                    OnInit="LinqDataSource_Init">
                    <whereparameters>
                        <asp:querystringparameter DefaultValue="000099" Name="RepID" QueryStringField="rep_id" Type="String" />
                        <asp:controlparameter ControlID="search_text" Name="search_text" PropertyName="Text" Type="String" />
                    </whereparameters>
                </asp:LinqDataSource>

    I've been trying various things all morning.  Do I need to abandon the linqdatasource and just write custom linq code in my codebehind?

    Thanks,
    Trey
    Thursday, August 09, 2007 11:04 PM

Answers

  • When the parser resolves the 'Contains' method it ensures that the parameter types match the method's argument types.  The problem is that the null is being treated as an Object instead of a String, so it has problems resolving the method.

     

    To avoid having the empty string treated as null you must set the ConvertEmptyStringToNull (default=true) property on the parameter to false.  There should be no problem using an empty string.

    Monday, August 13, 2007 3:55 PM
  • The contains method works fine with a real LINQ query. In the case of Like equivalents, LINQ to SQL translates StartsWith, EndsWith and Contains on a string to the appropriate LIKE TSQL method. It appears that the LinqDataSource must be the culprit here.

     

    I am concerned that people will confuse the String.Contains method with the new IQueryable<T>.Contains which is used for the "IN" clause. see http://devauthority.com/blogs/jwooley/archive/2007/08/06/69922.aspx.

     

    Jim Wooley

    http://www.LinqInAction.net

    Friday, August 10, 2007 1:59 AM
  • Note that if a query is too complex to specify through the declarative properties, LinqDataSource does provide a Selecting event where you can perform your own linq query.

     

    OnSelecting(object sender, LinqDataSourceSelectEventArgs args) {

        Northwind nw = new Northwind();

        args.Result = from p in nw.Products ... select p;

    }

    Friday, August 10, 2007 5:05 PM

All replies

  • The contains method works fine with a real LINQ query. In the case of Like equivalents, LINQ to SQL translates StartsWith, EndsWith and Contains on a string to the appropriate LIKE TSQL method. It appears that the LinqDataSource must be the culprit here.

     

    I am concerned that people will confuse the String.Contains method with the new IQueryable<T>.Contains which is used for the "IN" clause. see http://devauthority.com/blogs/jwooley/archive/2007/08/06/69922.aspx.

     

    Jim Wooley

    http://www.LinqInAction.net

    Friday, August 10, 2007 1:59 AM
  •  

    Thanks for the response... however I am a newbie in all this and it does work for "=" but not for 'Like" and do not understand whether what was proposed would, could work and or how ... is there a solution for general ASP.Net in the following siutation where I am trying to do a 'like' LastName search.... (the example uses = and works for an exact match)

     

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"

    ConnectionString="<%$ ConnectionStringsStick out tongueubs %>"

    SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE [LastName] = @LastName ORDER BY [FirstName]ASC">

    <SelectParameters>

    <asp:QueryStringParameter Name="LastName" QueryStringField="NameSearch_Result" />

    </SelectParameters>

    </asp:SqlDataSource>

     

    ??

     

    Thank you.

    Friday, August 10, 2007 3:56 PM
  • LinqDataSource is using a parser to convert the declarative string expressions into linq expression trees.  As a result, the syntax is a little different from actual linq and only a subset of linq is supported declaratively.  The C# team has released this parser with documentation as a beta2 sample here: http://download.microsoft.com/download/8/0/e/80e3f901-9bcc-4b8b-ba66-c49e24559fbc/vcs_samples_vs2008_beta2_02.exe.  Look at DynamicQuery\Dynamic Expressions.html for a great starting point to working with the LinqDataSource declarative expressions.

     

    The parser only allows method calls on a predefined set of types.  The Enumerable.Contains extension method is not accessible, but you can access methods off the String type, so String.Contains should be accessible.  You should be able to do something like the following:

     

    Where="LastName.Contains(@LastNameFilter)"

     

    Friday, August 10, 2007 4:59 PM
  • Note that if a query is too complex to specify through the declarative properties, LinqDataSource does provide a Selecting event where you can perform your own linq query.

     

    OnSelecting(object sender, LinqDataSourceSelectEventArgs args) {

        Northwind nw = new Northwind();

        args.Result = from p in nw.Products ... select p;

    }

    Friday, August 10, 2007 5:05 PM
  • I found that if I use


    Where="LastName.Contains(@SearchText)"

    where SearchText is not empty or null then it's ok but if SearchText is empty or null I get a 'No applicable method 'Contains' exists in type 'String' error. I don't know why.


    Saturday, August 11, 2007 8:12 AM
  • When the parser resolves the 'Contains' method it ensures that the parameter types match the method's argument types.  The problem is that the null is being treated as an Object instead of a String, so it has problems resolving the method.

     

    To avoid having the empty string treated as null you must set the ConvertEmptyStringToNull (default=true) property on the parameter to false.  There should be no problem using an empty string.

    Monday, August 13, 2007 3:55 PM
  • Very nice Zen and Chen!

    It turned out that I was having the same problem and once I set the ConvertEmptyStringToNull to false, I was in business. 

    Thanks for the help everyone!
    Monday, August 13, 2007 5:14 PM
  • Ok, try

    Contains("" +@search_text)

    Tuesday, April 21, 2009 1:42 PM
  • In the first posting here treybean writes "Basically, I have a text field that users will type in a search term that will in turn filter the results in a gridview. ".  Basically I want to do the same thing.  On top of my page, I have a textbox and a button.  The user will enter in a value in the text box, click on the button and the listview will be populated with rows that contain the value in the textbox.  I have the "Where="LastName.Contains(@SearchText)" working, but I do not know how to tie in the button to filling in the listview.  I see "OnInit="LinqDataSource_Init", I think that this is where this is done, but there is no code example here for LinqDataSource_Init.  I am fairly new to all this, I am using VS 2008 website, linq to sql, c# 3.5.  Also, how to have the viewlist initially populated with all the records and how to prevent a search of nothing having been entered in the textbox.  Do I need to do away with the linqdatasource or can I do all this using it. I have been searching all over the place and have come across this link...http://weblogs.asp.net/rajbk/archive/2008/01/31/linqdatasource-exceptions.aspx, but being that I am not "selecting" from a field in the listview, does "Where="DummyID == @DummyID" onselecting="LinqDataSource2_Selecting" >" have any significance for my project? Thanks.
    Thursday, December 31, 2009 11:59 PM
  • mobilelou I have the same problem and I do this hope it helps

     

     

     

    protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    
      {
    
       SomethingDataContext ctx = new SomethingDataContext();
    IQueryable<Student> query = from s in ctx.Students where SqlMethods.Like(s.Group.Name, String.Format("%{0}%", searchGroup)) select s; e.Result = query; }

     

    that's for my Selecting event and then I have my TextBox and Button and the click event of the button

    protected void bttSearchGroup_Click(object sender, EventArgs e)
      {
       searchGroup = txtSearchGroup.Text; //TextBox
    
       LinqDataSource1.RaiseViewChanged();
       LinqDataSource1e.DataBind();
      }

    I don't really know if it's really necessary the DataBind but it works on my computer

     

     

    Tuesday, July 06, 2010 8:39 PM