LINQ to SQL

Question

While trying to use LINQ to SQL I encountered several problems.

I have table persons:

• int ID
• string firstName
• string lastName

And table notes that has:

• int ID
• string noteText
• string createdBy
• datetime creationDate
• int PersonID

PersonID is a foreign key and the relationship is 1:n

I tried to use LINQ to SQL to create a person and some notes for each person.

Person person =  new person();
Person.firstName = "me";
Person.note = new note();   
Person.note.noteText = "some text…"; 

_DataContext.Persons.InsertOnSubmit(person);   
_DataContext.SubmitChanges();

The problem is that the person object doesn't yet exist in the DB so it doesn't have an ID yet. So the note.personID filed has a 0 value… (the ID field is an identity field in the sql server)

The only solution for this that I found is to create a person , submitchanges and then create a note and submitchanges again.

Am I missing something here or maybe that’s the way one should work with LINQ to SQL?

How can I add multiple notes per person with LTS? I have a 1:n relationship and I don't see it with LTS.

If a person has 10000 notes, I don't want the person object constructor to load all the notes he has. I want to load them only when I refer to them. How can I config LTS to load the notes on demand?

Answer 1

If you aren't using the LinqToSql class designer, you should think about using it. The classes it generates will support the insertion scenario you outlined.

I can tell you aren't using the designer because it would give you a Notes (plural) property on the Person... as Person is 1 to Many with Notes.

How can I config LTS to load the notes on demand?

The designer will generate a property of type EntitySet(Note), which will load Notes on demand.