Is it possible to Pivot data using LINQ?
Something like this?
List<CustData> myList = GetCustData();
var query = myList
.GroupBy(c => c.CustId)
.Select(g => new {
CustId = g.Key,
Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty),
Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty),
March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty)
});
GroupBy
in Linq does not work the same as SQL. In SQL, you get the key and aggregates (row/column shape). In Linq, you get the key and any elements as children of the key (hierarchical shape). To pivot, you must project the hierarchy back into a row/column form of your choosing.
Pivot Table Using Linq
Try this:
//static headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
.Select(g=>new{
VisitDate = g.Key.VisitDate,
PersonelId = g.Key.PersonelId,
A = g.Where(d=>d.VisitTypeId=="A").Count(),
B = g.Where(d=>d.VisitTypeId=="B").Count(),
D = g.Where(d=>d.VisitTypeId=="D").Count(),
S = g.Where(d=>d.VisitTypeId=="S").Count()
});
//dynamic headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
.Select(g=>new{
VisitDate = g.Key.VisitDate,
PersonelId = g.Key.PersonelId,
subject = g.GroupBy(f => f.VisitTypeId)
.Select(m => new { Sub = m.Key, Score = m.Count()})
});
Pivot data using LINQ
I'm not saying it is a great way to pivot - but it is a pivot...
// sample data
var data = new[] {
new { Foo = 1, Bar = "Don Smith"},
new { Foo = 1, Bar = "Mike Jones"},
new { Foo = 1, Bar = "James Ray"},
new { Foo = 2, Bar = "Tom Rizzo"},
new { Foo = 2, Bar = "Alex Homes"},
new { Foo = 3, Bar = "Andy Bates"},
};
// group into columns, and select the rows per column
var grps = from d in data
group d by d.Foo
into grp
select new {
Foo = grp.Key,
Bars = grp.Select(d2 => d2.Bar).ToArray()
};
// find the total number of (data) rows
int rows = grps.Max(grp => grp.Bars.Length);
// output columns
foreach (var grp in grps) {
Console.Write(grp.Foo + "\t");
}
Console.WriteLine();
// output data
for (int i = 0; i < rows; i++) {
foreach (var grp in grps) {
Console.Write((i < grp.Bars.Length ? grp.Bars[i] : null) + "\t");
}
Console.WriteLine();
}
Trying to pivot data using linq
If I understand the question, you want a mapping of Task to State for each Robot. You could group by Robot and select a dictionary for each group:
Logs.GroupBy(t => t.Robot).Select(g => new {
Robot = g.Key,
TaskStates = g.ToDictionary(t => t.Task, t => t.State)
})
This assumes that task names are unique for each robot (ToDictionary
would throw an exception otherwise).
You could also add another level of grouping for dates:
Logs.GroupBy(t => t.LogDate).Select(g => new {
Date = g.Key,
Details = g.GroupBy(t => t.Robot).Select(g => new {
Robot = g.Key,
TaskStates = g.ToDictionary(t => t.Task, t => t.State)
}).ToList()
})
Note that the Details
property is essentially equivalent to my first example, the only difference being that it queries the outer grouping instead of the whole sequence. The result is a sequence of {Date, Details} where each "detail" is a list of {Robot, TaskStates}.
I haven't tested this, so let me know if there are any bugs I missed.
How to pivot a list in C#
If you know how many props you have beforehand, you could do something like this
var query = myList
.GroupBy(c => c.TimeStamp)
.Select(g => new {
TimeStamp = g.Key,
Prop1 = g.Where(x => x.Name == "Prop1").Sum(x => x.Value),
Prop2 = g.Where(x => x.Name == "Prop2").Sum(x => x.Value),
Prop3 = g.Where(x => x.Name == "Prop3").Sum(x => x.Value),
Value = g.Sum(c => c.Value)
});
If you this list of props is dynamic, you are going to have to call pivot at the server and query this manually. or use a sublist and groupby Name
C# Linq Pivot data with variable columns
Was able to solve this using a dynamic list and the ExpandoObject class.
Here's the code, hope it helps someone else:
List<dynamic> dynList = new List<dynamic>();
for (int i = 0; i < d.Count; i++)
{
dynamic dynObj = new ExpandoObject();
((IDictionary<string, object>)dynObj).Add("FileTypeCode", result[i].FileTypeCode.ToString());
((IDictionary<string, object>)dynObj).Add("Fullname", result[i].Fullname.ToString());
((IDictionary<string, object>)dynObj).Add("Entitlement", result[i].Entitlement.ToString());
var res = d[i].ColumnName.ToList();
for (int j = 0; j < res.Count; j++)
{
((IDictionary<string, object>)dynObj).Add(res[j].Col.ToString(), res[j].Value.ToString());
}
dynList.Add(dynObj);
}
var dList = (from da in dynList select da).ToList();
return dList;
How to pivot data in LINQ without hard coding columns
I've simplified the solution for you by modifying the details from the link.
First of all create an extension method ToPivotTable
in a static class:
public static class PivotClass
{
public static DataTable ToPivotTable<T, TColumn, TRow, TData>(this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
{
DataTable table = new DataTable();
var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
table.Columns.Add(new DataColumn(rowName));
var columns = source.Select(columnSelector).Distinct();
foreach (var column in columns)
table.Columns.Add(new DataColumn(column.ToString()));
var rows = source.GroupBy(rowSelector.Compile())
.Select(rowGroup => new
{
Key = rowGroup.Key,
Values = columns.GroupJoin(
rowGroup,
c => c,
r => columnSelector(r),
(c, columnGroup) => dataSelector(columnGroup))
});
foreach (var row in rows)
{
var dataRow = table.NewRow();
var items = row.Values.Cast<object>().ToList();
items.Insert(0, row.Key);
dataRow.ItemArray = items.ToArray();
table.Rows.Add(dataRow);
}
return table;
}
}
Then, use this extension method on the list to get your pivot DataTable
as mentioned below:
List<CodeItemsRule> _CodeItemRules = new List<CodeItemsRule>()
{
new CodeItemsRule()
{
CodeID="00009",
ItemID="D1",
RuleID=2
},new CodeItemsRule()
{
CodeID="00009",
ItemID="D2",
RuleID=2
},new CodeItemsRule()
{
CodeID="00009",
ItemID="D3",
RuleID=1
},new CodeItemsRule()
{
CodeID="00008",
ItemID="D1",
RuleID=3
},new CodeItemsRule()
{
CodeID="00007",
ItemID="D3",
RuleID=1
},new CodeItemsRule()
{
CodeID="00007",
ItemID="D4",
RuleID=1
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D3",
RuleID=2
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D1",
RuleID=1
},new CodeItemsRule()
{
CodeID="00010",
ItemID="D2",
RuleID=1
}
};
var pivotTable = _CodeItemRules.ToPivotTable(
item => item.ItemID,
item => item.CodeID,
items => items.Any() ? items.Sum(x => x.RuleID) : 0);
Dynamically Pivot in LINQ: Pivoted Columns Determined at Runtime
You can build linq expression trees dynamically. This topic is covered (including example) in the following MSDN article: http://msdn.microsoft.com/en-us/library/bb882637.aspx
My suggestion would be to write an example Linq query for your task and rebuild it programmatically with expression trees. Once it works you adapt it and inject your dynamic parts.
Related Topics
Is Int[] a Reference Type or a Value Type
Typeloadexception Says 'No Implementation', But It Is Implemented
C# Regex Split - Commas Outside Quotes
What Does the '=>' Syntax in C# Mean
How to Access a Control Inside a Xaml Datatemplate
C# Get Thumbnail from File via Windows API
ASP.NET Calling Webmethod with Jquery Ajax "401 (Unauthorized)"
Accessing C# Anonymous Type Objects
Why Do C# Multidimensional Arrays Not Implement Ienumerable<T>
What Is the Purpose of Self Tracking Entities
Calling a Method Every X Minutes
System.Text.JSON.JSONelement Toobject Workaround
Calling C# Code from C++, But Executeindefaultappdomain() Is Too Limited