Linq query, how to build nested objects from single table

Question

Hi there,

I have a single table and I need to build a bunch of nested objects based on the single table.

Data:

PointA PointB Month Time Price 1 2 11 11:00 10.99 1 2 12 11:00 9.99

Objects are

POINTS {PointA, PointB, Details} Details {Month, ExtraDetails} ExtraDetails {Time, Price}

I want to avoid having loads of loops and if statements, so should be able to use linq to do this. but its beyond my linq experience.

edit: These need grouping aswell

any help would be great.

Thanks

Answer 1

Assuming you got your classes defined for the objects you mentioned, and you have a constructor or properties so you can propery create the object in one line you could have a LINQ query returning a list of a POINTS.

If would go something lik this :

var res = 
    from item in table.AsEnumerable()
    select new Points(){PointA = item["PointA"];
                           PointB = item["PointB"]; 
                           Details = from item2 in table.AsEnumberable()
                                     where item["PointA"] = item2["PointA"] and item["PointB"] = item2["PointB"]
                                     select new Details(){
                                                          month=item2["month"],
                                                          extraDetails = from item3 in table.AsEnumerable()... 
                                                         }
                        };

At the end res will be a IEnumerable of Points

I am sorry for the code, I am not at a computer with .NET 3.5 so I cannot write a proper testable query

Answer 2

Just tried out a solution:

var nestedObjects = from row in data
    select new {row.PointA, row.PointB, Details = new {
        row.Month, ExtraDetails = new {
            row.Time, row.Price
        }
    }};

This is assuming that you have already got your data into data.

---

Group by

If you want to group the Points together, you need 'Group By':

var nestedObjects = from row in data
    group row by new { row.PointA, row.PointB } into Points
    select new {
        Points = Points.Key,
        Details = from details in Points
            select new { row.Month, ExtraDetails = new {
                row.Time, row.Price
            }}
    };

A little more complicated - of course you might want to group by month as well, in which case, you need to follow the same pattern as for the Points bit. Note, this will not create tables, because the group by doesn't quite do that, but it at least creates the structure for you.