Can't import foreign key in MySQL DB using C#, Visual Studio and MySQL.Data

Question

I am getting data from MSSQL DB with Entity Framework using C# and Visual Studio and importing it in MySQL DB using MySQL.Data nuget package and C# and Visual Studio again. If I have data without foreing keys everything is OK and the import is done. Here is the code:

var context = new SupermarketsChainEntities();
var measures = context.Measures;
var supermarkets = context.Supermarkets;
var vendors = context.Vendors;
var expenses = context.Expenses;
var products = context.Products;

    String myConn = "server=localhost;Database=supermarkets_chain;uid=root;pwd=;";
    MySqlConnection conn = new MySqlConnection(myConn);
    conn.Open();

    //Insert measures
    foreach (var measure in measures)
    {
        String insertQuery = "insert into measures (id, name) values (null, @name)";
        MySqlCommand cmd = new MySqlCommand(insertQuery, conn);
        cmd.Parameters.AddWithValue("@name", measure.Name);
        cmd.ExecuteNonQuery();
    }

But when I try to import data in table with one-to-many relationships and foreign keys:

foreach (var product in products)
        {
            String insertQuery = "insert into products (id, name, price, measure_id, vendor_id) values (null, @name, @price, @measure_id, @vendor_id)";
            MySqlCommand cmd = new MySqlCommand(insertQuery, conn);
            cmd.Parameters.AddWithValue("@name", product.Name);
            cmd.Parameters.AddWithValue("@price", product.Price);
            cmd.Parameters.AddWithValue("@measure_id", product.Measure);
            cmd.Parameters.AddWithValue("@vendor_id", product.Vendor);
            cmd.ExecuteNonQuery();      
        }

something goes wrong and I get the following exception:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Cannot add or update
 a child row: a foreign key constraint fails (`supermarkets_chain`.`products`, C
ONSTRAINT `fk_products_measures` FOREIGN KEY (`measure_id`) REFERENCES `measures
` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& i
nsertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affected
Rows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)

   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior
)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at SqlToMysql.Program.Main(String[] args) in d:\SoftUni\DatabaseApps\Teamwork
\SqlToMysql\SqlToMysql\Program.cs:line 67

Can someone help me handle the problem and import the data properly. I am attaching the DB schema here.

have you got add foreign key in entity framework shema ? like this http://stackoverflow.com/questions/19591513/how-to-add-foreign-key-relationship-in-entity-framework — Paul, Jul 21 '15 at 07:33
I believe so, here is the edmx diagram: [link](http://postimg.org/image/n8q6zqe9j/) — Simeon Stoykov, Jul 21 '15 at 07:39
you add manualy your foreign key in entity framework model or you do an update of your model and the foreign key appeared ? — Paul, Jul 21 '15 at 07:47
I just had the data in MSSQL DB and imported in in Visual Studio with Database first and it automaticaly made all models for me with all the realtions. — Simeon Stoykov, Jul 21 '15 at 07:49
Yes i didn't change anything. I am actually getting part of the data in the table products and then i get that exeption. I changed little bit the code - instead of ("@measure_id", product.Measure); i tried: ("@measure_id", product.Measure.Id); because the Measure is the naviagtion property and i tried to get its Id that way but the result is the same. — Simeon Stoykov, Jul 21 '15 at 07:52
hum, the measure_id you want to add exist in your other table ? it's your foreight key right ? — Paul, Jul 21 '15 at 07:53
Yes it is the foreign key for the products table and i get it from the measures table. — Simeon Stoykov, Jul 21 '15 at 07:56
Are you sure you have the right measure id because this error appeared when you want to add data and the id pass doesn't exist — Paul, Jul 21 '15 at 07:57
look at this response [link](http://stackoverflow.com/questions/12966626/mysql-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) i thinks can helps you to verify your data — Paul, Jul 21 '15 at 07:59
Ok so Console.WriteLine(product.Measure.Id); in the foreach loop where i get all products returns the right value for every measure_id for every product, i checked it but the error still occurs. — Simeon Stoykov, Jul 21 '15 at 08:02
ok so your id is a string or a int in the database ? and in your inser ? — Paul, Jul 21 '15 at 08:04
cast your measure id in int32 after insert it or verify that are int32 or int 64 in the two way — Paul, Jul 21 '15 at 08:14
something like that ? CAST(@measure_id AS SIGNED); in the insert query ? — Simeon Stoykov, Jul 21 '15 at 08:18
measure id is int(11) in mysql and just int in MSSQL does this matter? And i tried to make it int(11) but i couldnt do that in MSSQL int just turns to int. — Simeon Stoykov, Jul 21 '15 at 08:22
i don't no sorry. before execute your query display here for watch if all off the data are ok and verify the data in your 2 DB because i think is an error in data in db or in your query — Paul, Jul 21 '15 at 08:30
I just deleted the whole DB schema from MySQL, recreated it and than run the code and now everything runs and imports fine. Maybe the old data was the reason for that error. — Simeon Stoykov, Jul 21 '15 at 08:41

Can't import foreign key in MySQL DB using C#, Visual Studio and MySQL.Data

0 Answers0