Fetching child records using Stored Procedures with LINQ to SQL by ThinqLinq

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 - Comment
Categories: LINQ - VB - VB Dev Center -
comments powered by Disqus