In this article, I will give one of the solutions to overcome the "The given key was not present in the dictionary" exception in MySQL. Developers may face this error in many situations, but I faced this error in query execution after migrating the MSSQL Database to MySQL.

Recently, I have migrated a database from MSSQL to MySQL. After migration, I tried to run the simple select query with joins which already worked in my application with MSSQL connection string. When I ran the same query with MySQL Connection string, I got the “The given key was not present in the dictionary” Exception. But if I run the same query in MySQL Workbench, it's working fine.

So, here is my sample query which I tried to execute through MySqlDataAdapter.
Select * from usersdetails; 
nMySqlDataAdapter = new MySqlDataAdapter(xszQuery, zSqlConn); 

When this query passes MySqlDataAdapter, I'm getting the exception “The given key was not present in the dictionary”. But the same query is working fine in MySQL Workbench.

It's the same process if I pass the query like,
Select usersID,usersname from usersdetails; 
nMySqlDataAdapter = new MySqlDataAdapter(xszQuery, zSqlConn); //Working fine without any exception. 

I have searched for many solutions on Google, but none worked for me. Some solutions said that error in connection string needs to include the charset=utf8 parameter with the connection string. But that didn't work for me.

Then I found something:
Select usersID,usersname from usersdetails; // this query working fine, no error 
Select usersID,usersname,usersAddress from usersdetails; // this query returns exception 
Select usersID,userGUID from usersdetails; // this query working fine, no error 
Select usersID,userGUID,userDepartment from usersdetails;  // this query returns exception 

So, the problem is something with the columns which I trying to select, then I found what makes each column differ from the other.

What I found is “Collation” value of each column is different based on column datatype, I don’t know in what basis these values are assigned during migration.

What is Collation in MySQL?
A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.

A collation orders characters based on weights. Each character in a character set maps to a weight. Characters with equal weights compare as equal, and characters with unequal weights compare according to the relative magnitude of their weights.

To know more about “Collation” click here.

I followed two different methods to overcome this exception.

Method 1
By selecting “Table default” value in “Collation” option for the all columns in a table we can overcome this exception.

Method 2
During migration from MSSQL to MySQL, on manual editing step, we can see the tables, column names, and respective datatypes. In the following image on step 3, by default MySQL had taken “CHARACTER SET ‘utf8mb4’ ” as charset value for some columns. We can edit this section, just select and delete “CHARACTER SET ‘utf8mb4’ ” and apply changes. Now all columns with “Collation” value become “Table default” after migration.

By using these two ways we can overcome this exception. In this article, I have given one of the ways to overcome “The given key was not present in the dictionary” exception. If anybody knows any other way to overcome this exception, please mention in the comment  box. I hope this article is very useful.