Converting a stored procedure output into Objects through LINQ

Question

My stored procedure returns data in following format

Hierarchy table:

ParentId ChildId  ParentName  ChildName
  1         9     AAA          BBB
  1        10     AAA          CCC
  1        11     AAA          DDD

This data can be linked to a main table which holds other properties of parent and child entities such as Name etc.

Person table:

  Id     Name    Age
   1      AAA     40

Id in Person table is linked to Hierarchy table.

I need to convert this to following

Public Class HierarchyData
{
      public Person Parent {get;set;}
      List<Person> Children {get;set}
}

I am calling a stored procedure and storing output in var.

this.context.ExecuteQuery<Hierarchy>(MyStoreProc, Paramerters);

Public Class Hierarchy
{
   public int ParentId {get;set;}
   public int ChildId {get;set;}
}

Can you please suggest how it can be converted to List<HierarchyData>?

Can you show some more code, especially the one that reads the data from the stored procedure? What have you tried so far? — Markus
– Markus, Commented Mar 14, 2014 at 11:43
So your getting two ids from your database and you want to store them as these Person objects? where's the rest of the data coming from (name and age) or is that still in the database? — GrahamHull
– GrahamHull, Commented Mar 14, 2014 at 11:59
If you are going to read a stored procedure to convert the result sets into a list of IEnumerable objects, why not map this in EF directly? You would have the entities already, and instead of using stored procedures, you would use Linq to SQL directly? You are tyring to achieve the same thing, yet the other way around. — Mez
– Mez, Commented Mar 14, 2014 at 12:01
Check here : stackoverflow.com/questions/20916873/…. Doing the same thing, however from mapped entities directly. — Mez
– Mez, Commented Mar 14, 2014 at 12:04
My Store Proc does Recursive CTEs (Queries) to do some business logic. I have changed my store proc to return Parent Name and Child Name as Well. All i want now is to somehow convert that to List of HierarchyData class. Some Sort of GroupBy ParentId thing is Required here Perhaps. — InTheWorldOfCodingApplications
– InTheWorldOfCodingApplications, Commented Mar 14, 2014 at 12:09

Wasif Hossain · Accepted Answer · 2014-03-14 13:10:20Z

1

Say, the list is of type List<Hierarchy>. Then you may run the following code:

var hierarchyDataList = 
list
 .GroupBy(x => x.ParentId)
 .Select(g => new HierarchyData
                  {
                      Parent = new Person
                                   {
                                       Id = g.Key
                                       Name = g.First().ParentName  // NAME FIELD ADDED
                                   },
                      Children = g.Select(x => new Person 
                                                   { 
                                                       Id = x.ChildId, 
                                                       Name = x.ChildName
                                                   }
                                         )
                  }
        );

EDIT

Name field has been added in each Parent property of HierarchyData.

edited Mar 14, 2014 at 13:10

answered Mar 14, 2014 at 12:13

Wasif Hossain

3,9501 gold badge20 silver badges20 bronze badges

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

4 Comments

InTheWorldOfCodingApplications Over a year ago

Can we replace g.Key?

InTheWorldOfCodingApplications Over a year ago

I have changed this following Id = g.First().ParentId Name = g.First().ParentName

InTheWorldOfCodingApplications Over a year ago

Will this work for All the Parents in the Result Set , confusing as we are using First() ?

Wasif Hossain Over a year ago

yes, it'll work and you can use in that way avoiding g.Key. As we are grouping by the ParentId and moreover, the Hierarchy table contains both ParentId and ParentName for each row, so it is clear that each grouping will contain the list of same ParentId and ParentName. For this reason, First() should work for all the Parents inside each group. Last() will also do the same for the above reason.

Collectives™ on Stack Overflow

Converting a stored procedure output into Objects through LINQ

1 Answer 1

EDIT

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related