Repository navigation
Query works in pgAdmin, but returns System.InvalidCastException: Can't cast database type text to Int64 when executed using Npgsql #174
Description
Activity
Update on this: I found that there was a type incompatibility in my database context. However, upon resolving this, I ended up having to split the execution of the query into 2 parts. A problem that I encountered here is that the query for
CREATE TABLEandDROP TABLEseem to never be executed when the entirety of the code is implemented but works just fine when it's executed on its own. Why is this?Upon seeing this problem, I tried defining a function solely for the purpose of creating and dropping these tables, as seen below:
static void ExecuteTable(String pQuery) { NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; User Id=postgres; Password=password; Database=TodoList;"); conn.Open(); // Define a query returning a single row result set NpgsqlCommand command = new NpgsqlCommand(pQuery, conn); // Execute the query and obtain the value of the first column of the first row command.ExecuteNonQuery(); conn.Close(); }where
pQueryis"DROP TABLE IF EXISTS public.temptable;" + "CREATE public.temptable(" + "property_value TEXT, " + "\"sum\" INTEGER" + "); ";I hope this is clear enough.
For the first issue, the result column types must match whatever data types you have for your entity properties.
I don't really understand the second issue. What is the expected and actual outcome of your last posted code?
Another thing, you need to escape user input here if you build your query manually, to avoid sql injections.
Another thing, you need to escape user input here if you build your query manually, to avoid sql injections.
For this, the API that I am currently developing is for internal use only, so I'm putting that further down the checklist first
For the first issue, the result column types must match whatever data types you have for your entity properties.
I don't really understand the second issue. What is the expected and actual outcome of your last posted code?
This one I already solved, because I overlooked the fact that the
_context.resultitem.FromSqlQuery()command uses a DbSet in my API, which had the type mismatch.Update on this: I found that there was a type incompatibility in my database context. However, upon resolving this, I ended up having to split the execution of the query into 2 parts. A problem that I encountered here is that the query for
CREATE TABLEandDROP TABLEseem to never be executed when the entirety of the code is implemented but works just fine when it's executed on its own. Why is this?The problem now lies in the fact that I cannot execute the CREATE TABLE and DROP TABLE queries along with my other queries. I don't know why.
How do you execute your CREATE TABLE and DROP TABLE? Using EF6 or Npgsql only? What happens instead when you run the code? What is the result/status/return value, if any?
How do you execute your CREATE TABLE and DROP TABLE? Using EF6 or Npgsql only? What happens instead when you run the code? What is the result/status/return value, if any?
Sorry, I'll get back to you tomorrow!
How do you execute your CREATE TABLE and DROP TABLE? Using EF6 or Npgsql only? What happens instead when you run the code? What is the result/status/return value, if any?
Hi, so for this, I use the the following string:
command = "DROP TABLE IF EXISTS public.temptable;" + "CREATE public.temptable(" + "property_value TEXT, " + "\"sum\" INTEGER" + "); ";Where the string
commandis run like such:_context.resultitems.FromSqlRaw(command).AsNoTracking();As for the result, the query can never find the table, namely giving the error of
the relation public.todoitems does not exist, implying that the table was never created in the first place.To add to the confusion, this worked when all the queries were executed together. However, since now I have 2 different DbSets, I had to split the execution of the queries into 2 parts. This split was necessary because in the first half of the query it works with integers while the second half of the query works with the same table altered into a string. This is where the problem started.
SUMMARY: I did a query in pgAdmin and it worked, but using the same query in C# using
FromSqlRawnow returns a cast error.DETAILS:
I'm currently working on a C# RESTful API project that aims to use a string of arrays
requestsposted by a user and execute an SQL query tasked to find integers in a column of a mix of integers and text, add it up, and return asumbased on a key attached to the entry.This will return the text attached to the key as well as the summed up integer.
This already works using the following query:
But then there is an additional requirement where the sum value should be set to "NA", which results in an error because now the "sum" column of the results have to be set to TEXT. I came up with a workaround like such:
In the second chunk of code, you can see another table called
alteredtableto transform the sum given intemptableand store it as a string. However, performing this using Postman yields the following error:System.InvalidCastException: Can't cast database type text to Int64Doing this query in pgAdmin works perfectly. What went wrong in executing this through Npgsql?
I've tried comparing the queries word for word and they seem to match.
To be honest, the whole point of adding
altertableat all was to try and solve this casting problem, by casting everything intemptablefirst and then copying everything over toaltertablebut as you can see, that solution also fell apart.Before adding this
altertableelement in the query, it already worked in pgAdmin even though the type of thesumcolumn in temptable is TEXT.I did some research online and this might be because of the weird execution order of an SQL query. However, if this is the case, shouldn't the query fail too when I ran it in pgAdmin?
I am so confused.
To give some more context, this is how the table looks like:
and posting
["Jean", "Mona", "Michael"]should result in