C#•8mo ago

❔ EF8 SqlQuery with PostgreSQL, put variable as string in FormattableString

Hi, I'm trying use SqlQuery with custom types.

_context.Database.SqlQuery<T>(@$"SELECT * FROM ""public"".{_tableName} WHERE ""Id""={Guid.Parse(id)}").SingleOrDefaultAsync();

Error: Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1" This line getting error cause the method converting this query to SELECT * FROM "public".@p0 WHERE "Id"=@p1. But I need that: SELECT * FROM "public"."Posts" WHERE "Id"=@p0 How can I do that?

3 Replies

JakenVeina•8mo ago

you'll have to do two stages of string interpolation the issue is that SqlQuery satisfies the interpolated string handler pattern, in its method signature, so the compiler doesn't translate the interpolated string into a call to string.Format(), it just passes the interpolated pattern string and the separate arguments straight to SqlQuery() I.E. you told EF that the name of the table is a query parameter and that's just invalid you need to explicitly build your query string separate from your call to SqlQuery() to ensure that the compiler translates it correctly

var sql = @$"SELECT * FROM ""public"".{_tableName} WHERE ""Id""=@id";

var result = await _contect.Database.SqlQuery<T>(sql, Guid.Parse(id)).SingleOrDefaultAsync();

var sql = @$"SELECT * FROM ""public"".{_tableName} WHERE ""Id""=@id";

var result = await _contect.Database.SqlQuery<T>(sql, Guid.Parse(id)).SingleOrDefaultAsync();

on a separate note, I have to ask what this is for, because it looks like you're trying to build a generic repository and you should not be doing that

od•8mo ago

I was bussy, I couldn't see your message. I'll try today. Thank you for your comment.

Accord•8mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF8 SqlQuery with PostgreSQL, put variable as string in FormattableString