I searched for how to do pivot data with LINQ and found THIS
I am trying apply this for my OData API service.
Table Data has composite key as { Id, Name, DataType }.
Table Analyses has key as Id.
So, I try this code, and everything works fine:
var pv = _context.Data.GroupBy(g => new { g.Id, g.Name, g.DataType })
.Select(s => new
{
Id = s.Key.Id,
Name = s.Key.Name,
DataType = s.Key.DataType,
C1 = s.Where(w => w.Component == "C1").Max(s => s.Value),
C2 = s.Where(w => w.Component == "C2").Max(s => s.Value),
C3 = s.Where(w => w.Component == "C3").Max(s => s.Value),
.
.
.
.
.
C98 = s.Where(w => w.Component == "C98").Max(s => s.Value),
C99 = s.Where(w => w.Component == "C99").Max(s => s.Value),
C100 = s.Where(w => w.Component == "C100").Max(s => s.Value)
});
var rs = pv.Where(w => w.Id == 1);
But when I try to join with another table, it run like forever:
var pv = _context.Data.GroupBy(g => new { g.Id, g.Name, g.DataType })
.Select(s => new
{
Id = s.Key.Id,
Name = s.Key.Name,
DataType = s.Key.DataType,
C1 = s.Where(w => w.Component == "C1").Max(s => s.Value),
C2 = s.Where(w => w.Component == "C2").Max(s => s.Value),
C3 = s.Where(w => w.Component == "C3").Max(s => s.Value),
.
.
.
.
.
C98 = s.Where(w => w.Component == "C98").Max(s => s.Value),
C99 = s.Where(w => w.Component == "C99").Max(s => s.Value),
C100 = s.Where(w => w.Component == "C100").Max(s => s.Value)
}).Join(_context.Analyses, p => p.Id, a => a.Id, (p, a) => new
{
Id = p.Id,
Name = p.Name,
DataType = p.DataType,
AnalysName = a.AnalysName,
C1 = p.C1,
.
.
C100 = p.C100,
});
var rs = pv.Where(w => w.Id== 1);
The less aggregate column (C1, C2...) the faster the query. So, is there anyway I can make this? Any advice for the query or another way to approach this will be appreciated, thanks.
