Actions


Subscribe in a reader
Subscribe by Email

Follow jimwooley on Twitter
View Jim Wooley's profile on LinkedIn

Sign in

My Books:

Linq In Action cover
Buy from Amazon
Purchase Ebook

Fetching child records using Stored Procedures with LINQ to SQL

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(7)
Categories: VB Dev Center LINQ VB
Comments:
  • Gravatar 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
    Andrea Adami (Posted on 9/13/2008 11:59:00 AM)
  • Gravatar 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.
    Jim (Posted on 9/13/2008 3:45:00 PM)
  • Gravatar 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!
    Joe (Posted on 9/22/2008 9:31:00 AM)
  • Gravatar Is there any way out for above scenario.Because though we add anything in designer.cs of Linq file, the next time when we drag & drop anything on to it,the custom code get removed.Can we change this file dynamically?Thanks for the update.
    Bhavana Rana (Posted on 7/25/2009 7:21:00 AM)
  • Gravatar Bhavana, You shoul never modify the .Designer file directly precisely due to the reason you mentioned. Instead, create another partial class file and put your custom implementation in that custom class.
    Jim (Posted on 7/26/2009 3:44:00 PM)
  • Gravatar I'm really starting to hate linq, having spent the best part of an afternoon trying to work out why the designer ALWAYS enforces a sproc return type of (None)
    Jonathan (Posted on 10/16/2009 11:00:00 AM)
  • Gravatar Jonathan, The tools try to figure out your return type, but sometimes it can't figure out the type, particularly when your sproc could return differing shapes depending on the input values. In those cases, you may need to configure your mappings manually in the partial class.
    Jim (Posted on 10/17/2009 9:59:00 PM)