linq with Contains issue

4 posts, 0 answers
  1. Hans
    Hans avatar
    49 posts
    Member since:
    Jul 2010

    Posted 18 Mar 2011 Link to this post

    Hello !

    I have this code
     
    IList  <TwoFieldsClass> tempList1 = (from c in _localContext.CustomerMasters where c.CUSTNMBR.Contains(searchCriteria) select new TwoFieldsClass 
    { Field1 = c.CUSTNMBR, Field2 = c.CUSTNAME }).OrderBy(f => f.Field1).ToList();

     

    for searchcriteria = "p"

    it generate the sql

     

     

     

    declare @p1 int
    set @p1=1
    exec sp_prepexec @p1 output,N'@p0 nchar(65)',N'SELECT a.[CUSTNMBR] AS COL1, a.[CUSTNAME] AS COL2 FROM [RM00101] a WHERE a.[CUSTNAME] LIKE ''%'' + @p0 + ''%'' ESCAPE ''\'' ',@p0=N'p'
    select @p1

     


    but I have no results. templist1 has zero records
    if I query direct in sql server

    select custnmbr,custname from rm000101 where custname like '%p%'
    i have a bunch of records

    So my question is why the linq doesn't return anything ?

    Regards,
    Hans

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 21 Mar 2011 Link to this post

    Hi Hans,

    I believe this could come from a char(x) or nchar(x) column; is CUSTNMBR mapped to such a column type? I have just delivered a change that will make avoid this issue for such column types. To work around this issue, please use  c.CUSTNMBR.Contains(searchCriteria+"%").

    Greetings,
    Thomas
    the Telerik team
  3. DevCraft banner
  4. Hans
    Hans avatar
    49 posts
    Member since:
    Jul 2010

    Posted 21 Mar 2011 Link to this post

    Hi !

    Thanks for your answer.
    The field is nvarchar
    I found another work around
    IList<TwoFieldsClass> tempList1 = (from c in _localContext.CustomerMasters where c.CUSTNMBR.Trim().Contains(searchCriteria)  select new TwoFieldsClass { Field1 = c.CUSTNMBR, Field2 = c.CUSTNAME }).OrderBy(f => f.Field1).ToList();

    So If i call trim befor contains, generate correct the sql.

    You said you just delivered a version thar fixed this error ? Where ? I just installed 2011 Q1.

    Hans
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Mar 2011 Link to this post

    Hello Hans,

    ok, this is a valid work around. The translated Trim() will cause the non-column type converter to be used which will pass the string correctly to the server. 
    As stated already, I've fixed the converter and the next patch will contain the changes so that Trim() would no longer be required.

    Greetings,
    Thomas
    the Telerik team
Back to Top