Recreating a table from individually indexed items in Linq

I have a table in an SQL DB that stores individual data items from a table; it might help to think of it as a matrix but with labels instead of integer indexes.

The structure of the table looks like this:

DataItem
{
      int TableID,
      int DataItemID,
      string YLabel,
      string RowLabel,
      int Data    
}

Basically I'd like to pass in an argument to my query that specifies the TableID of the table I would like to build. This step would select EVERY data item with TableID = PARAM. Simple enough to do in Linq:

var first = (from di in db.DataItem
             where TableID == PARAM
             select new {di.Row, db.YLabel, di.Data});

I would then like to be able to take these tuples in first and recreate the table using an asp.net control that is databound to this query.

I'll try and create a concrete example. Suppose I have this table; it contains the percent of fruit that are of a given color (its a stupid example):

          Orange | Yellow | Green
  Banana    12       80       8
  Orange    91       9        -
  Apple     0        33      33

This would get stored in DataItem like this:

{TableID = 1, DataItemID = 1, "Orange", "Banana", 12}
{TableID = 1, DataItemID = 2, "Orange", "Orange", 91}
{TableID = 1, DataItemID = 3, "Orange", "Apple", 0}

{TableID = 1, DataItemID = 4, "Yellow", "Banana", 80}
{TableID = 1, DataItemID = 5, "Yellow", "Orange", 9}
{TableID = 1, DataItemID = 6, "Yellow", "Apple", 33}

{TableID = 1, DataItemID = 7, "Green", "Banana", 8}
{TableID = 1, DataItemID = 8, "Green", "Apple", 3}

(Notice there is no Green/Orange entry in the DataItem table, this facilitates the need for DefaultIfEmpty() outer join stuff.)

I then want to take these DataItem tuples and recreate the original table in HTML using an asp.net control of somesort (probably gridview/datagrid or repeater). Once I have the linq query, I know what I'm doing with the databinding stuff, I just can't get to that point. I'm using C# 4.0 and I have absolutely no idea where to start.

I assume this involves using the DefaultIfEmpty() and group by stuff in linq, but I just can't get it to work. I can join everything up together, but I'm having issues with the potential blank and getting the titles to be what they should be.

ansaurus

tags:

views:

answers:

Recreating a table from individually indexed items in Linq

related questions