The following code is a simplified version of the code I am trying to run, but should convey the same message.
When using EF with NpgSQL I am able to call raw SQL queries using: Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.SqlQuery(FormattableString sql)
For some reason when running the following code, it runs successfully:
//Functional Version
string TargetString = "";
FormattableString BasicVersion = $"SELECT * FROM tenant";
IQueryable<string> QueryableBasicVersion = _context.Database.SqlQuery<string>(BasicVersion);
Returning a QueryableBasicVersion with the Expression.Sql as "SELECT * FROM tenant" as expected. I am then able to read the response from this as expected.
But when I run the following:
//Non-Functional Version
string TargetString1 = "";
string ParsedVariable = "tenant";
FormattableString AdvancedVersion = $"SELECT * FROM {ParsedVariable}";
IQueryable<string> QueryablyAdvancedVersion = _context.Database.SqlQuery<string>(AdvancedVersion);
This should provide the same SQL statement but instead it is "SELECT * FROM {0}". Which should be referencing the variable on call. Then when trying to read the data in the same way as the first I get the following error:
MessageText: invalid input syntax for type uuid: "@p0"
(In my original code, I am returning a string rather than *, and I am replacing a uuid, hence the error message above. But the same problem applies)
Why does the resulting response from the database return an error?
Does the 2nd method require a different way of reading the response?
Should it not be exactly the same call?
Databases almost never support parameterizing table/column names; so SELECT * FROM foo WHERE bar = {SomeValue} works, but SELECT * FROM {SomeTable} does not. Parameterizing table/column names as you're doing effectively creates fully dynamic SQL queries.
If you need to parameterize table/column names, you'll have to interpolate them into your SQL as a .NET string, so that the database just receives a non-parameterized string. Note that this exposes your application to SQL injection attacks, so be sure to sanitize whatever it is that your interpolating into the string.
Please see these docs which fully explain these concepts and the relevant APIs.