Linq to SQL string concatenation where clause generates buggy sql

Answers to similar questions are not working for me.

Consider this string concat query:

.Where(c => (
              c.FirstName ?? String.Empty +
              c.LastName ?? String.Empty +
              c.CompanyName ?? String.Empty).Contains(searchText)

results in the sql below. this actually fails to find a match on last name due to the first case statement.

I'm jumping through all sorts of hoops to find a solution (computed column < forget it, udf < forget it, sp < forget it), variations on the where theme...

CASE 
            WHEN ([Extent3].[FIRST_NM] IS NULL) 
                THEN 
   开发者_StackOverflow                 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''~'')

Any ideas?

Looking at the generated SQL, it looks like you might need to introduce more bracketing:

.Where(c => (
          (c.FirstName ?? String.Empty) +
          (c.LastName ?? String.Empty) +
          (c.CompanyName ?? String.Empty)).Contains(searchText)

Since it looks like ?? has lower precedence than + (at least, in the generated SQL)

(Unfortunately the only C# operator precedence spec I can find online is for .NET 1.1/2003, but looking in later specs, it appears that the null coalescing operator appears just above Conditional)

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''~'')))