Linq to SQL datacontext not updated for foreign keys

Question

I am writing a database test with a repository using L2S. My database has a Manifest object and an AllocatedTransaction object. The AllocatedTransaction object has a foreign key for the manifest identifier. DDL looks something like this:

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

In my test, I check if AllocatedTransactions are returned with a manifest. The test is as follows:

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

_testDataProvider simply adds entries to the database using the passed to dataContext . The FindById method is as follows:

 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 that the Manifest object returning from the DataContext does not have an AllocateTransaction associated with it. The strange thing is that pre-existing do manifestos come back with AllocatedTransactions attached. Can I use the same DataContext to insert records and retrieve records? Is this a bug with L2S?

Answer 1

If I understand your question, is that what you are 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 do not need to manually retrieve the AllocatedTransaction objects associated with the manifest. Just retrieve the Manifest object, as I did with the newManifest object, and all associated AllocatedTransaction objects should follow.

UPDATE:

It looks like you are trying to connect in the wrong direction. You need to bind the selected Transact to the manifest, and not vice versa:

 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 the Manifest and AllocatedTransaction records already exist. I highly recommend you ** not * pre-populate the ManifestID field in the AllocatedTransactions object. If you add it, as I showed above, the LINQ mechanism will automatically allow and update the value of the foreign key. If you set the value ahead of time, it may reset the ChangeSet and assume that you are trying to add a new record, rather than join an existing one.