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?

is your PersonID a foreign key on notes table? If so it should be automatically managed the way you want — JohnIdol
– JohnIdol, Commented Dec 14, 2008 at 18:13

Amy B · Accepted Answer · 2008-12-14 18:48:41Z

3

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.

answered Dec 14, 2008 at 18:48

Amy B

110k21 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Omri Over a year ago

Well actually I am using the designer, I just added my tables with the relations from the DB and it doesn't work like you described. maybe I should configure something to change the way it works? I configured the relationship to be 1 to many and still no notes collection or something...

Amy B Over a year ago

Hmm. Ok. Here's Scott Guthrie's blog about the designer. weblogs.asp.net/scottgu/archive/2007/05/19/… He's showing the insert scenario you describe. Check the properties of the relationship to make sure that it's Person.Id to Note.PersonId

Omri Over a year ago

Yep found the problem, the relationship wasn't configured correctly at the SQL server. now everything is working just great. Thanks allot. Omri.

Collectives™ on Stack Overflow

LINQ to SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related