Null associations

[rodusa]

How do I handle 0 to many associations. For example product and features. Not all products have special features and there might be cases where the features association is null.

Something like this:

product.feature = new feature();
or
product.feature = null;
or
not explicitly specifying any of those

PS: when I try the above I keep getting:
Value cannot be null.
Parameter name: key

thanks

Rod

[AndreyR]

Could you please send me a script of your product and feature tables including the foreign key definition?

[rodusa]

I can only send my real script because that post was just a simple example I used to convey my problem. I know the problem is caused because owner is NULL. "object null reference... null key value"

Tables
real_estate - realstateID PK, AddressID FK, OwnerID FK
address - AddressID FK
owner - OwnerID FK

Associations:
real_estate and address
real_estate and owner
owner and address


protected void btCadastrar_Click(object sender, EventArgs e)
{

btEditContent.Visible = true;

MessageBox.InnerHtml = string.Empty;
MessageBox.Visible = false;
if (ValidateForm() != string.Empty)
{
MessageBox.Visible = true;
MessageBox.InnerHtml = ValidateForm();
btCadastrar.Enabled = true;
return;

}

ImovelAjatoDbDataContext db = new ImovelAjatoDbDataContext();

//ADD NEW REAL ESTATE ADDRESS
address a = new address();
a.City = TxtCity.Text;
a.Street = TxtStreet.Text;
a.PostalCode = TxtPostalCode.Text;
a.Neighboor = TxtNeighboor.Text;
a.State = DpStates.SelectedValue;
db.SubmitChanges();

//ADD NEW REAL ESTATE OBJECT
real_estate re = new real_estate();
re.RefCode = TxtRefCode.Text;
re.Transaction_Type = (chbIsForRent.Checked ? "Aluguel" : "") + ", " + (chbIsForSale.Checked ? "Venda" : "") + ", " + (chbIsForSeason.Checked ? "Season" : "");
re.Construction_Type = DpConstructionType.SelectedValue;
re.Financial_Status = DpFinancialStatus.SelectedValue;
re.Documentation = DpDocumentation.SelectedValue;
re.Description = TxtDescription.Text;
// re.HtmlContent = TxtHtmlContent.Text;
re.BuildingName = TxtBuildingName.Text;
re.Rooms = (string.IsNullOrEmpty(TxtRooms.Text) ? 0 : long.Parse(TxtRooms.Text));
re.Garages = (string.IsNullOrEmpty(TxtGarages.Text) ? 0 : long.Parse(TxtGarages.Text));
re.Suites = (string.IsNullOrEmpty(TxtSuites.Text) ? 0 : long.Parse(TxtSuites.Text));
re.YearConstruction = TxtYearConstruction.Text;
re.Notes = TxtNotes.Text;
if (!string.IsNullOrEmpty(TxtLatitude.Text))
{
decimal.Parse(TxtLatitude.Text);
}

if (!string.IsNullOrEmpty(TxtLongitude.Text))
{
decimal.Parse(TxtLongitude.Text);
}

//re.Latitude = (string.IsNullOrEmpty(TxtLatitude.Text) ? (decimal?)null : decimal.Parse(TxtLatitude.Text));
//re.Longitude = (string.IsNullOrEmpty(TxtLongitude.Text) ? (decimal?)null : decimal.Parse(TxtLongitude.Text));
re.AddressID = a.AddressID;
re.MainPhoto = TxtMainPhoto.Text;
re.address = a;


Owner o = new Owner();
o.address = new address();
if (TxtOwnerID.Text != "Novo")
{
re.OwnerID = Int32.Parse(TxtOwnerID.Text);
re.Owner = o;
}
else
{
//ADD NEW OWNER OBJECT
o.CompanyName = TxtOwnerCompanyName.Text;
o.FirstName = TxtOwnerFirstName.Text;
o.LastName = TxtOwnerLastName.Text;
o.Phone_Home = TxtOwnerPhoneHome.Text;
o.Phone_Cell = TxtOwnerMobile.Text;
o.Phone_Work = TxtOwnerPhoneWork.Text;
o.Email = TxtOwnerEmail.Text;

// CHECK OWNER ADDRESS
if (chbSameAddress.Checked)
{
//OWNER ADDRESS EQUALS TO REAL ESTATE ADDRESS (SAME ADDRESS CHECKBOX IS CHECKED)
o.CompanyName = TxtOwnerCompanyName.Text;
o.FirstName = TxtOwnerFirstName.Text;
o.LastName = TxtOwnerLastName.Text;
o.Phone_Home = TxtOwnerPhoneHome.Text;
o.Phone_Cell = TxtOwnerMobile.Text;
o.Phone_Work = TxtOwnerPhoneWork.Text;
o.Email = TxtOwnerEmail.Text;
o.address = a;
}
else
{
//ADD NEW OWNER ADDRESS
address ao = new address();
ao.Street = TxtOwnerAddress.Text;
ao.City = TxtOwnerCity.Text;
ao.PostalCode = TxtOwnerPostalCode.Text;
ao.Neighboor = TxtOwnerNeighboor.Text;
ao.State = DpOwnerStates.SelectedValue;
db.SubmitChanges(); // create new address
o.address = ao;
}

db.SubmitChanges(); // create new owner

}

re.Owner = o; // update real estate owner

if (!string.IsNullOrEmpty(TxtPrice.Text))
{
//string x = re.Price.GetType().ToString();
re.Price = Convert.ToDecimal(TxtPrice.Text);
}

if (!string.IsNullOrEmpty(TxtTotalArea.Text))
{
re.TotalArea = Convert.ToDecimal(TxtTotalArea.Text);
}

if (!string.IsNullOrEmpty(TxtPrivateArea.Text))
{
re.PrivateArea = Convert.ToDecimal(TxtPrivateArea.Text);
}

re.Notes = TxtNotes.Text;

// Insert RealEstate Object into Collection
db.real_estates.InsertOnSubmit(re);
// save Real Estate
db.SubmitChanges(); //############## ERROR HERE BECAUSE OWNER IS NULL #############

// Rename thumbnails after getting RealEstate new ID
RenameThumbnails(re.RealEstateID.ToString());

// CREATE CATEGORY OBJECT
real_estate_category rec = new real_estate_category();
rec.RealEstateID = Convert.ToInt32(re.RealEstateID);


int cat = GetSelectedNode();


// Insert Category Object into Collection
db.real_estate_categories.InsertOnSubmit(rec);
//Save Category
db.SubmitChanges();

foreach (ListItem item in chbFeatures.Items)
{
if (item.Selected)
{
real_estate_feature rf = new real_estate_feature();
rf.FeatureID = Convert.ToInt32(item.Value);
rf.RealEstateID = re.RealEstateID;
// Insert Feature Object into Collection
db.real_estate_features.InsertOnSubmit(rf);
db.SubmitChanges();
}
}


}

[AndreyR]

Could you please send a script of your three tables to me (support * devart * com, subject "LINQ: Null associations")?
I have made some tests using dotConnect for MySQL 2.0 Beta with the code you have provided (I simplified it for testing purposes) and all scenarios succeeded.

[rodusa]

Ok. I 've tested with SQL Server 2008 and I had no problems. It seems to be a bug with your provider that is looking for NULL associations. To reproduce just add a button and try:

DbDataContext db = new DbDataContext();
real_estate r = new real_estate();
r.RefCode = "test";
// Insert RealEstate Object into Collection
db.real_estates.InsertOnSubmit(r);
db.SubmitChanges();

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for real_estate
-- ----------------------------
CREATE TABLE `real_estate` (
`RealEstateID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RefCode` varchar(50) DEFAULT NULL,
`Description` text,
`HtmlContent` text,
`BuildingName` varchar(255) DEFAULT NULL,
`PrivateArea` decimal(10,2) DEFAULT '0.00',
`TotalArea` decimal(10,2) DEFAULT '0.00',
`Price` decimal(10,2) DEFAULT '0.00',
`Financial_Status` varchar(100) DEFAULT NULL COMMENT 'quitado, financidao',
`Documentation` varchar(100) DEFAULT NULL COMMENT 'escritura lavrada',
`Floors` int(10) unsigned DEFAULT '0',
`Rooms` int(10) unsigned DEFAULT '0',
`Garages` int(10) unsigned DEFAULT '0',
`Suites` int(10) unsigned DEFAULT '0',
`Transaction_Type` varchar(100) DEFAULT NULL COMMENT 'venda, aluguel, temporada',
`Construction_Type` varchar(100) DEFAULT NULL COMMENT 'novo, usado, construcao',
`LastFloor` tinyint(3) unsigned DEFAULT '0' COMMENT 'cobertura',
`Notes` text COMMENT 'observacao',
`StateID` int(11) DEFAULT NULL,
`IsForRend` tinyint(4) DEFAULT '0',
`IsForSale` tinyint(4) DEFAULT '0',
`IsForSeason` tinyint(4) DEFAULT '0',
`IsLastFloor` tinyint(4) DEFAULT NULL,
`YearConstruction` varchar(4) DEFAULT NULL,
`CategoryID` int(11) DEFAULT NULL,
`AddressID` int(10) unsigned zerofill DEFAULT NULL,
`Latitude` decimal(16,6) DEFAULT NULL,
`Longitude` decimal(16,6) DEFAULT NULL,
`MainPhoto` varchar(100) DEFAULT NULL,
`OwnerID` int(11) DEFAULT NULL,
`Date_Created` date DEFAULT NULL,
PRIMARY KEY (`RealEstateID`)
) ENGINE=InnoDB AUTO_INCREMENT=229 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 207872 kB';




SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for owners
-- ----------------------------
CREATE TABLE `owners` (
`OwnerID` int(10) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(100) DEFAULT NULL,
`LastName` varchar(100) DEFAULT NULL,
`CompanyName` varchar(100) DEFAULT NULL,
`CPF` varchar(20) DEFAULT NULL,
`CNPJ` varchar(20) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL,
`Phone_Home` varchar(45) DEFAULT NULL,
`Phone_Work` varchar(45) DEFAULT NULL,
`Phone_Cell` varchar(45) DEFAULT NULL,
`owner_type` varchar(100) DEFAULT NULL COMMENT 'buyer-owner-user',
`AddressID` int(10) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`OwnerID`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for addresses
-- ----------------------------
CREATE TABLE `addresses` (
`AddressID` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`Street` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`State` varchar(255) DEFAULT NULL,
`PostalCode` varchar(20) DEFAULT NULL,
`Neighboor` varchar(255) DEFAULT NULL,
PRIMARY KEY (`AddressID`)
) ENGINE=InnoDB AUTO_INCREMENT=206 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

[AndreyR]

Can you reproduce the problem using dotConnect for Oracle 5.20 Beta?

[rodusa]

I am not using Oracle provider. I am using dotnetconnect for Mysql and I know for sure that your provider has a bug because I tried with sql server 2008 and everything just works as it should.

Rod

[AndreyR]

Sorry I meant dotConnect for MySQL 5.20 Beta.
We plan to release the new Beta build of dotConnects in a week or sooner with a significant amount of bug fixes in it.
I will let you know as soon as this build is available for download.

[please_help_me]

I'm getting the same thing with the Oracle provider.

In my case I have a CUSTOMER table (FIRST_NAME, LAST_NAME, and EMAIL_ADDRESS do not allow NULLS).

If I do a manual psql insert (i.e. INSERT INTO CUSTOMER (FIRST_NAME, LAST_NAME, EMAIL_ADDRESS) everything is fine.

Otherwise if I try the following (as suggested in your documentation) I get the parameter cannot be null error:

CUSTOMER c = new CUSTOMER();
c.EMAIL_ADDRESS = this._email;
c.FIRST_NAME = this._firstName;
c.LAST_NAME = this.LastName;

try
{
ctx.CUSTOMERs.InsertOnSubmit(c);
ctx.SubmitChanges();
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message);

}

If I can't get this working with dotConnect I am absolutely screwed - initial testing with the demo version of this product did not reveal this error.

I am using version 5.20.33.0.
Please help me!!

[AndreyR]

Please make sure that you have set the Auto Generated Value property to true for your Primary Key column(CUSTOMER_OID).
Also I recommend to set the AutoSync property value to OnInsert for obtaining the value of the generated ID in the code after the data is inserted.

[please_help_me]

i've done what you suggested...however i cannot get the generated pk back out.

if do the following, it always returns null (please note the context for this linq query is inside of the business object that just saved itself):

try
{
ctx.CUSTOMERs.InsertOnSubmit(c);
ctx.SubmitChanges();

}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message);
}
}

var ctx2 = new BizMasterDataContext();
ctx2.Connection.ConnectionString = Database.Connection.BizMasterCnString;

var customerOid = (from cust in ctx2.CUSTOMERs where cust.SSN == this._ssn select cust.CUSTOMER_OID).SingleOrDefault();


also, please note that the name of the pk column is CUSTOMER.CUSTOMER_OID

thanks

[AndreyR]

Thank you for the report, we have found the problem. I will let you know as soon as it is fixed.

[please_help_me]

do you have an estimation when this will be fixed? I have an impending QA date for some functionality that is dependent on this fix and want to give my immediate report an accurate idea of when I can push my changes.

thanks for your help...

[AndreyR]

The problem is fixed, look forward to the next build.