Having problems comparing strings in Linq entities and SQL

Question

I'm fairly new to Linq and EF.

Any way the comparison of strings is incorrect?

I've already tried the String.Equals or CompareTo but those return boolean values, i read for Linq the comparison String == string is like a WHERE statement from SQL.

public IHttpActionResult GetMultifiberResult(string partNumber)
{
    var list = db.MultifiberResults.Where(s => s.PartNumber == 
                partNumber).ToList();

    return Ok(list);
}

list should return a bunch of values where the column PartNumber from the DB is equal to parameter partNumber. When I search using int comparison it does find matches in an int column but not with varchar columns. Controller always returns empty and the count is 0.

Maybe it is `CHAR` column, in which case you need to use `Trim()` method — Michał Turczyn, Jun 12 '19 at 06:32
did you inspect the value of `partNumber` in the debugger? may be you have some trailing spaces or hidden characters in there. Where does the value come from? user input? file? any other source? — Mong Zhu, Jun 12 '19 at 06:44
Perhaps you're having [this](https://stackoverflow.com/questions/5080727/string-equals-not-working-as-intended) problem? (or a variant of it at least) — fredrik, Jun 12 '19 at 06:45
The code looks fine. Can you put a breakpoint where you create `MultifiberResults` and manually check the collection has an instance with the PartNumber you're trying to match? — Reinstate Monica Cellio, Jun 12 '19 at 06:51
Please show us a SQL statement that you have run **directly against the database** and it returned the data you wanted. — mjwills, Jun 12 '19 at 06:53
@mjwills It may help, but the `MultifiberResults` collection is what we really need to see. Anything could happen between getting the data and putting it into that collection. — Reinstate Monica Cellio, Jun 12 '19 at 06:54
Incidentally, `.Where()` will return all elements where the function returns true, so you can use `String.Equals` or `CompareTo` without issue. However, what you have should also be fine. — Reinstate Monica Cellio, Jun 12 '19 at 06:55
Create test `For Each` loop and see what values you have on both sides. — JohnyL, Jun 12 '19 at 07:14

score 0 · Answer 1 · answered Jun 12 '19 at 08:34

0

Could it be simply mismatching case? Try this if and only if you want to ignore case:

public IHttpActionResult GetMultifiberResult(string partNumber)
{
    var list = db.MultifiberResults.Where(s => s.PartNumber.Equals(partNumber, StringComparison.OrdinalIgnoreCase)).ToList();

    return Ok(list);
}

Otherwise, your code is fine. You need to debug it and show us the results, and make sure there are no leading or trailing spaces in either the database or the parameter value; in which case you should .Trim() both.

answered Jun 12 '19 at 08:34

Mark Cilia Vincenti

1,410
8
25

This is Linq-to-Sql, so are you sure the expression tree representation of `s.PartNumber.Equals(partNumber, StringComparison.OrdinalIgnoreCase)` is translated into some meaningful SQL (different from what `s == partNumber` is translated into)? At least it depends on the Linq provider. You can force the filtering to be executed locally, in .NET, rather than on the database server, by using `.AsEnumerable()` just before the `.Where(…)`, but that _may_ hurt performance dramatically. – Jeppe Stig Nielsen Jun 12 '19 at 08:53
@JeppeStigNielsen, I think it depends on whether the database has case-insensitive collation, in which case this would not be needed. – Mark Cilia Vincenti Jun 12 '19 at 09:41
I agree. But if the database has case-_sensitive_ collation (for the relevant column), are you sure your code actually works? Will the `StringComparison.Ordinal` translate into some `COLLATE` syntax on the database side? I am not sure. – Jeppe Stig Nielsen Jun 12 '19 at 13:35

Having problems comparing strings in Linq entities and SQL

1 Answers1