CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Shrage Smilowitz
Shrage Smilowitz

Reputation: 25162

Return Multiple Objects from a Single Record using LINQ to SQL

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.

Upvotes: 1

Views: 1602

Answers (2)

Karuna Sorate
Karuna Sorate

Reputation: 173

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;

Upvotes: 2

abatishchev
abatishchev

Reputation: 100248

  • 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

Upvotes: 0

Related Questions