Why my LINQ Where clause concatenating a string and call 'contains' using a value is not working?

Question

I am trying to filter data from db using two columns.

The thing is that the user only posts a single field, and I will use that field to check if there is any record on database that concatenating both columns values exist.

At the moment I have the following:

query
  .Where(x => string
     .Concat(x.Field1, " ", x.Field2)
     .Contains(parameter.MyValue, StringComparison.InvariantCultureIgnoreCase));

My problem is that the data is not being filtered this is weird, does anyone knows what is happening? thank you!

Are you just calling `query.Where(...)`? `Where` _returns_ a new query so you'd need something like `query = query.Where(...)` or `var filteredQuery = query.Where(...)` if you want to keep the original query intact. — D Stanley, Jan 05 '17 at 21:26
@MatthewWhited It's an expression that will be parsed by the query provider, not a method that's being executed. — Servy, Jan 05 '17 at 22:16
Then do a .tostring and take the query to you database tools. — Matthew Whited, Jan 06 '17 at 00:38
@D Stanley has the answer, please D Stanley make a "proper" answer so I accept it! I am so dumb, this was a typo from my side, shame on me (I don't even deserve to be here in this wonderful community lol) — TiagoM, Jan 19 '17 at 10:07

Frank Fajardo · Answer 1 · 2017-01-06T03:08:22.200

2

You did not mention which your db provider is. But you could try this:

query
  .Where(x => 
      ((x.Field1 ?? "") + " " + (x.Field2 ?? "")).Contains(parameter.MyValue));

This code assumes your parameter and its MyValue property are both not null, and the latter is also not empty.

edited Jan 06 '17 at 03:08

answered Jan 05 '17 at 22:10

Frank Fajardo

7,034
1
29
47

score 1 · Answer 2 · answered Jan 05 '17 at 21:24

1

Because your LINQ provider knows nothing about the Concat method. Try the same with the + operator.

answered Jan 05 '17 at 21:24

Ricardo Peres

13,724
5
57
74

1

If the problem was that the query provider didn't support it, it would throw an error saying as much, it wouldn't just ignore the filter. – Servy Jan 05 '17 at 21:28
What query provider is going to just ignore the invalid method used and not filter the data? Different providers might fail *differently*, but they wouldn't do *nothing*. And it's not my question. – Servy Jan 05 '17 at 22:28
That would depend on the query provider. – Matthew Whited Jan 06 '17 at 00:40

score 1 · Answer 3 · edited May 23 '17 at 12:08

First thing to check is ensure that the Sql (I assume you are querying a relational database) being generated is working when run directly against the database.

You could get the Sql statement either using a profiler, during a debug session in Visual Studio you may be able to get it via a trace, or my preference is to use LinqPad. Try these methods also.

Your query looks correct, I was able to get it to return results from my database.

Alternative queries:

query
  .Where(x => x.Field1.Contains(parameter.MyValue) || x.Field2.Contains(parameter.MyValue));


query.Select(x => new 
                  { 
                    Field = string.Concat(x.Field1, " ", x.Field2), 
                    RowData = x 
                  })
     .Where(x => x.Field.Contains(parameter.MyValue));

Why my LINQ Where clause concatenating a string and call 'contains' using a value is not working?

3 Answers3