Null values with mySQL

[mobyscore]

I am using LINQ with MySQL and am having problems null values.

I have two tables; "Issue" and "Fix". There is a foreign key in the "Issue" table such that "Issue.FixID" references "Fix.ID". Cascade delete and update are on for this relationship.

One "Fix.ID" can statisfy multiple "Issues". But an "Issue" may have no "Fix" in which case its "FixID" is null on the database.

I have used your Enity Developer to produce the entity classes. The code it generated for the FixID property is:

_
Public Property FixID() As System.Int32
Get
Return Me._FixID
End Get
Set
If ((Me._FixID = value) _
= false) Then
If Me._aqtweb_fix.HasLoadedOrAssignedValue Then
Throw New System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException
End If
Me.OnFixIDChanging(value)
Me.SendPropertyChanging
Me._FixID = value
Me.SendPropertyChanged("FixID")
Me.OnFixIDChanged
End If
End Set
End Property

When I look at "Issue.FixID" for an "Issue" with this values a "Null", the property returns zero. But specifying zero for an update or insert does not (obviously) return null to the database - it tries to insert zero which causes the operation to fail as there is no "Fix.ID" with a value of zero.

Please can you tell me how to specify a null database value via LINQ when I update an existing "Issue" (i.e. how to set "Issue.FixID" = null) and when I insert a new "Issue" with its "Issue.FixID" = null?

I am using:

Entity developer standard V1.0.50
dbForge Fusion for MySQL standard 3.0.183
dotConnect for mySQL V5.0.12

Thanks

[AndreyR]

This problem was discussed here:
http://devart.com/forums/viewtopic.php?t=14044

[mobyscore]

I've done as you say and used the code supplied by Zero-G in the last post on the topic. I am still unable to enter a row in my 'Issue' table with a null value fro FixID. The error is:

Cannot add or update a child row: a foreign key constraint fails (`mobyscore/aqtweb_issue`, CONSTRAINT `aqtwebissue_FK1` FOREIGN KEY (`FixID`) REFERENCES `aqtweb_fix` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE)

I can enter the row OK with a null FixID in MySQL Query Browser.

It is not clear to me how I should specify a null in the VB when inserting a new 'Issue'. I get a failure with:

If Val(lstNewFix.SelectedValue) > 0 Then
NewIssue.FixID = Val(lstNewFix.SelectedValue)
Else
NewIssue.FixID = Nothing
End If

Thanks for your help

[AndreyR]

Could you please send me (support * devart * com, subject "LINQ: Nothing treating") a small test project illustrating this problem?

[Zero-G.]

Hey

I do not know, if it would help, or not.

But try instead of NOTHING : DbNull.Value.
This is a Method of VB.NET for such problems.