Problem with m:n relationship with Entity Framework
-
- Posts: 4
- Joined: Thu 19 Jun 2008 17:06
Problem with m:n relationship with Entity Framework
Hello,
I'm wondering if there are any known issues with the ADO.Net Entity Framework and MyDirect.Net. I have a simple scenario:
Tables: Manufacturers -->Mfgaddress--> Addresses.
Entity Objects: Manufacturs has an EntityCollection of Addresses. If I create a new Manufacturer, add a single new Address to it, and use the Entity Context like so:
ctx.AddToManufacturers(m);
int affected = ctx.SaveChanges();
Then I get:
---- CoreLab.MySql.MySqlException : Cannot add or update a child row: a foreign key constraint fails (`audioland/mfgaddress`, CONSTRAINT `FK_mfg` FOREIGN KEY (`manufacturerid`) REFERENCES `manufacturers` (`ManufacturerId`))
It would seem that it's trying to create the in-between relationship table before it should?
I'm wondering if there are any known issues with the ADO.Net Entity Framework and MyDirect.Net. I have a simple scenario:
Tables: Manufacturers -->Mfgaddress--> Addresses.
Entity Objects: Manufacturs has an EntityCollection of Addresses. If I create a new Manufacturer, add a single new Address to it, and use the Entity Context like so:
ctx.AddToManufacturers(m);
int affected = ctx.SaveChanges();
Then I get:
---- CoreLab.MySql.MySqlException : Cannot add or update a child row: a foreign key constraint fails (`audioland/mfgaddress`, CONSTRAINT `FK_mfg` FOREIGN KEY (`manufacturerid`) REFERENCES `manufacturers` (`ManufacturerId`))
It would seem that it's trying to create the in-between relationship table before it should?
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
-
- Posts: 4
- Joined: Thu 19 Jun 2008 17:06
I am using MySQL 5.0. I turned on SQL logging in mysql and I can see the following:
080623 11:51:01 1 Query insert into manufacturers(ManufacturerId, Name, URL)
values (0, 'Damon\s Audio Extravaganza', 'http://www.damonpayne.com')
1 Query insert into manufactureraddresses(ManufacturerAddressId, ManufacturerId, AddressType, Line1, Line2, City, State, Zip)
values (0, 0, 0, '1313 Mockingbird Lane', null, 'Gloomsville', 'WI', '12345')
1 Query ROLLBACK
In between there, it would seem, there should have been a query to get the auto-incremented Primary key from "manufacturers" and set that foreign key value on the associated manufactureraddresses object. (Yes, I changed from m:n to 1:n, but its still showing the same behavior) Without that query, the 2nd insert of course fails with a constraint violation.
080623 11:51:01 1 Query insert into manufacturers(ManufacturerId, Name, URL)
values (0, 'Damon\s Audio Extravaganza', 'http://www.damonpayne.com')
1 Query insert into manufactureraddresses(ManufacturerAddressId, ManufacturerId, AddressType, Line1, Line2, City, State, Zip)
values (0, 0, 0, '1313 Mockingbird Lane', null, 'Gloomsville', 'WI', '12345')
1 Query ROLLBACK
In between there, it would seem, there should have been a query to get the auto-incremented Primary key from "manufacturers" and set that foreign key value on the associated manufactureraddresses object. (Yes, I changed from m:n to 1:n, but its still showing the same behavior) Without that query, the 2nd insert of course fails with a constraint violation.
-
- Posts: 43
- Joined: Thu 19 Jun 2008 14:30
-
- Posts: 4
- Joined: Thu 19 Jun 2008 17:06
Exception: System.Data.UpdateException with Inner exception CoreLab.MySql.MySqlException
CREATE TABLE `manufactureraddresses` (
`ManufacturerAddressId` int(10) unsigned NOT NULL auto_increment,
`ManufacturerId` int(10) unsigned NOT NULL,
`AddressType` smallint(5) unsigned NOT NULL,
`Line1` varchar(255) NOT NULL,
`Line2` varchar(255) default NULL,
`City` varchar(45) NOT NULL,
`State` varchar(45) NOT NULL,
`Zip` varchar(10) NOT NULL,
PRIMARY KEY (`ManufacturerAddressId`),
KEY `FK_ManufacturerAddresses_Manufacturer` (`ManufacturerId`),
CONSTRAINT `FK_ManufacturerAddresses_Manufacturer` FOREIGN KEY (`Manufacturer
d`) REFERENCES `manufacturers` (`ManufacturerId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `manufacturers`
--
CREATE TABLE `manufacturers` (
`ManufacturerId` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL,
`URL` varchar(255) default NULL,
PRIMARY KEY (`ManufacturerId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
C#
Manufacturer dummy = new Manufacturer();
dummy.Addresses.Add(
new ManufacturerAddress()
{
AddressType = (short)AddressTypes.Primary,
City = "Gloomsville",
State = "WI",
Zip = "12345",
Line1 = "1313 Mockingbird Lane"
});
dummy.Name = "Damon's Audio Extravaganza";
dummy.URL = "http://www.damonpayne.com";
using (AudioLandEntities ctx = new AudioLandEntities())
{
ctx.AddToManufacturers(m);
int affected = ctx.SaveChanges();
}
http://blogs.msdn.com/dsimmons/pages/en ... k-faq.aspx
CREATE TABLE `manufactureraddresses` (
`ManufacturerAddressId` int(10) unsigned NOT NULL auto_increment,
`ManufacturerId` int(10) unsigned NOT NULL,
`AddressType` smallint(5) unsigned NOT NULL,
`Line1` varchar(255) NOT NULL,
`Line2` varchar(255) default NULL,
`City` varchar(45) NOT NULL,
`State` varchar(45) NOT NULL,
`Zip` varchar(10) NOT NULL,
PRIMARY KEY (`ManufacturerAddressId`),
KEY `FK_ManufacturerAddresses_Manufacturer` (`ManufacturerId`),
CONSTRAINT `FK_ManufacturerAddresses_Manufacturer` FOREIGN KEY (`Manufacturer
d`) REFERENCES `manufacturers` (`ManufacturerId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `manufacturers`
--
CREATE TABLE `manufacturers` (
`ManufacturerId` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL,
`URL` varchar(255) default NULL,
PRIMARY KEY (`ManufacturerId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
C#
Manufacturer dummy = new Manufacturer();
dummy.Addresses.Add(
new ManufacturerAddress()
{
AddressType = (short)AddressTypes.Primary,
City = "Gloomsville",
State = "WI",
Zip = "12345",
Line1 = "1313 Mockingbird Lane"
});
dummy.Name = "Damon's Audio Extravaganza";
dummy.URL = "http://www.damonpayne.com";
using (AudioLandEntities ctx = new AudioLandEntities())
{
ctx.AddToManufacturers(m);
int affected = ctx.SaveChanges();
}
http://blogs.msdn.com/dsimmons/pages/en ... k-faq.aspx
-
- Posts: 43
- Joined: Thu 19 Jun 2008 14:30
-
- Posts: 4
- Joined: Thu 19 Jun 2008 17:06