How to Pivot a List and assign to a GridView without the use of DataTable?

Question

I am currently preparing my Application to transition over to MVC and in doing so have replaced all SQL statements with LINQ (EF) and removing all Datasets/DataTables, replacing them with strongly typed Lists.

I am stuck on one scenario where I need to pivot a strongly typed List<T> , after I pivot (number of columns produced vary) I am attempting to re-assign the results back to the GridView, keeping in mind that I don't want to use a DataTable.

I have looked at various examples where people are attempting to use ExpandoObject but I can't get it to work and continue to get this error:

The data source for GridView with id 'GridReport' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.

The alternative would be to create some kind of class dynamically with properties getter and setter, would this be the right approach?

Given that eventually I will discard GridView too in MVC (controls not supported) I am now just thinking to maybe create an output just using HTML table? Since all I am doing is outputting the display and not using the GridView for any other purpose.

Some guidance and code example would help for the right scenario.

My List <T> looks like this (shortened for simplicity) and I pivot on ticker_id using a GroupBy. Am I able to return the property names from the Linq query too? if so how?:

public class CorporationCompare
{

    public int ticker_id { get;  set; }
    public string tickerSymbol { get; set; }
    public decimal? price { get; set; }
}

//pivot
var query = (from item in lstCompareCorp
                let key = new { ticker_id = item.ticker_id }
                group new { tickerSymbol = item.tickerSymbol, price = item.price } by key)
                .ToList();

Before Pivot:

ticker_id     tickerSymbol     price

1             GOOG             123.45
208           AAPL             543.21

After Pivot:

ticker_id     1                208
tickerSymbol  GOOG             AAPL
price         123.45           543.21

Your after pivot picture doesn't make sense, that isn't how `group` works. There is no reason to create an anonymous object for `key`. What data type do you want for `query`? — NetMage, Oct 16 '18 at 23:10
Possible duplicate of [Binding a GridView to a Dynamic or ExpandoObject object](https://stackoverflow.com/questions/5573856/binding-a-gridview-to-a-dynamic-or-expandoobject-object) — NetMage, Oct 16 '18 at 23:14
Another possibility would be to convert the `List` to a `BindingSource`. — NetMage, Oct 16 '18 at 23:29
@NetMage. "What data type do you want for query?"...That is the question i'm asking. A strongly typed data type that requires varying number of properties depending on the number of tickers requested. — bob56, Oct 17 '18 at 12:22
@NetMage The "Binding a GridView to a Dynamic or ExpandoObject object" suggests ExpandoObject cannot be bound to a GridView, gives anternative suggestions to use DataTable (which i want to avoid). It also suggest using impromptu to convert an ExpandoObject to a POCO via an interface, im guessing the interface needs to know the properties at compile-time. — bob56, Oct 17 '18 at 12:22
@NetMage BindingSource is only applicable to WinForms not WebForms which is what im using. — bob56, Oct 17 '18 at 12:23
@NetMage The property names would be something like PropertyName, Company0, Company1, Company2...CompanyX. Where X is the number of tickers requested. — bob56, Oct 18 '18 at 15:30

How to Pivot a List and assign to a GridView without the use of DataTable?

0 Answers0