Can linq2sql do this without a lot of custom code?

Question

Just dipping my toes into Linq2sql project after years of rolling my own SQL Server DB access routines.

Before I spend too much time figuring out how to make linq2sql behave like my custom code used to, I want to check to make sure that it isn't already "built" in behavior that I can just use by setting up the relationships right in the designer...

Very simple example:

I have two tables: Person and Notes, with a 1 to many relationship (1 Person, many notes), linked by Person.ID->Note.PersonID.

I have a stored procedure (all data access is done via SP's and I plan on continuing that) which makes the Link2SQL a bit more work for me.

sp_PersonGet(@ID int) which returns the person record and sp_PersonNotesGet(@PersonID) which returns a set of related notes for this person.

So far so good, I have an object:

Dim myPerson As Person = db.PersonGet(pnID).Single

and I can access my fields: myPerson.Name, myPerson.Phone etc.

and I can also do a

Dim myNotes As Notes = db.PersonNotesGet(pnID) 

to get a set of notes and I can iterate thru this list like:

For Each N As Note In myNotes
  ( do something)
Next

This all works fine...BUT....What I would prefer is that if I call:

  myPerson = db.PersonGet(pnID)

that I also end up with a myPerson.Notes collection that I can iterate thru.

For Each N As Note In myPerson.Notes
  ( do something)
Next

Basically, Linq2SQl would need to call 2 stored procedures each time a Person record is created...

Is this doable "out of the box", or is this something I need to code around for myself?

Answer 1

This is normally what we would call child collections and they can be eager loaded or lazy loaded. Read these:

http://davidhayden.com/blog/dave/archive/2009/01/08/QuickExamplesLINQToSQLPerformanceTuningPerformanceProfilingORMapper.aspx

http://www.thinqlinq.com/default/Fetching-child-records-using-Stored-Procedures-with-LINQ-to-SQL.aspx

Answer 2

It uses partial classes. You can add your own "Notes" property to your Person class and initialize it in it's GETter function. This would be better than populating the notes every time you load a person record.

Answer 3

I believe that you can do this more or less out of the box, although I haven't tried it -- I don't use stored procedures with LINQ. What you would need to do is change the Insert/Delete/Update methods from using the runtime to use your stored procedures. Then you'd create an Association between your two entity tables which would create an EntitySet of Notes on the Person class and a EntityRef of Person on the Notes class. You can set this up to load automatically or using lazy loading.

The only tricky bit, as far as I can see, is the change from using the runtime generated methods to using your stored procedures. I believe that you have to add them into the data context as methods (by dropping it on your table from the server explorer in the designer) before it is available to use instead.