DevExpress Pivot chart database implementation

Question

I have just installed evaluation version of devExpress component and I have followed the wizard for creating PivotGrid which I have hoped of using.

The query the wizard has created db access as follows

    Demo.Db.Database1Entities1 db = new Demo.Db.Database1Entities1();

    [ValidateInput(false)]
    public ActionResult PivotGridPartial()
    {
        var model = db.DemoSources;
        return PartialView("_PivotGridPartial", model.ToList());
    }

My intention is to use is on dataset with about 3M of records?

I have created dummy data set with only 20k records.

When running the default query, it does throw out of memory exception

Progress update

I have wrote the code in view only to get some progress

After the inefficient query I have ended up with updating the view only just for the proof of the concept. My view now looks like:

@{

var grid = Html.DevExpress().PivotGrid(settings =>
{
    settings.Name = "PivotGrid";

    settings.CallbackRouteValues = new {Controller = "Grid", Action = "PivotGridPartial"};

    settings.Fields.Add(field =>
    {
        field.Area = PivotArea.FilterArea;
        field.FieldName = "Datum";
        field.Caption = "Datum";
    });
});

}

@grid.BindToEF(typeof(LinkyDemoEntities), "Linky", (object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) =>
{
    e.KeyExpression = "ID";

    var dataContext = new LinkyDemoEntities();
    e.QueryableSource = dataContext.Linkies.AsQueryable();
}).GetHtml();

How do you defer sql execution to database instead of materialising the data?

Answer 1

so after contacting support, the component is not designed for ef without OLE DB connection which for my prototype I do not have and my client does not entertain the idea of providing / buying

I have solved it this way: use sql database that is older than 2008 and then use 'pivot' command as existing functionality that will work.

now it depends on my, sql skills to get the right query and work on this.