Index.razor

@page "/"

@using PivotTableBindToData.Data
@using PivotTableBindToData.Northwind
@using Microsoft.EntityFrameworkCore
@using System.Drawing;
@inject IDbContextFactory<NorthwindContext> NorthwindContextFactory
@implements IDisposable
@using DevExpress.Blazor.PivotTable

<div>
    <DxPivotTable Data="Data">
    <Fields>
        <DxPivotTableField Field="CategoryName" Area="@PivotTableArea.Row" SummaryType="PivotTableSummaryType.CountDistinct"/> 
        <DxPivotTableField Field="ProductName"  Area="@PivotTableArea.Row" SummaryType="PivotTableSummaryType.CountDistinct"/>
        <DxPivotTableField Field="OrderDate" Area="@PivotTableArea.Column" GroupInterval="@PivotTableGroupInterval.DateYear" Caption="Year" />
        <DxPivotTableField Field="OrderDate" Area="@PivotTableArea.Column" GroupInterval="@PivotTableGroupInterval.DateQuarter" Caption="Quarter" >
            <ValueTemplate>
                <span>@($"Q{context.Text}")</span>
            </ValueTemplate>
            </DxPivotTableField>
        <DxPivotTableField Field="UnitPrice" Area="@PivotTableArea.Data" SummaryType="@PivotTableSummaryType.Sum" />
        <DxPivotTableField Field="ShipCountry"  Area="@PivotTableArea.Filter" SummaryType="PivotTableSummaryType.CountDistinct"/>
        <DxPivotTableField Field="ShipCity" Area="@PivotTableArea.Filter" SummaryType="PivotTableSummaryType.CountDistinct" />
    </Fields>
</DxPivotTable>

</div>

@code {
    NorthwindContext Northwind { get; set; }
    IEnumerable<PivotRow> Data { get; set; }

    protected override async Task OnInitializedAsync() {
        Northwind = NorthwindContextFactory.CreateDbContext();

        var products = await Northwind.Products.ToListAsync();
        var categories = await Northwind.Categories.ToListAsync();
        var orders = await Northwind.Orders.ToListAsync();
        var orderDetails = await Northwind.OrderDetails.ToListAsync();

        Data = (from c in categories
                      join p in products on c.CategoryId equals p.CategoryId
                      join od in orderDetails on p.ProductId equals od.ProductId
                      select new PivotRow{
                          CategoryName = c.CategoryName,
                          ProductName = p.ProductName,
                          UnitPrice = p.UnitPrice,
                          OrderDate = od.Order.OrderDate,
                          ShipCountry = od.Order.ShipCountry,
                          ShipCity = od.Order.ShipCity
                      })
              .ToList();
    }

   public void Dispose() {
        Northwind?.Dispose();
    }
}