Null associations

Null associations

Postby rodusa » Mon 23 Mar 2009 22:38

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
rodusa
 
Posts: 5
Joined: Fri 20 Feb 2009 23:09

Postby AndreyR » Tue 24 Mar 2009 11:07

Could you please send me a script of your product and feature tables including the foreign key definition?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

script

Postby rodusa » Tue 24 Mar 2009 12:05

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();
}
}


}
rodusa
 
Posts: 5
Joined: Fri 20 Feb 2009 23:09

Postby AndreyR » Tue 24 Mar 2009 15:02

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

tables

Postby rodusa » Tue 24 Mar 2009 15:29

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;
rodusa
 
Posts: 5
Joined: Fri 20 Feb 2009 23:09

Postby AndreyR » Wed 25 Mar 2009 08:59

Can you reproduce the problem using dotConnect for Oracle 5.20 Beta?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Not using oracle

Postby rodusa » Wed 01 Apr 2009 22:07

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
rodusa
 
Posts: 5
Joined: Fri 20 Feb 2009 23:09

Postby AndreyR » Thu 02 Apr 2009 07:37

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby please_help_me » Mon 15 Jun 2009 19:57

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!!
please_help_me
 
Posts: 3
Joined: Mon 15 Jun 2009 19:52

Postby AndreyR » Tue 16 Jun 2009 12:51

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

how do you get the inserted pk value in the code

Postby please_help_me » Fri 19 Jun 2009 18:41

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
please_help_me
 
Posts: 3
Joined: Mon 15 Jun 2009 19:52

Postby AndreyR » Tue 23 Jun 2009 13:30

Thank you for the report, we have found the problem. I will let you know as soon as it is fixed.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

thanks Andrey

Postby please_help_me » Tue 23 Jun 2009 13:44

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...
please_help_me
 
Posts: 3
Joined: Mon 15 Jun 2009 19:52

Postby AndreyR » Wed 24 Jun 2009 10:49

The problem is fixed, look forward to the next build.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for MySQL