LINQ is converting string to special characters

Question

I am using this query to get data from database.

string nfc = "53f8372c";    
var temp = db.tempTable.AsNoTracking().Where(
               x =>
                   x.uid.Equals(nfc, StringComparison.CurrentCultureIgnoreCase)
                   && x.ENDED == null
                   && x.STATUS.Equals(Constants.ACTIVE)
              );

The sql that is generated from this query is:

{SELECT 
"Extent1"."ID" AS "ID", 
"Extent1"."uid" AS "uid", 
"Extent1"."ENDED" AS "ENDED", 
"Extent1"."STATUS" AS "STATUS", 
FROM "tempTable" "Extent1"
WHERE (("Extent1"."uid" = :p__linq__0) AND ("Extent1"."ENDED" IS NULL) AND ('Active' = "Extent1"."STATUS"))}

Why does it convert 53f8372c to :p__linq__0?

`x.uid.Equals(nfc, StringComparison.CurrentCultureIgnoreCase)` nfc is variable and not string literal/constant? — Lukasz Szozda, Nov 30 '17 at 18:14
@DawoodAbbasi Exactly, and why it should be literal in SQL then? — Lukasz Szozda, Nov 30 '17 at 18:17
On a side note you're not going to get case insensitive comparisons that way. It's going to be completely based on the collation of your db, which is likely already case insensitive. — juharr, Nov 30 '17 at 18:19

score 6 · Accepted Answer · answered Nov 30 '17 at 18:15

6

That's just parameterizing the SQL. If you look at the parameters passed to the query, you'll see that :p__linq__0 has a value of 53f8372c.

This parameterization is helpful, as the server can cache the query plan and reuse it for the same query using different parameter values.

answered Nov 30 '17 at 18:15

Jon Skeet

1,421,763
867
9,128
9,194

It's also better to use parameterized SQL to protect against SQL injection. https://stackoverflow.com/questions/4712037/what-is-parameterized-query – Marisa Nov 30 '17 at 18:16
1

@Marisa: Yes, although it's at least *less* of an issue when the SQL is being automatically generated - LINQ *could* have used a string value and escaped it if necessary. That would still allow for bugs to creep in in terms of escaping, of course. – Jon Skeet Nov 30 '17 at 18:19
@Marisa Parameterised queries have downsides too, for example they won't use filtered indexes. That's a bit of an edge case though. – DavidG Nov 30 '17 at 18:23
@DavidG Good point. Generally in those cases the inlined value isn't variable or user input at all, but a hardcoded constant, so there is no risk of injection there. – Alejandro Nov 30 '17 at 18:43

LINQ is converting string to special characters

1 Answers1