/// <summary>
/// Execute the Upivot operation in EF.
/// </summary>
static void UnpivotInEF()
{
// set the function list of attributes.
Dictionary <String, Func <Person, DateTime?> > attrFuncList = new Dictionary <string, Func <Person, DateTime?> >();
attrFuncList["HireDate"] = p => p.HireDate;
attrFuncList["EnrollmentDate"] = p => p.EnrollmentDate;
// Store the Unpivot result.
IEnumerable <UnpivotRow <String, String, DateTime> > result = null;
using (MySchoolEntities school = new MySchoolEntities())
{
// Get the data from databasy by EF.
var persons = (from person in school.People
select person).ToList();
// Get the query result of every attribute.
foreach (String key in attrFuncList.Keys)
{
String k = key;
IEnumerable <UnpivotRow <String, String, DateTime> > query =
(from person in persons
where attrFuncList[k](person) != null
select new UnpivotRow <String, String, DateTime>
{
ObjectId = person.FirstName + " " + person.LastName,
Attribute = k,
// Get the value of a certain attribute.
Value = (DateTime)attrFuncList[k](person)
});
// Concat the results.
result = result == null ? query : result.Concat(query);
}
}
Console.WriteLine("It's the result of Unpivot In EF:");
Console.WriteLine("{0,-15}{1,-15}{2,-15}", "ObjectId", "AttributeName", "Value");
foreach (var row in result.ToList())
{
Console.WriteLine("{0,-15}{1,-15}{2,-15}",
row.ObjectId, row.Attribute, row.Value.ToShortDateString());
}
}
/// <summary>
/// Execute the Pivot operation in EF.
/// </summary>
static void PivotInEF()
{
List <PivotRow <Person, String, Decimal> > studentGrade = null;
List <String> courses = null;
using (MySchoolEntities school = new MySchoolEntities())
{
// Get the data from the database.
studentGrade = (from sg in school.StudentGrades
group sg by sg.StudentID into sgGroup
select new PivotRow <Person, String, Decimal>
{
ObjectId = sgGroup.Select(g => g.Person).FirstOrDefault(),
Attributes = sgGroup.Select(g => g.Course.Title),
Values = sgGroup.Select(g => g.Grade)
}
).ToList();
// Get the list of attributes.
courses = school.Courses.Select(c => c.Title).ToList();
}
// Get the Pivot table.
using (DataTable pivotTable = PivotRow <Person, String, Decimal> .GetPivotTable(courses, studentGrade))
{
Console.WriteLine("It's the result of Pivot in EF:");
foreach (DataColumn col in pivotTable.Columns)
{
Console.Write("{0,-15}", col.ColumnName);
}
Console.WriteLine();
foreach (DataRow row in pivotTable.Rows)
{
Person p = (Person)row[0];
Console.Write("{0,-15}", p.FirstName + " " + p.LastName);
for (int i = 1; i < pivotTable.Columns.Count; i++)
{
Console.Write("{0,-15}", row[i].GetType().Equals(typeof(DBNull)) ? "NULL" : row[i]);
}
Console.WriteLine();
}
}
}