SELECT COUNT(*) Thinks String parameter is an IEnumerable - Returns an Array? (CockroachDB C#)

I am working on migrating a project that used a MySQL DB over to CockroachDB. In this query, I’m simply trying to see if the username exists in the accounts table. When I run the same query in a CLI, it returns count 1.

I am trying to get this integer in my C# application, however I’m not quite understanding the issue with adding a parameter in my query. If my SELECT COUNT(*) query has a string parameter in it, it seems to think this string is an IEnumerable?

Accounts table create/structure:

CREATE TABLE accounts (
	id INT4 NOT NULL DEFAULT nextval('accounts_auto_inc':::STRING),
	sessionid VARCHAR(10) NULL,
	ipadd VARCHAR(50) NULL,
	connectionid VARCHAR(50) NULL,
	username VARCHAR(50) NOT NULL,
	password VARCHAR(50) NULL,
	displayname VARCHAR(50) NULL,
	phonenum VARCHAR(10) NULL,
	status VARCHAR(50) NULL,
	perms VARCHAR(20) NULL,
	email VARCHAR(100) NULL,
	CONSTRAINT "primary" PRIMARY KEY (id ASC),
	FAMILY "primary" (id, sessionid, ipadd, connectionid, username, password, displayname, phonenum, status, perms, email)
)

This table was automatically converted from MySQL to CockroachDB.

C# Query Code:

public bool accExists(string id, string userName)
{
    int totalCount = 0;

    using (var conn = new NpgsqlConnection(connstr.getConnStr(id)))
    {
        //SSL Connection Init
        conn.ProvideClientCertificatesCallback += ProvideClientCertificatesCallback;
        conn.UserCertificateValidationCallback += UserCertificateValidationCallback;

        conn.Open();

        // Check if Account Exists
        NpgsqlCommand accExists = new NpgsqlCommand("SELECT COUNT(*) FROM accounts WHERE username LIKE @username", conn);
        accExists.Parameters.AddWithValue("@username", userName);

        // Query Executions:
        totalCount = Convert.ToInt32(accExists.ExecuteScalar());
    }

    // Return Value
    switch (totalCount)
    {
        case 1: // Account Exists
            return true;

        case 0: // Account Does NOT Exist
            return false;

        default: // Protective Code - Shouldn't be hit
            addManageLog("SYSTEM", id, "MORE THAN ONE OF THE SAME USERNAME " + userName);
            return false;
    }
}

The exception caught is:

System.NotSupportedException: ‘Npgsql 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead’

I have also now tried changing the parameter line to:

accExists.Parameters.AddWithValue("@username", NpgsqlTypes.NpgsqlDbType.Varchar, userName);

to explicitly define the parameter type as it seems to think this is an IEnumerable value. However, this returns the exception:

The NpgsqlDbType ‘Varchar’ isn’t present in your database.

The username column in my database is set as a VARCHAR(50). I’ve also tried changing the VARCHAR type to TEXT with no luck.

I have also tried simply changing the query to have no parameter using the following:

NpgsqlCommand accExists = new NpgsqlCommand("SELECT COUNT(*) from accounts", conn);
totalCount = Convert.ToInt32(accExists.ExecuteScalar());

This returns the exception:

System.NotSupportedException: ‘The field ‘count’ has a type currently unknown to Npgsql (OID 20). You can retrieve it as a string by marking it as unknown, please see the FAQ.’

Thank you for your help!

I origionally posted this issue on Stack Overflow, but figured I may be able to get some better help here. You can view the post: https://stackoverflow.com/questions/56412276/select-count-thinks-string-parameter-is-an-ienumerable-returns-an-array-c

@BrianS can you double check and make sure that the credentials you are using has access to the table for select? I know it’s odd, but permission issues often show themselves in random error messages about types for some reason.

I’ve been trying to figure this out for the past two days. Something rather peculiar is that I can run a SELECT COUNT(*) query without a parameter just fine on another database, but I can’t run a SELECT COUNT(*) query without a parameter on this particular database.

Working:
Running the following select count query on my metrics database works fine:

NpgsqlCommand totalAnnouncements = new NpgsqlCommand("SELECT COUNT(*) from announcements", conn);
anncNum = Convert.ToInt32(totalAnnouncements.ExecuteScalar());

The anncNum integer is parsed without any issues.
Here is the create/table structure for the announcements table in my metrics database:

CREATE TABLE announcements (
	id INT4 NOT NULL DEFAULT nextval('announcements_auto_inc':::STRING),
	announcement VARCHAR(100) NULL,
	CONSTRAINT "primary" PRIMARY KEY (id ASC),
	FAMILY "primary" (id, announcement)
)

This table was automatically converted from MySQL to CockroachDB.



Not Working:
I’ve tried creating a new/clean database in CockroachDB called test with an accounts table containing just a SERIAL type ID and running a SELECT COUNT(*) on it.

NpgsqlCommand accExists = new NpgsqlCommand("SELECT COUNT(*) from accounts", conn);
totalCount = Convert.ToInt32(accExists.ExecuteScalar());

This returns the error:

System.NotSupportedException: ‘The field ‘count’ has a type currently unknown to Npgsql (OID 20). You can retrieve it as a string by marking it as unknown, please see the FAQ.’

This is the create/structure for this new test database

CREATE TABLE accounts (
	id INT NOT NULL DEFAULT unique_rowid(),
	CONSTRAINT accounts_pk PRIMARY KEY (id ASC),
	FAMILY "primary" (id)
)

If anyone is able to assist in discovering what the issue is, it would be greatly appreciated!

I appreciate the reply, I indeed have permissions for all tables in this database. I have removed them, received a permission error, and re-added them. I can use the same user to run the select count(*) query in the command line and will receive the integer count just fine. This seems to be an issue just with C# and cockroachdb. It may be worth mentioning, I am changing my secured database connection quite often.

using (var conn = new NpgsqlConnection(connstr.getConnStr(id)))

I pass in the database to connect to in this getConnStr string builder. This builds the connection query using the following:

        public static string getConnStr(string id = null)
        {
            var connStringBuilder = new NpgsqlConnectionStringBuilder();
            connStringBuilder.Host = "localhost";
            connStringBuilder.Port = 26257;
            connStringBuilder.SslMode = SslMode.Require;
            connStringBuilder.Username = "sauce";
            connStringBuilder.Password = "ChangeMe123*";
            if(!string.IsNullOrEmpty(id))
                connStringBuilder.Database = id;
            return connStringBuilder.ConnectionString;
        }

I am aware that this database (id) parameter is vulnerable to an injection attack, I will sanitize it before pushing this to production.

Hey @BrianS, thanks for writing in about this. We’ve seen similar things before - generally this is due to slight incompatibilities between CockroachDB and Postgres in the “type oid” department, which is roughly how Postgres assigns stable identifiers to their types.

What version of CockroachDB are you running? We’ve corrected several deficiencies in this area over time.

@jordan I’m currently running the latest V19.1.1 on Windows.