Query works in pgAdmin, but returns System.InvalidCastException: Can't cast database type text to Int64 when executed using Npgsql

[michaelsutanto]

SUMMARY: I did a query in pgAdmin and it worked, but using the same query in C# using FromSqlRaw now returns a cast error.

DETAILS:

I'm currently working on a C# RESTful API project that aims to use a string of arrays requests posted 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 a sum based 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:

request = string.Join("', '", requests);
request = string.Format("'{0}'", request);

command = "SELECT DISTINCT t2.property_value, " +
                     "SUM(CAST(t1.property_value AS INTEGER)) " +
                     "FROM public.todoitems t1 " +
                     "JOIN public.todoitems t2 " +
                     "ON t1.\"FK\" = t2.\"FK\"" +
                     "WHERE t1.property_name = 'Income' " +
                     "AND t2.property_value IN("+ request +") " +
                     "GROUP BY t2.property_value";

             var result = _context.resultitems.FromSqlRaw(command);
             return result;

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:

request = string.Join("', '", requests);
            request = string.Format("'{0}'", request);
            string na = "NA";
            na = string.Format("'{0}'", na);

            command =
                    "DROP TABLE IF EXISTS temptable; " +
                    "DROP TABLE IF EXISTS altertable; " +

                    "CREATE TEMP TABLE temptable(" +
                        "property_value TEXT, " +
                        "\"sum\" INTEGER" +
                    "); " +

                    "INSERT INTO temptable " +
                    "SELECT DISTINCT t2.property_value, " +
                    "SUM(CAST(t1.property_value AS INTEGER)) " +
                    "FROM public.todoitems t1 " +
                    "JOIN public.todoitems t2 " +
                    "ON t1.\"FK\" = t2.\"FK\" " +
                    "WHERE t1.property_name = 'Income' " +
                    "AND t2.property_value IN(" + request + ") " +
                    "GROUP BY t2.property_value;" +

                    "DO $$ " +
                    "BEGIN " +

                    "CREATE TEMP TABLE altertable AS TABLE temptable; " +

                    "ALTER TABLE altertable " +
                    "ALTER COLUMN \"sum\" TYPE TEXT; ";

            foreach (string requestObject in requests)
            {
                string req = string.Format("'{0}'", requestObject);
                command = command +

                    "IF NOT EXISTS (SELECT 1 FROM altertable WHERE altertable.property_value = " + req + ") " +
                    "THEN " +
                        "INSERT INTO altertable " +
                        "VALUES " +
                            "(" + req + ", + " + na + "); " +

                    "ELSE " + 
                       "IF ((SELECT 1 FROM altertable WHERE altertable.property_value = " + req + ") < 30) " +
                       "THEN " +
                            "UPDATE altertable " +
                            "SET \"sum\" = " + na + " " +
                            "WHERE property_value = " + req + "; " +
                       "END IF; " +
                    "END IF; ";
            };
            command = command 
                + "END $$; " 
                + "SELECT * FROM altertable;";
            finalResult = _context.resultitems.FromSqlRaw(command);

            return finalResult;

In the second chunk of code, you can see another table called alteredtable to transform the sum given in temptable and store it as a string. However, performing this using Postman yields the following error:

System.InvalidCastException: Can't cast database type text to Int64

Doing 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 altertable at all was to try and solve this casting problem, by casting everything in temptable first and then copying everything over to altertable but as you can see, that solution also fell apart.

Before adding this altertable element in the query, it already worked in pgAdmin even though the type of the sum column 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:

Id property_value property_name FK
1  Jean           Name          A
2  Headmaster     Job           A
3  200            Income        A
4  Mona           Name          B
5  Professor      Job           B
6  100            Income        B
7  Jean           Name          C
8  Headmaster     Job           C
9  100            Income        C

and posting ["Jean", "Mona", "Michael"] should result in

property_value sum
Jean           300
Mona           100
Michael        NA

[michaelsutanto]

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 TABLE and DROP TABLE seem 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?

[michaelsutanto]

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 pQuery is

"DROP TABLE IF EXISTS public.temptable;" +
                    "CREATE public.temptable(" +
                        "property_value TEXT, " +
                        "\"sum\" INTEGER" +
                    "); ";

I hope this is clear enough.

[Emill]

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?

[Emill]

Another thing, you need to escape user input here if you build your query manually, to avoid sql injections.

[michaelsutanto]

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

[michaelsutanto]

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 TABLE and DROP TABLE seem 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.

[Emill]

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?

[michaelsutanto]

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!

[michaelsutanto]

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 command is 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.