CODEWITHSUNDEEP
c#mysql
Optimax
Optimax

Reputation: 1584

Specified cast is not valid when querying MySQL

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.

Upvotes: 0

Views: 978

Answers (1)

Optimax
Optimax

Reputation: 1584

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.

Upvotes: 1

Related Questions