LinQ to SQL data insert issue

Question

Hello,

I have two tables Settlement and Violations.SettlementID is primary Key in Settlements table and foreign key in Violations.

In my dbml file I dragged and dropped both the tables and the association is set.

In my VB.net code when I try to insert records into db it is not giving option to add values Violation collection into Settlements

I am trying to read an xml file with Settlements and Violation and insert into SQL. But only Settlement table is getting inserted with records.

I am not able to add _v to settlement s as it is not listed.

Please help

Dim Orders As XDocument = XDocument.Load("c:/inetpub/violations.xml")

    Dim _Orderss = violations.<Collections>.<Settlement>

    For Each _settlement In _settlements
        Dim s As New Settlement

        s.CamisID = _settlement.<CamisID>.Value 
        s.DocketID = _settlement.<DocketID>.Value
        s.RespFName = _settlement.<RespFName>.Value
        s.RespMName = _settlement.<RespMName>.Value
        s.RespLName = _settlement.<RespLName>.Value
        s.DispDate = Date.Parse(_settlement.<DispDate>.Value)

        Dim _violations = _settlement.<ViolationList>.<Violation>

        For Each _violation In _violations
            Dim _v As New Violation

            _v.DocketID = _settlement.<DocketID>.Value
            _v.ViolationInfo = _violation.<ViolationInfo>.Value
            _v.violationCD = _violation.<ViolationCd>.Value
            _v.Severity = Integer.Parse(_violation.<Severity>.Value)
            _v.Point = Integer.Parse(_violation.<Point>.Value)
            _v.Occurence = Integer.Parse(_violation.<Occurence>.Value)


        Next
        _db.Settlements.InsertOnSubmit(s)
        _db.SubmitChanges()
    Next

Answer 1

You need to set the parent property of each child instead of adding children to the parent.

Can you try to set the Settelment property of each _v instead of adding the violations to the settlement?

For Each _settlement In _settlements
    Dim s As New Settlement

    s.CamisID = _settlement.<CamisID>.Value 
    s.DocketID = _settlement.<DocketID>.Value
    s.RespFName = _settlement.<RespFName>.Value
    s.RespMName = _settlement.<RespMName>.Value
    s.RespLName = _settlement.<RespLName>.Value
    s.DispDate = Date.Parse(_settlement.<DispDate>.Value)



    Dim _violations = _settlement.<ViolationList>.<Violation>

    For Each _violation In _violations
        Dim _v As New Violation

        _v.DocketID = _settlement.<DocketID>.Value
        _v.ViolationInfo = _violation.<ViolationInfo>.Value
        _v.violationCD = _violation.<ViolationCd>.Value
        _v.Severity = Integer.Parse(_violation.<Severity>.Value)
        _v.Point = Integer.Parse(_violation.<Point>.Value)
        _v.Occurence = Integer.Parse(_violation.<Occurence>.Value)
        _v.Settelment = s
        _db.Violations.InsertOnSubmit(_v)
    Next

    _db.Settlements.InsertOnSubmit(s)
    _db.SubmitChanges()

Next

Answer 2

Jason, I am not able to add _v to the settlement. When I try to add s.violations.add(_v)

It is throwing error saying invalid .....