Help needed for LINQ To SQL operations (insert/update) with nested POCO's

Question

Ok well I've been trying to convert my model to use LINQ but didn't want to throw away my current DTO's and their interfaces which are scattered through the domain.

I managed to find this blog post which has outlined the process quite nicely:

Achieving POCOs in LINQ To SQL

I have the managed to get the retrieval of records to objects working properly, however, due to the nested nature of my my model, I can't seem to get addition working for the child objects. That is, if I create a child object, and set the reference to the desired parents object, LINQ to SQL still throws an exception stating the child's reference to the parent is null. If I attempt to add a plain old parent object, it succeeds, but adding child objects directly fails

Here is my failing test:

    [Test]
    public void AddSelectionShouldAddSelectionToMarket()
    {
        Market market = (Market) new Repository().GetMarket(1);

        Selection selection = new Selection();
        selection.Market = market;

        new Repository().AddSelection(selection);

        Assert.IsTrue(selection.SID > 0);
    }

Here is the error message:

System.InvalidOperationException: An attempt was made to remove a relationship between a Market and a Selection. However, one of the relationship's foreign keys (Selection.MID) cannot be set to null.

The relevant parts of the 2 objects:

[DataContract]
public class Selection : ISelection
{
    private int mID;
    [DataMember]
    public int MID
    {
        get { return this.mID; }
        set { this.mID = value; }
    }

    private Market market;
    [DataMember]
    public Market Market
    {
        get { return this.market; }
        set
        {
            this.market = value;
            this.mID = value.MID;
        }
    }
}

[DataContract]
public class Market : IMarket
{
    private int mID;
    [DataMember]
    public int MID
    {
        get { return this.mID; }
        protected set { this.mID = value; }
    }

    private List<Selection> selections;
    [DataMember]
    public List<Selection> Selections
    {
        get { return this.selections; }
        set
        {
            this.selections = value;
            // For LINQ
            foreach (Selection selection in selections)
            {
                selection.MID = mID;
                selection.Market = this;
            }
        }
    }
}

My DA code:

        MarketsDataContext context = new MarketsDataContext();

        DataLoadOptions options = new DataLoadOptions();
        options.LoadWith<Selection>(s => s.Prices);
        options.LoadWith<Market>(m => m.Selections);

        context.LoadOptions = options;
        return context;

and;

    public void AddSelection(ISelection selection)
    {
        using (MarketsDataContext context = MarketsDataContext.GetContext())
        {
            context.Selections.InsertOnSubmit((Selection) selection);
            context.SubmitChanges();
        }
    }

And finally my XML mapping:

  <Table Name="dbo.Markets" Member="Markets">
    <Type Name="Market">
      <Column Name="MID" Member="MID" Storage="mID" DbType="Int NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Association Name="FK_Market-Selections" Member="Selections" Storage="selections" ThisKey="MID" OtherKey="MID" DeleteRule="NO ACTION"  />
    </Type>
  </Table>

  <Table Name="dbo.Selections" Member="Selections">
    <Type Name="Selection">
      <Column Name="SID" Member="SID" Storage="sID" DbType="Int NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="MID" Member="MID" Storage="mID" DbType="Int NOT NULL" />
      <Association Name="FK_Market-Selections" Member="Market" Storage="market" ThisKey="MID" OtherKey="MID" IsForeignKey="true" />
    </Type>
  </Table>

So, can anyone point me in the right direction? I've been searching for hours...

Edit:

Here's my stacktrace for my test failure:

at System.Data.Linq.ChangeTracker.StandardChangeTracker.StandardTrackedObject.SynchDependentData()
at System.Data.Linq.ChangeProcessor.ValidateAll(IEnumerable`1 list)
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges()
at BetMax.DataModel.Repository.AddSelection(ISelection selection) in Repository.cs: line 68
at BetMax.DataModel.Test.ModelTest.AddSelectionShouldAddSelectionToMarket() in ModelTest.cs: line 65

And my GetMarket method:

    public IMarket GetMarket(int MID)
    {
        Market market;
        using (MarketsDataContext context = MarketsDataContext.GetContext())
        {
            market = context.Markets.Single(m => m.MID == MID);
        }
        return market;
    }

Edit 2:

Well, adding

DeleteOnNull="true"

to Selections foreign key in the XML mapping has removed the foreign key error, but now I'm getting a null reference on one of Selections's child objects, saying its reference to Selection is null even though Selection is being initialised with none of its variables set (outside the foreign keys). I even tried creating a child object, and set its references correctly but am still getting this error:

System.NullReferenceException: Object reference not set to an instance of an object.
at BetMax.DTO.Price.set_Selection(Selection value) in Price.cs: line 25
at System.Data.Linq.Mapping.PropertyAccessor.Accessor`3.SetValue(ref T instance, V value)
at System.Data.Linq.Mapping.MetaAccessor`2.SetBoxedValue(ref Object instance, Object value)
at System.Data.Linq.ChangeProcessor.ClearForeignKeysHelper(MetaAssociation assoc, Object trackedInstance)
at System.Data.Linq.ChangeProcessor.ClearForeignKeyReferences(TrackedObject to)
at System.Data.Linq.ChangeProcessor.PostProcessUpdates(List`1 insertedItems, List`1 deletedItems)
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges()
at BetMax.DataModel.Repository.AddSelection(ISelection selection) in Repository.cs: line 68
at BetMax.DataModel.Test.ModelTest.AddSelectionShouldAddSelectionToMarket() in ModelTest.cs: line 69

Price is another object, constructed in the same that that Selection is related to Market (1 selection has many prices, 1 market has many selections) etc etc.

Answer 1

Could you tell where the exception occurs and write the GetMarkets method here?

Answer 2

I'd suggest sending your code to Sidar Ok. He's a nice guy and will point you in the right direction. Or at least post a comment on his blog pointing him to your question here.

Answer 3

I guess the problem is in your test method. You created a Repository with a DataContext but you did your submits with another one.

[Test]
public void AddSelectionShouldAddSelectionToMarket()
{
    Market market = (Market) new Repository().GetMarket(1);

    Selection selection = new Selection();
    selection.Market = market;

    new Repository().AddSelection(selection);

    Assert.IsTrue(selection.SID > 0);
}

Create a Repository and use it in the test method.

[Test]
public void AddSelectionShouldAddSelectionToMarket()
{
    Repository repository = new Repository();
    Market market = (Market) repository.GetMarket(1);

    Selection selection = new Selection();
    selection.Market = market;

    repository.AddSelection(selection);

    Assert.IsTrue(selection.SID > 0);
}

Answer 4

Just a guess but it might be here

public Market Market
{
    get { return this.market; }
    set
    {
        this.market = value;
        this.mID = value.MID;
    }
}

What happens when the value you set to Market is null? The last line of that will be invalid since it wont be able to resolve null.MID. Maybe you need this for your setter:

    set
    {
        this.market = value;
        this.mID = (value == null) ? null : value.MID;
    }

Also your MID would have to be nullable

int? MID

Answer 5

For your new issue; The problem occurs on null assignment to selection property of Price. Did you do that by your code? Could you again give the code part that you got the exception? I mean assignment to Price entity...

Edit according to comment: I guess it is because of null control exception as we mentioned before on GeekyMonkeys post. In initialization of Selection class the Price property needs to set as null but when null is assigned to, it throws null reference. So you have to do a null control in set of price property.

private List<Price> prices
[DataMember]
public List<Price> Prices
{
    get { return this.prices; }
    set
    {
        if(value != null)
        {
          this.pricess = value;
          // For LINQ             
          foreach (Price price in prices)
          {
            price.MID = mID;
            price.Selection = this;
          }
       }
    }
}

Answer 6

I know it's been a while and you've probably already resolved the issue, but maybe not...

I'm assuming that your data structure is similar to this:

Market
======
Market_ID      int not null identity (1, 1)


Selection
=========
Selection_ID   int not null identity (1, 1)
Market_ID      int (FK to Market)
Selection_Name varchar(50)

To add a new Market and a new Selection simultaneously:

Selection selection = new Selection();
Market market = new Market();

market.Selections.Add(selection);
DataContext.Markets.InsertOnSubmit(market);
DataContext.SubmitChanges();

To add a new Selection to an existing Market:

Selection selection = new Selection();
Market market = DataContext.Markets.Where(a => a.Market_ID == 7).Single();

market.Selections.Add(selection);
DataContext.SubmitChanges();

To update the first Selection in a Market:

Selection selection = DataContext.Markets.Where(a => a.Market_ID == 7).Selections.First();

selection.Selection_Name = "New Name";
DataContext.SubmitChanges();