Where clause in method syntax fails when combining strings

Question

I am trying to retrieve a record from a database using Entity Framework and LINQ. It works mostly as expected, but for a bug with a WHERE statement I can't quite figure out.

var nodeId = "001";

//This works and returns the record I am looking for
var nodeDisplayName = ("#" + nodeId);
var filteredNodes = dbContext.Node.Where(n => n.DisplayName == nodeDisplayName).ToList();

//This does not work and returns 0 results
var filteredNodes2 = dbContext.Node.Where(n => n.DisplayName == ("#" + nodeId )).ToList();

I would expect the two variants to behave exactly the same, and they appear to do so if I run them on a List in memory. But when running against entity framework/a database they behave differently. I can fix the bug easily enough by just using the first method, but I would like to understand what causes the difference in behavior.

EDIT

So the generated SQL queries look like this:

SELECT `n`.`id`, `n`.`display_name`, `n`.`reg_tms`
FROM `tablename`.`node` AS `n`
WHERE `n`.`display_name` = '#001'

SELECT `n`.`id`, `n`.`display_name`, `n`.`reg_tms`
FROM `tablename`.`node` AS `n`
WHERE `n`.`display_name` = ('#' + '001')

So that clears thing up a bit. I had assumed Entity Framework would concat the strings before generating the query.

Define "behave differently". Did you examine the SQL being generated? — MakePeaceGreatAgain, Jan 04 '19 at 09:16
Probably you can see [this issue](https://stackoverflow.com/questions/19147589/linq-to-entities-does-not-recognize-string-format-or-concatenation), which mentions that string concatenation (`+`) is not supported inside LINQ to Entities. — Tetsuya Yamamoto, Jan 04 '19 at 09:41
How about `$"#{nodeId}"` instead? It would avoid the ugly concat inside a nested parenthesis. — Svek, Jan 04 '19 at 09:44
Better use [String.Equals](https://learn.microsoft.com/de-de/dotnet/api/system.string.equals?view=netframework-4.7.2) as mentioned in [this post](https://stackoverflow.com/questions/814878/c-sharp-difference-between-and-equals) — derHugo, Jan 04 '19 at 09:44
`var filteredNodes2 = dbContext.Node.Where(n => n.DisplayName == $"#{nodeId}").ToList();` use [interpolated strings](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/tokens/interpolated) if your environment supports C# 6 or VS 2015 or above. — Kunal Mukherjee, Jan 04 '19 at 09:51
@KunalMukherjee Why? What does it change? Does EF even support that? — Patrick Hofman, Jan 04 '19 at 10:02
@PatrickHofman The `expression>` which is passed in the `where()` will be eventually converted to SQL queries right? — Kunal Mukherjee, Jan 04 '19 at 10:06
@PatrickHofman its not about EF I am just passing a regular `Func` wrapped inside an expression which is valid C# code — Kunal Mukherjee, Jan 04 '19 at 10:09
@PatrickHofman So the OP can use the SQL server profiler to check the query which is generated by modifying the where with the interpolated string, whether or not it satisfies the question — Kunal Mukherjee, Jan 04 '19 at 10:10
@KunalMukherjee That does indeed work. Not entirely sure why, but still. — Duckslayer, Jan 04 '19 at 10:16
@Duckslayer because The `Expression>` which is passed in the `where()` will be eventually converted to SQL query you can verify it in the SQL server profiler. — Kunal Mukherjee, Jan 04 '19 at 10:20
@PatrickHofman Confirming the interpolated string "solution". Current EF Core query translator is a "funny" thing. Would you ever think that the interpolated string (`string.Format`) or `string.Concat` (!) will be translated differently than the `string +` operator :) — Ivan Stoev, Jan 04 '19 at 14:57

score 3 · Answer 1 · answered Jan 04 '19 at 10:27

I am going to go ahead and work off the comments, your original question and your findings that you noted as an edit to your post --- and I think the objective was to refactor your original two statements into a single statement.

// code to refactor into a single statement
var nodeDisplayName = ("#" + nodeId);
var filteredNodes = dbContext.Node.Where(n => n.DisplayName == nodeDisplayName).ToList();

This can be refactored to...

// solution (refactored)
var filteredNodes = dbContext.Node.Where(n => n.DisplayName == $"#{nodeId}").ToList();

I had originally noted my answer as a comment, but the new syntax may not have stood out as an answer for you initially. I hope this helps.

Where clause in method syntax fails when combining strings

1 Answers1