Fetching child records using Stored Procedures with LINQ to SQL

You can consume stored procs rather than the standard dynamic sql for accessing child objects. To do this, set up your fetch stored procs and make sure that they return the correct data type (not the standard custom generated type for stored procedures). To load a child collection, create a method on the partial implementation of your context. Name the function "LoadCs" where "C" is the name of the child property accessor from the parent object in the designer. This function will take a type as the parent type as a parameter and return an IEnumerable of the Child type. The names you use must agree with the names of the types and properties in your entities in order for this to work.

      Public Class CustomDataContext

     ‘Load a child collection

     Public Function LoadCs(ByVal parent As T) As IEnumerable(Of C)

         Return Me.LoadCs(parent.ID)

     End Function

  End Class

The process to load a single child is similar. In this case, the function needs to be the singularized version of your entity and the return type will be the actual entity type rather than an IEnumerable as follows:

     ‘Load a single child

     Public Function LoadC(ByVal parent As P) As C

         Return Me.LoadC(parent.CId).SingleOrDefault

     End Function

Using these methods causes the context to lazy load the child objects. The default change tracking implementation will continue to work and if you have replaced the runtime behavior for the CUD operations with stored procedure implementations, they will be used just as if you fetched the objects through LINQ generated dynamic SQL.

Posted on 6/23/2008 8:14:00 AM - Comments (3)
Categories: VB Dev Center , LINQ , VB

  • #Posted on 9/13/2008 11:59:00 AM by Andrea Adami
    Hi Jim, I think the the last part of your sample would look better as follow: ‘Load the parent Public Function LoadP(ByVal child As C) As P Return Me.LoadP(child.PId).SingleOrDefault End Function I hope i'm right, otherwise i don't understand the meaning of the function. Thanks for the sample Andrea Adami
  • #Posted on 9/13/2008 3:45:00 PM by Jim
    You could use the parent analogy. Load single child works if there is a 1:0-1 relationship between the tables. This could apply for a parent or otherwise simply related table.
  • #Posted on 9/22/2008 9:31:00 AM by Joe
    Is this only possible in vb? I've tried in c# and can't get it to work at all! Would you have a wroking sample of this? Thanks!



 
Save Comment