Specified cast is not valid when querying MySQL

Question

Connecting to MySQL database via MySQL.Data.Entity v6.9.9 (the current version). Trying to retrieve the list of all tables via a straight SQL query (within MyDBContext):

public async Task<IEnumerable<DBTableInfo>> GetTablesAsync()
{
    string sql = @"SELECT TABLE_NAME Name, TABLE_ROWS NumRows 
        FROM information_schema.tables
        where TABLE_TYPE='BASE TABLE'
        order by TABLE_NAME;";
    var result = Database.SqlQuery<DBTableInfo>(sql);
    return await result.ToListAsync();
}

DBTableInfo is defined simply as:

public class DBTableInfo
{
    public string Name { get; set; }
    public long NumRows { get; set; }
}

I am getting the error:

Specified cast is not valid.

The offending line is the return statement of the GetTableAsync method. The error occurs because of the NumRows property of DBTableInfo. If I comment out NumRows property from DBTableInfo, the query works.

Now, the information_schema.tables defines TABLE_ROWS as bigint. My DBTableInfo object declares the corresponding property as long. Even though this column does not contain nulls, I also tried with long?, and it does not work either.

Why? Any suggestions on the best way to deal with it will be appreciated.

If you change the NumRows property to object instead of long, you can check which type is actually being used to deserialize the TABLE_ROWS value. Have you tried checking that? — Mateus Schneiders, Apr 06 '17 at 20:42
@Mt. Schneiders Actually, it seems to come back as `System.UInt64`, a.k.a. `ulong`. But I am getting zero as the value for all tables, even those that are not empty. And no, `decimal` doesn't work either. — Optimax, Apr 06 '17 at 20:59
Assuming the query is correct, you could try casting the column value on the SQL query itself. — Mateus Schneiders, Apr 06 '17 at 21:01
Casting to what? It needs to be an integer for my purposes. The query runs fine in MySQL Workbench. — Optimax, Apr 06 '17 at 21:03
You could try casting the BIGINT column to INT. Don't know if that would actually change the way MySQL deserializes the value. — Mateus Schneiders, Apr 06 '17 at 21:13
@Mt. Schneiders You were right. I was reluctant to do this, because of the possible overflow, but in the end, that turns out the only way to do it, and the overflow won't happen before I retire. ;-) — Optimax, Apr 24 '17 at 14:29

score 1 · Accepted Answer · edited May 23 '17 at 12:17

The problem was solved (more like "worked around") by casting the NumRows value to a signed version in SQL like this:

CAST(TABLE_ROWS as SIGNED) NumRows

and ensuring my class is still defined as:

public class DBTableInfo
{
    public string Name { get; set; }
    public long NumRows { get; set; } // <-- ulong does not work!
}

With those two steps, the records deserialize correctly.

Note that you cannot use ulong (unsigned 64-bit integer) as the declared type for NumRows because - as it turns out - unsigned longs are still not supported by Entity Framework. It took me a while to re-learn this.

Here is a relevant reference:

How to use unsigned int / long types with Entity Framework?

So, you have to both cast the unsigned long value from the database to a signed version, and declare the receiving member of the class as (signed) long.

Unfortunately, this leaves the room open for overflow errors on MySQL side. Fortunately, my tables are nowhere near the number of rows where that would become a real problem.

Specified cast is not valid when querying MySQL

1 Answers1