Turn Function or Stored Procedure Result into "live" Result for LINQ

Question

Is it possible to turn result sets obtained in LINQ through a stored procedure or function call into a "live" set of objects of which I can retrieve Foreign Key related objects?

If, for example, my stored procedure returns a set of rows (= LINQ objects) of type "Contact", then I can't seem to obtain Contact.BillingAddress (which is related by Foreign Key).

Any idea how to make this work?

Answer 1

You can do this in the Linq to SQL designer by dragging a stored procedure from the server explorer over to the "methods" pane, which is on the right hand side of the objects pane.

Then, you will have a method on your datacontext that will return a type of ISingleResult. You can optionally override this method to map to any type from the model that you wish.

Here are a couple resources for help: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/1667a989-08a0-402c-9358-e4637406a75f/

http://davidhayden.com/blog/dave/archive/2007/08/07/CreatingDataAccessLayerLINQToSQLStoredProceduresSupport.aspx

Answer 2

You have to establish the relationship between the tables explicitly. The LINQ to SQL designer might be able to do this, but I prefer writing the code, like this:

[Table(Name="Contact")]
public class Contact
{
   [Column(Id=true)]
   public int ContactID;
   [Column]
   public string BillingAddressID;
   private EntityRef<BillingAddress> _BillingAddress;    
   [Association(Storage="_BillingAddress", ThisKey="BillingAddressID")]
   public BillingAddress BillingAddress {
      get { return this._BillingAddress.Entity; }
      set { this._BillingAddress.Entity = value; }
   }
}

[Table(Name="BillingAddress")]
public class BillingAddress
{
   [Column(Id=true)]
   public string BillingAddressID;
   ...
   private EntitySet<Contact> _Contacts;
   [Association(Storage="_Contacts", OtherKey="BillingAddressID")]
   public EntitySet<Contact> Contacts {
      get { return this._Contacts; }
      set { this._Contacts.Assign(value); }
   }
}

Answer 3

Simply drag the Contacts table and the BillingAddresses table into your DBML designer, so it imports the Contacts and BillingAddress objects properly, including the relations (I assume you actually already did this, but I mentioned it non the less).

Next change the stored procedure/function in DBML to declare that it returns a Contacts type, see How to: Change the Return Type of a DataContext Method (O/R Designer). Now the return of the procedure will be a 'live' Contacts object.