CODEWITHSUNDEEP
c#.netentity-framework-corenpgsql
Laurie Young
Laurie Young

Reputation: 138444

Sql Parameterising with PgSql using EF Core and C#

I'm trying to execute an SQL query using C# and EF Core that takes a parameter. I believe the parameter is being ignored

The code I'm using is

string sqlQuery = @"SELECT ""Studies"".* 
                    FROM ""Studies"" 
                    INNER JOIN ""Searches"" on ""Searches"".""StudyId"" =""Studies"".""StudyId"" 
                    WHERE ""Searches"".""Document"" @@ to_tsquery('@text')";

      
IQueryable<Study> studies = _studiesContext.Studies.FromSqlRaw(sqlQuery, new NpgsqlParameter("@text", "fire"));

What I'm expecting to happen is that I get the same rows back from my database as if I run the following command into the Postgres console:

SELECT "Studies"."StudyId", 
        "Studies"."Title",
        "Studies"."Author"
FROM "Studies"
INNER JOIN "Searches" on "Searches"."StudyId" ="Studies"."StudyId" 
WHERE "Searches"."Document" @@ to_tsquery('fire');

But I do not. Instead, I get the same results back as if I type the following into the Postgres console:

SELECT "Studies"."StudyId", 
        "Studies"."Title",
        "Studies"."Author"
FROM "Studies"
INNER JOIN "Searches" on "Searches"."StudyId" ="Studies"."StudyId" 
WHERE "Searches"."Document" @@ to_tsquery('@text');

I believe that @text is not being replaced with fire in my query

The EF Core logs show:

Executed DbCommand (10ms) [Parameters=[@text='?'], CommandType='Text', CommandTimeout='30']
SELECT "Studies".*
FROM "Studies"
INNER JOIN "Searches" on "Searches"."StudyId" ="Studies"."StudyId"
WHERE "Searches"."Document" @@ to_tsquery('@text')

I feel like I'm missing something that should be obvious?

Upvotes: 0

Views: 1050

Answers (1)

MaliTica
MaliTica

Reputation: 70

I believe that the single quotes around @text are the problem, try without them

Upvotes: 1

Related Questions