Linq to SQL datacontext not updating for foreign key relationships

Question

I'm writing a database test against a repository that uses L2S. In my database I have a Manifest entity and an AllocatedTransaction entity. The AllocatedTransaction entity has a foreign key to the Manifest's id. The DDL looks something like this:

Manifest:
Id - int - identity

AllocateTransaction:
Id - int - identity
Quantity - int
ManifestId - FK to Manifest

In my test, I'm checking to see if AllocatedTransactions are coming back with the Manifest. The test looks like this:

[TestMethod]
public void FindByIdTest()
{
    using (data.EZTracDataContext dataContext = new data.EZTracDataContext())
    {
        using (new TransactionScope())
        {
            data.Manifest manifest = _testDataProvider.AddManifest(dataContext);

            data.AllocatedTransaction allocatedTransaction = _testDataProvider.AddEligibilityAllocated(dataContext, 5, manifest.Id);

            ManifestRepository repository = CreateRepository(dataContext);

            var actual = repository.FindById(manifest.Id).AllocatedTransactions;
            var expected = new[] { new domain.AllocatedTransaction(allocatedTransaction.Id, 5, manifest.Id) }.ToList();

            CollectionAssertAreEqual(actual, expected);
        }
    }
}

The _testDataProvider just adds records to the database using the passed in dataContext. The FindById method looks like this:

public domain.Manifest FindById(int id)
{
    var persistedManifest = GetPersistedManifest(id);
    var requestedManifest = GetDomainManifestFromData(persistedManifest);
    return requestedManifest;
}

private Manifest GetPersistedManifest(int manifestId)
{
    return (from manifests in DataContext.Manifests
            where manifests.Id == manifestId
            select manifests).FirstOrDefault();
}

My problem is the Manifest entity coming back from the DataContext does not have the AllocateTransaction associated to it. The strange thing is pre-existing Manifests do come back with their AllocatedTransactions attached. Could using the same DataContext object for inserting the records and retrieving the records be causing this? Is this a bug with L2S?

Answer 1

If I'm understanding your question, is this what you're trying to do?

using (new TransactionScope())
{
    Manifest manifest = new Manifest
    {
        AllocatedTransactions.Add(new AllocatedTransaction
        {
            Quantity = 5
        }
    };

    DataContext.Manifests.InsertOnSubmit(manifest);
    DataContext.SubmitChanges();

    Manifest newManifest = DataContext.Manifests.Where(a => a.ID == manifest.ID).SingleOrDefault(); 

    Assert.AreEqual(manifest.AllocatedTransactions.First().Quantity, newManifest.AllocatedTransactions.First().Quantity);
}

You also don't need to manually retrieve the AllocatedTransaction entities associated with the Manifest. Just retrieve the Manifest entity as I did with the newManifest object and all the associated AllocatedTransaction entities should follow along.

UPDATE:

It looks like you're trying to attach in the wrong direction. You need to attach the AllocatedTransaction to the Manifest, not the other way around:

Manifest manifest = DataContext.Manifests.Single(a => a.ID == 27);
AllocatedTransaction allTrans = DataContext.AllocatedTransactions.Single(a => a.ID == 53);

manifest.AllocatedTransactions.Add(allTrans);
DataContext.SubmitChanges();

This assumes that both the Manifest and AllocatedTransaction records already exist. I would highly recommend that you *not prepopulate the ManifestID field in the AllocatedTransactions object. If you add it as I have demonstrated above, the LINQ engine will automatically resolve and update the foreign key value. If you set the value ahead of time, it may throw off the ChangeSet and assume that you are trying to add a new record rather than attach an existing one.