In a datacontext are inserted values not available within the datacontext until after submitchanges?

Question

I'm going through an XML file of articles and the journalist(s) that wrote them. As we are adding the articles into _Data our datacontext we may come across a journalist that needs adding so we do this:

newJourno = New journalist With {.name = strJournalist}
_Data.journalists.InsertOnSubmit(newJourno)
.articles_journalists.Add(New articles_journalist With {.id_journalist = newJourno.id,         .id_article = .id})

However subsequently we may come across this same journalist again and nothing is returned when we do this:

Dim journo = _Data.journalists.Where(Function(s) s.name = strJournalist).SingleOrDefault

So it uses the code above again to insert the same journalist again.

Once all of our inserts are done we do a submitchanges. At this point it has a head fit:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_articles_journalists_journalists'. The conflict occurred in database 'blah', table 'journalists', column 'id'. The statement has been terminated.

From looking through the sql generated in sql profiler you can see that it is trying to add some journalists more than once, this will fail as the name must be distinct. The subsequent records that are trying to be inserted with these journalists are failing as the journalist wasn't updated.

Surely if I have a collection of journalists, add some to it and then look in my collection I should see all of them and not just the original ones. I can fudge it I guess by doing a submitchanges but that seems a bit silly.

Thanks in advance,

Dave.

Answer 1

If you want to add two child-parent rows to the database, you must assign the entity, instead of the Id column, the Id will be autogenerated and will be available only after the submit changes.

You have to do a articles_journalist object, and then assign the newJourno entity to this:

articles_journalist.journalist = newJourno;

Answer 2

CMS is right about needing to assign the object, not the id.

However this doesn't seem to get around the problem of the datacontext not realising that it has had new stuff added to it until you submitchanges. I can only presume this is by design and therefore I am now calling submitchanges as and when the code inserts objects that we later search for.

Answer 3

"the name must be distinct."

This is a serious design flaw. Person names are never unique.