Having the following error on run while query runs perfect in phpmyAdmin-CodePudding

I have the following code and I get System.IndexOutOfRangeException: 'Could not find specified column in results: categories.category_id' on execution. What could be the problem.

  public List<Category> GetCat()
        {
            try
            {
                List<Category> categories = new List<Category>();
                con.Open();
                

                var cmd = con.CreateCommand();
                cmd.CommandText = "SELECT categories.category_id, categories.category, subcategory.subCatName, subcategory.subCategory_id  from subcategory inner join categories on categories.category_id = subcategory.category_id";

                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    
                            Category category = new Category(dr.GetInt32("categories.category_id"), dr.GetString("categories.category"), dr.GetInt32("subcategory.subCategory_id"), dr.GetString("subcategory.subCatName"));
                            categories.Add(category);
                    
                }
                return categories;


            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                this.con.Close();
            }
        }

CodePudding user response：

The error isn't being generate by the SQL, it's from this line;

Category category = new Category(dr.GetInt32("categories.category_id"), dr.GetString("categories.category"), dr.GetInt32("subcategory.subCategory_id"), dr.GetString("subcategory.subCatName"));

At this point the data is local, and the column names do not have table prefixes. So, the error is telling you it can't find those column names.

Try this...

Category category = new Category(dr.GetInt32("category_id"), dr.GetString("category"), dr.GetInt32("subCategory_id"), dr.GetString("subCatName"));

(As an aside, if you run the SQL in phpMyAdmin, you'll see the actual column names.)