Return Multiple Objects from a Single Record using LINQ to SQL

Question

I have a stored procedure that returns a record like this (I simplified it)

SELECT 
    Id, FirstName, LastName, Father.FirstName, Father.LastName
FROM Profiles 
Left JOIN Profiles Father on Profiles.FatherId = Father.Id

I want this record to be returned as 2 properties of an object

var result = dataContext.StoredProcedure();
Profile = result.Profile;
Father = result.Father;

My question is, how do i take the single record

SELECT 
    Id, FirstName, LastName, Father.FirstName, Father.LastName

And split half of it to become one property/object (Profile), and the other half to become the other property/object (Father)

Reason For Using a Stored Procedure The actual sql statement includes a Full Text search, which is not supported in linq, so i cannot use a normal linq query.

why use a SP to achieve this? is it a real constraint from your db guy's or? — Tim Mahy
– Tim Mahy, Commented May 1, 2011 at 8:52
Because the actual statement uses full text search which isn't supported by linq. I just simplified the code — Shrage Smilowitz
– Shrage Smilowitz, Commented May 1, 2011 at 15:13

Karuna Sorate · Accepted Answer · 2011-06-07 07:02:03Z

2

If I understand your problem correctly, you need two strongly typed objects, 'Profile' & 'Father' from your SQL result set. I see two options to do this;

1> Get your results as is from the stored proc and create your objects as stated below;

Profile p = new Profile P { Id=result.Id, FirstName= result.FirstName, LastName=result.LastName}; 
//and similar code for creating the 'Father' object

2> In your stored proc, get the results in a table variable first and then select two results from this table variable, first one 'Profile', second 'Father'. Your LINQ to SQL method calling the stored proc should be set to allow multiple result-sets. You can then access the results like;

Profile = result.Profile;
Father = result.Father;

answered Jun 7, 2011 at 7:02

Karuna Sorate

1735 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

abatishchev · Accepted Answer · 2011-05-01 08:50:26Z

0

Create strongly-type object with 2 properties: Profile, Father
Create a navigation property Father for Profile object, so receiving Profile a consumer could easy navigate to Father

answered May 1, 2011 at 8:50

abatishchev

101k88 gold badges304 silver badges443 bronze badges

1 Comment

Shrage Smilowitz Over a year ago

I suppose this is a suggestion to remove the sp and use linq instaed. This isn't an option as I explained in the comments at the original question

Collectives™ on Stack Overflow

Return Multiple Objects from a Single Record using LINQ to SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related