Sql – LINQ to SQL

linq-to-sql

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?

Best Answer

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.

Related Topic