Equivalent to Pivot Table in LINQ

[paguirre82]

0
I've been struggling for a few days to display something like Pivoting a dynamic table on SQL, using Linq. I don't actually want to create a pivoted table, I just want to display the results as if it was a pivoted table.
I have two entities:

Category:

 public int Id { get; set; }
public string Name { get; set; }
public string Icon { get; set; }
public ICollection<StaticEvent> StaticEvents { get; set; }

StaticEvent:

public int Id { get; set; }
public int CategoryId {get; set; }
public Category Category { get; set; }
public DateTimeOffset Time {get; set; }

[MaxLength(500)]
public string Comment {get; set;}

I'm trying to generate a table showing the COUNT of STATIC EVENTS per CATEGORY PER YEARMONTH. So the rows would be the YEARMONTH, the columns would be the Categories and the values would be the COUNT.



I got to the point where I can count the STATIC EVENTS per YEARMONTH:

var query = _context.Categories
            .SelectMany(c => c.StaticEvents )
            .GroupBy(c =>
new {
                   Year = c.Time.Year,
                   Month = c.Time.Month
               })
.Select( x=> new {YearMonth = x.Key, Count = x.Count()})
            .ToList();

foreach (var x in query)
{Console.WriteLine($"Month = {x.YearMonth.Year},{x.YearMonth.Month},  , Count = " + x.Count);}

but I'm lost about what to do from here.

I can easily do it in SQL with this query :

SELECT @cols = @cols + QUOTENAME(categoryId) + ',' FROM (select distinct categoryId from StaticEvents) as tmp
SELECT @Cols = SUBSTRING(@cols, 0, len(@cols)) --trim "," at end
SET @query =
'SELECT * FROM
(
    select Month(Time) as Month, Id, categoryID from StaticEvents
) src
pivot
(
    Count(Id) for categoryId in (' + @cols + ')
) piv'
EXECUTE (@query)

Any help will be enormously appreciated

 

[Herman]

There's no good way to do a true pivot in Linq to SQL or Entity Framework. Your best bet is to run your SQL query using the .SqlQuery method. You will need to create a type for the result of your query, and you can then call it like this:

var result = _context.Database.SqlQuery<MyReturnType>(myQueryString);

 

[paguirre82]

There's no good way to do a true pivot in Linq to SQL or Entity Framework. Your best bet is to run your SQL query using the .SqlQuery method. You will need to create a type for the result of your query, and you can then call it like this:

var result = _context.Database.SqlQuery<MyReturnType>(myQueryString);

Thanks for your help. It looks like .SqlQuery doesn't exist anymore. I tried what you suggested with both .FromSQLInterpolated and .FromSqlRaw and I'm getting this error:

'DbSet<Category>' does not contain a definition for 'FromSqlInterpolated' and the best extension method overload 'RelationalQueryableExtensions.FromSqlInterpolated<Report>(DbSet<Report>, FormattableString)' requires a receiver of type 'DbSet<Report>'

 

[Herman]

If you are in Linq to SQL, the correct method is the following, directly from the context:

public System.Collections.Generic.IEnumerable<TResult> ExecuteQuery<TResult> (string query, params object[] parameters);

 

[Skydiver]

LINQ is very good with doing transform and mapping functions. Normally for any type of mapping functions that needs to be done, the mapping is hard coded into the code. With a pivot table, the mapping into the columns cannot be hard coded. It has to be dynamically determined based on the available data found in the prior layer's query results. This is not to say that it's not doable. It is. It'll just require multiple passes of Zip() at the tail end of the operation to build up the columns dynamically.

 

[Skydiver]

I think I found a way to do this with only 5 passes through the data instead of multiple passes using Zip(). Unfortunately, it uses the old fashioned DataTable to hold the results instead of a nice C# object. Be warned, the code below is untested. It's just me doodling on the keyboard.

var categoriesByYearMonth = _context.Categories
                .SelectMany(c => c.StaticEvents)
    .GroupBy(s => new { YearMonth = $"{s.Time.Year} {s.Time.Month}" })
    .Select(g => new {
        YearMonth = g.Key.YearMonth,
        Categories = g.GroupBy(cg => cg.Category)
            .Select(cg => new { Category = cg.Key, Count = cg.Count()}) })
    .ToList();

var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("YearMonth", typeof(string)));
dataTable.Columns.AddRange(categoriesByYearMonth.SelectMany(c => c.Categories)
                           .Distinct()
                           .Select(c => new DataColumn(c.Category.Name, typeof(int)))
                           .ToArray());
foreach(var g in categoriesByYearMonth)
{
    var row = dataTable.NewRow();
    row["YearMonth"] = g.YearMonth;
    foreach(var c in g.Categories)
        row[c.Category.Name] = c.Count;
    dataTable.Rows.Add(row);
}