4

The Question

When I write with Linq to entities 

context.MyTable.Where(t => t.Name == "Test");

it translates to the following sql: 

select * from MyTable where Name = 'Test'

I want to write an expression in Linq to entities that will translate to: 

select * from MyTable where Name like 'Test'

Is there some way to achieve that?

Note- I also tried Equals and CompareTo == 0 but to no avail.

The back story

The reason I want to use Like instead of = is because of the fact that white spaces are ignored at the end of the string if you use =, but it works if you use Like (see this question: Why the SQL Server ignore the empty space at the end automatically?, and also this: Linq to Entity comparing strings ignores white spaces).

Edit: Why it's not a duplicate of How to do SQL Like % in Linq?

That question asks for like %, which can be done with Contains/StartsWith/EndsWith, but it's not the same question, I want complete equality so those will not help me. There was an answer on that question that did look promising though, using SqlMethods.Like, so I tried 

context.MyTable.Where(t => SqlMethods.Like(t.Name, "Test")

but I got the following error:

{"LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression."}

Community
  • 1
  • 1
tzachs
  • 4,331
  • 1
  • 28
  • 28
  • @Reinard + @L.B, no, it's not a duplicate. I want to do Like without %, `Contains` does not work here. – tzachs Sep 02 '16 at 21:17
  • I would suggest you use `context.MyTable.Where(t => t.Name.TrimEnd() == "Test")` in your query and see if that achieves your desired result. – Anthony Pegram Sep 02 '16 at 21:27
  • @AnthonyPegram it won't help. I want the opposite behavior, I get a match when I don't want to get a match. – tzachs Sep 02 '16 at 21:28
  • I must be completely misunderstanding MSDN's documentation on LIKE, then. "If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero *or more* trailing blanks are returned." https://msdn.microsoft.com/en-us/library/ms179859.aspx – Anthony Pegram Sep 02 '16 at 21:31
  • @AnthonyPegram not sure what you're getting at here. Yes, LIKE should work, if only I could use that in LINQ to Entities instead of = which is exactly what I'm trying to do. – tzachs Sep 02 '16 at 21:36
  • So to be sure I understand. If your supplied value (in code) is "Test", you want it to match (in the db) "Test", and "Test ", correct? – Anthony Pegram Sep 02 '16 at 21:39
  • @AnthonyPegram I'm pretty sure OP is saying the opposite of that. Using `=` will match records with trailing spaces. OP is trying to *not* match records with the trailing spaces... the way `LIKE` works, but OP can't use `LIKE` in EF because of the error shown. – itsme86 Sep 02 '16 at 21:41
  • @itsme86, OK, but that's not what LIKE does, not according to the documentation and to my testing from the last few minutes. – Anthony Pegram Sep 02 '16 at 21:44
  • @AnthonyPegram You're saying "Test" will match "Test " (trailing space) with the `LIKE` operator? – itsme86 Sep 02 '16 at 21:45
  • @itsme86, yes, it does. – Anthony Pegram Sep 02 '16 at 21:45
  • I should say the documentation I refer to says this behavior applies to SQL 2008+ and that is also what I am testing with. I cannot say if prior versions of SQL Server handle it differently. – Anthony Pegram Sep 02 '16 at 21:47
  • @AnthonyPegram Hmm... I get the same behavior with SQL 2014. I guess http://stackoverflow.com/a/17876575/1141432 is a *liar*! ;) – itsme86 Sep 02 '16 at 21:50
  • LIKE seems to work better when you actually mean to include a given number of trailing characters as significant, not eliminate them. (LIKE 'Test ' will *not* match 'Test', but LIKE 'Test' *will* match 'Test '). – Anthony Pegram Sep 02 '16 at 22:00
  • I don't understand this question. If all you want is simple equality, rather than the pattern matching LIKE is typically used for, why can't you just use equality operators? And why do you want your SQL to use LIKE even though you don't want the pattern matching? – Peter Duniho Sep 02 '16 at 22:05
  • @PeterDuniho You must have missed the whole "trailing spaces" part of the question / discussion. – itsme86 Sep 02 '16 at 22:12
  • @itsme86: no, I didn't miss it. But a) the OP didn't participate in it, so it's not any sort of definitive clarification (indeed, the OP says they want _"complete equality"_), and b) for anything other than strict equality, the same basic answer is going to apply, according to the Q&A that addresses LIKE. – Peter Duniho Sep 02 '16 at 22:15
  • @PeterDuniho Read the paragraph in the OP's question under "The back story" where OP goes in depth into why LIKE instead of =. – itsme86 Sep 02 '16 at 22:20
  • 1
    IMO OP should just say exactly what strings should and shouldn't match, including all examples of trailing whitespace on both sides (DB has "test", "test ", "test ", when do you want "test", "test ", and "test " to match those 3?) Chances are OP's conception of how LIKE works is incorrect, so if people answer his question he won't get the results he wants. He says LIKE doesn't ignore whitespace, but it does, only in certain cases however. LIKE does *not* compare "complete equality" as both anthony and itsme found out. edit: and the comment system strips all but 1 trailing space.. – Quantic Sep 02 '16 at 22:20
  • @AnthonyPegram, wow, thanks for that piece of information. I tested that like 'test ' doesn't match 'test' prior to posting the question, but didn't think that the other direction will behave differently (and still don't understand why, as the doc says it will not be padded right, you would think it applies to both sides). – tzachs Sep 05 '16 at 15:50

2 Answers2

4

It's official:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces.

And it's a nuisance if you don't want it.

One SQL function that doesn't ignore spaces is DATALENGTH. Fortunately, we can use this function in EF queries, because it is one of the functions in SqlFunctions. So you could add an additional check whether DATALENGTH is equal to the length of the search string:

var searchText = "Test";
var result = context.MyTable
                    .Where(t => t.Name == searchText
                             && SqlFunctions.DataLength(t.Name)
                                  == SqlFunctions.DataLength(searchText))

Comparing DataLength of both t.Name and searchText is necessary because DataLength returns the number of bytes, not the number of characters (Ivan, thanks for commenting).

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • 1
    Hi Gert, Interesting solution as usual, but `DataLength` returns number of bytes, so I guess you need to apply it also to the parameter, e.g. `SqlFunctions.DataLength(t.Name) == SqlFunctions.DataLength(searchText)` – Ivan Stoev Sep 03 '16 at 08:26
  • 2
    @IvanStoev Yeah, I did this late last night. Went to bed with a small voice whining about bits and bytes, length of diacritics/unicode etc. making me doubt what DATALENGTH returns anyway. Busy today and then I saw your suggestion. Gladly adopted! – Gert Arnold Sep 03 '16 at 18:40
2

Funny I wrote the answer to the original question and would agree that this edge case is not a duplicate of that.

Depending on your query result size, could you run a second pass client-side:

var result = iQueryableSource
    .Where(i => i.Field.Contains("Fred"))
    .AsEnumerable()
    .Where(i => i.Field == "Fred");

I agree that if your data set is massive this may be an issue.

Another approach that is entirely server-side, is to combine StartsWith and EndsWith:

var result = iQueryableSource
        .Where(i => i.Field.StartsWith("Fred"))
        .Where(i => i.Field.EndsWith("Fred");
andleer
  • 22,388
  • 8
  • 62
  • 82
  • Maybe on the first approach, though I was hoping for something completely server side. The second approach is a bug: if I take your example, it will match with "FredFred". – tzachs Sep 04 '16 at 05:22
  • Sorry I didn't have access to a sql server to test this. How about adding a length check to the second approach? – andleer Sep 04 '16 at 15:32
  • Well, yes, that's basically Gert's proposed solution (and then I don't need startswith and endswith). – tzachs Sep 05 '16 at 15:57