asked on

Pivot Sql Table column to a list c#

Hi,

I have a question about how to pivot a sql table column into a list of objects.

Assume I have a product table with columns:

product id location price
1 x $10
1 y $12
1 z $14

I created a (location, price) class and a product class as:

public class priceLocation
{
public string location;
public decimal price;
}

and
public class product
{
public int productid;
public list<priceLocation> priceLocations;
}
public list<object> locationprice

How can I load the above sql data to these classes?

Thanks

JayFromPep

I think that LINQ would be the way to go.

Check this article....

http://www.c-sharpcorner.com/UploadFile/scottlysle/L2SinCS06022008035847AM/L2SinCS.aspx

sofux

ASKER

I am not sure how it helps.

Let me clarify little bit:

product id location price
1 x $10
1 y $12
1 z $14
2 x $15
2 y $17
2 z $13

need to convert this table to create a product list. If I had to do it manually, it would look like this:

list<product> Products = new list<product>
{ new Product{productid = 1, new List<priceLocation>{new priceLocation{location = x, price =10},
new priceLocation{location = x, price =12},
new priceLocation{location = x, price =14}}},
new Product{productid = 2, new List<priceLocation>{new priceLocation{location = x, price =15},
new priceLocation{location = x, price =17},
new priceLocation{location = x, price =13}}}};

I am familiar with linq but not sure if this can be accomplished with linq.
Thanks.

ASKER CERTIFIED SOLUTION

Algol36

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sofux

ASKER

Thanks.

Need to small adjustments but overall it works.

This part of the code didn't work:

grp.Select<DataRow, priceLocation>(
l => new priceLocation() {
location = (string)l["location"],
price = (decimal)l["price"]
}

so I replaced this with:

grp.Select( l => new priceLocation() {
location = l.location,
price = l.price
}

sofux

ASKER

Provided very good direction but further improvements required on my side.