none
Bug with string concatenation RRS feed

  • Question

  • consider this

    predicate = predicate.And(p => (

              p.FirstName ?? String.Empty + " " +

              p.LastName ?? String.Empty + " " +

              p.CompanyName ?? String.Empty).Trim().Contains(temp));

    generates this god awful sql...which actually does not work. You only get matches for FirstName or CompanyName...matches for LastName are not found

    Is there a code-first workaround (i.e. i dont want have to create assets like functions on sql server)

    CASE 

    WHEN ([Extent3].[FIRST_NM] IS NULL) 

    THEN 

    CASE 

    WHEN (@p__linq__0 + [Extent3].[LAST_NM] IS NULL) 

    THEN 

    CASE 

    WHEN (@p__linq__1 + [Extent3].[COMPANY_NM] IS NULL) 

    THEN @p__linq__2 

    ELSE @p__linq__1 + [Extent3].[COMPANY_NM] 

    END 

    ELSE @p__linq__0 + [Extent3].[LAST_NM] 

    END 

    ELSE [Extent3].[FIRST_NM] 

    END LIKE @p__linq__3 ESCAPE N''~'')

    Wednesday, June 8, 2011 9:07 AM

Answers

  • Got it. But this is Fugly (so is the sql). String concat needs some serious attention!

    .Where(c => (
                 
    ((c.FirstName ?? String.Empty).Length > 0 && (c.FirstName ?? String.Empty).Contains(searchText)) ||
                   
    ((c.LastName ?? String.Empty).Length > 0 && (c.LastName ?? String.Empty).Contains(searchText)) ||
                   
    ((c.CompanyName ?? String.Empty).Length > 0 && (c.CompanyName ?? String.Empty).Contains(searchText))
                   
    )  
                 
    )

    yields

    AND (((( CAST(LEN(CASE WHEN ([Extent3].[FIRST_NM] IS NULL) THEN @p__linq__0 ELSE [Extent3].[FIRST_NM] END) AS int)) > 0) 
            AND
    (CASE WHEN ([Extent3].[FIRST_NM] IS NULL) THEN @p__linq__1 ELSE [Extent3].[FIRST_NM] END LIKE @p__linq__2 ESCAPE N''~''))
            OR
    ((( CAST(LEN(CASE WHEN ([Extent3].[LAST_NM] IS NULL) THEN @p__linq__3 ELSE [Extent3].[LAST_NM] END) AS int)) > 0)
            AND
    (CASE WHEN ([Extent3].[LAST_NM] IS NULL) THEN @p__linq__4 ELSE [Extent3].[LAST_NM] END LIKE @p__linq__5 ESCAPE N''~''))
            OR
    ((( CAST(LEN(CASE WHEN ([Extent3].[COMPANY_NM] IS NULL)
            THEN
    @p__linq__6 ELSE [Extent3].[COMPANY_NM] END) AS int)) > 0)
            AND
    (CASE WHEN ([Extent3].[COMPANY_NM] IS NULL) THEN @p__linq__7 ELSE [Extent3].[COMPANY_NM] END LIKE @p__linq__8 ESCAPE N''~'')))
    Friday, June 17, 2011 11:03 AM

All replies

  • Hi,

    Have you tried with parenthesis ? I 'm not 100% sure what you expect but could it be that the :: and + operator precedence is not the one you expect ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, June 8, 2011 6:42 PM
  • Hi,

    You cannot use String.Empty like that, when your ObjectQuery is converted to SQL these are presented as parameters (p_linq_X). Try to use "" instead of String.Empty and it should work.

    Hope this helps!

     

     

     


    --Rune
    Wednesday, June 8, 2011 6:49 PM
  • Hi BobTodd,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 17, 2011 3:31 AM
    Moderator
  • Got it. But this is Fugly (so is the sql). String concat needs some serious attention!

    .Where(c => (
                 
    ((c.FirstName ?? String.Empty).Length > 0 && (c.FirstName ?? String.Empty).Contains(searchText)) ||
                   
    ((c.LastName ?? String.Empty).Length > 0 && (c.LastName ?? String.Empty).Contains(searchText)) ||
                   
    ((c.CompanyName ?? String.Empty).Length > 0 && (c.CompanyName ?? String.Empty).Contains(searchText))
                   
    )  
                 
    )

    yields

    AND (((( CAST(LEN(CASE WHEN ([Extent3].[FIRST_NM] IS NULL) THEN @p__linq__0 ELSE [Extent3].[FIRST_NM] END) AS int)) > 0) 
            AND
    (CASE WHEN ([Extent3].[FIRST_NM] IS NULL) THEN @p__linq__1 ELSE [Extent3].[FIRST_NM] END LIKE @p__linq__2 ESCAPE N''~''))
            OR
    ((( CAST(LEN(CASE WHEN ([Extent3].[LAST_NM] IS NULL) THEN @p__linq__3 ELSE [Extent3].[LAST_NM] END) AS int)) > 0)
            AND
    (CASE WHEN ([Extent3].[LAST_NM] IS NULL) THEN @p__linq__4 ELSE [Extent3].[LAST_NM] END LIKE @p__linq__5 ESCAPE N''~''))
            OR
    ((( CAST(LEN(CASE WHEN ([Extent3].[COMPANY_NM] IS NULL)
            THEN
    @p__linq__6 ELSE [Extent3].[COMPANY_NM] END) AS int)) > 0)
            AND
    (CASE WHEN ([Extent3].[COMPANY_NM] IS NULL) THEN @p__linq__7 ELSE [Extent3].[COMPANY_NM] END LIKE @p__linq__8 ESCAPE N''~'')))
    Friday, June 17, 2011 11:03 AM