c#linq
linq crosstab with dynamic columns
I have the following Lists of objects and I use linq join them using a Left Outer Join, after that I need to expand some dynamic rows into columns using linq but that is where I have an issue.
private List<ClasslistDto> GetClassList()
{
return new List<ClasslistDto>
{
new ClasslistDto{StudentID = "0101", StudentName = "Student 1", ClassID = "1", ClassName="Class 2", Sex = "M", ResStatus="Day"},
new ClasslistDto{StudentID = "0102", StudentName = "Student 2", ClassID = "1", ClassName="Class 2", Sex = "M", ResStatus="Day"},
new ClasslistDto{StudentID = "0103", StudentName = "Student 3", ClassID = "1", ClassName="Class 2", Sex = "F", ResStatus="Day"},
new ClasslistDto{StudentID = "0104", StudentName = "Student 4", ClassID = "1", ClassName="Class 2", Sex = "M", ResStatus="Day"},
new ClasslistDto{StudentID = "0105", StudentName = "Student 5", ClassID = "1", ClassName="Class 2", Sex = "F", ResStatus="Day"},
new ClasslistDto{StudentID = "0106", StudentName = "Student 6", ClassID = "1", ClassName="Class 2", Sex = "M", ResStatus="Day"},
new ClasslistDto{StudentID = "0107", StudentName = "Student 7", ClassID = "1", ClassName="Class 2", Sex = "M", ResStatus="Day"},
new ClasslistDto{StudentID = "0108", StudentName = "Student 8", ClassID = "1", ClassName="Class 2", Sex = "F", ResStatus="Day"},
new ClasslistDto{StudentID = "0109", StudentName = "Student 9", ClassID = "1", ClassName="Class 2", Sex = "F", ResStatus="Day"}
};
}
private List<ExamTypeDto> GetExams()
{
return new List<ExamTypeDto>
{
new ExamTypeDto{ExamTypeID="EX01", ExamType = "Test 1", MaxMark = 10},
new ExamTypeDto{ExamTypeID="EX02", ExamType = "Project", MaxMark = 20},
new ExamTypeDto{ExamTypeID="EX03", ExamType = "Test 2", MaxMark = 20},
new ExamTypeDto{ExamTypeID="EX04", ExamType = "MidTerm", MaxMark = 50},
new ExamTypeDto{ExamTypeID="EX05", ExamType = "Exams", MaxMark = 100}
};
}
private List<StudentMarkDto> GetStudentMarks()
{
return new List<StudentMarkDto>
{
new StudentMarkDto{StudentID = "0101", ClassID = "1", ExamTypeID="EX01", Mark=8},
new StudentMarkDto{StudentID = "0101", ClassID = "1", ExamTypeID="EX02", Mark=17},
new StudentMarkDto{StudentID = "0101", ClassID = "1", ExamTypeID="EX03", Mark=12},
new StudentMarkDto{StudentID = "0101", ClassID = "1", ExamTypeID="EX04", Mark=38},
new StudentMarkDto{StudentID = "0101", ClassID = "1", ExamTypeID="EX05", Mark=97},
new StudentMarkDto{StudentID = "0102", ClassID = "1", ExamTypeID="EX01", Mark=9},
new StudentMarkDto{StudentID = "0102", ClassID = "1", ExamTypeID="EX02", Mark=13.7},
new StudentMarkDto{StudentID = "0102", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0102", ClassID = "1", ExamTypeID="EX04", Mark=47},
new StudentMarkDto{StudentID = "0102", ClassID = "1", ExamTypeID="EX05", Mark=91},
new StudentMarkDto{StudentID = "0103", ClassID = "1", ExamTypeID="EX01", Mark=10},
new StudentMarkDto{StudentID = "0103", ClassID = "1", ExamTypeID="EX02", Mark=14},
new StudentMarkDto{StudentID = "0103", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0103", ClassID = "1", ExamTypeID="EX04", Mark=50},
new StudentMarkDto{StudentID = "0103", ClassID = "1", ExamTypeID="EX05", Mark=86},
new StudentMarkDto{StudentID = "0104", ClassID = "1", ExamTypeID="EX01", Mark=5},
new StudentMarkDto{StudentID = "0104", ClassID = "1", ExamTypeID="EX02", Mark=10},
new StudentMarkDto{StudentID = "0104", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0104", ClassID = "1", ExamTypeID="EX04", Mark=25},
new StudentMarkDto{StudentID = "0104", ClassID = "1", ExamTypeID="EX05", Mark=59},
new StudentMarkDto{StudentID = "0105", ClassID = "1", ExamTypeID="EX01", Mark=8},
new StudentMarkDto{StudentID = "0105", ClassID = "1", ExamTypeID="EX02", Mark=13},
new StudentMarkDto{StudentID = "0105", ClassID = "1", ExamTypeID="EX03", Mark=10.5},
new StudentMarkDto{StudentID = "0105", ClassID = "1", ExamTypeID="EX04", Mark=39},
new StudentMarkDto{StudentID = "0105", ClassID = "1", ExamTypeID="EX05", Mark=75},
new StudentMarkDto{StudentID = "0106", ClassID = "1", ExamTypeID="EX01", Mark=8},
new StudentMarkDto{StudentID = "0106", ClassID = "1", ExamTypeID="EX02", Mark=14},
new StudentMarkDto{StudentID = "0106", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0106", ClassID = "1", ExamTypeID="EX04", Mark=40},
new StudentMarkDto{StudentID = "0106", ClassID = "1", ExamTypeID="EX05", Mark=65},
new StudentMarkDto{StudentID = "0107", ClassID = "1", ExamTypeID="EX01", Mark=10},
new StudentMarkDto{StudentID = "0107", ClassID = "1", ExamTypeID="EX02", Mark=14},
new StudentMarkDto{StudentID = "0107", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0107", ClassID = "1", ExamTypeID="EX04", Mark=50},
new StudentMarkDto{StudentID = "0107", ClassID = "1", ExamTypeID="EX05", Mark=86},
new StudentMarkDto{StudentID = "0108", ClassID = "1", ExamTypeID="EX01", Mark=10},
new StudentMarkDto{StudentID = "0108", ClassID = "1", ExamTypeID="EX02", Mark=14},
new StudentMarkDto{StudentID = "0108", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0108", ClassID = "1", ExamTypeID="EX04", Mark=50},
new StudentMarkDto{StudentID = "0108", ClassID = "1", ExamTypeID="EX05", Mark=86},
new StudentMarkDto{StudentID = "0109", ClassID = "1", ExamTypeID="EX01", Mark=10},
new StudentMarkDto{StudentID = "0109", ClassID = "1", ExamTypeID="EX02", Mark=14},
new StudentMarkDto{StudentID = "0109", ClassID = "1", ExamTypeID="EX03", Mark=15},
new StudentMarkDto{StudentID = "0109", ClassID = "1", ExamTypeID="EX04", Mark=50},
new StudentMarkDto{StudentID = "0109", ClassID = "1", ExamTypeID="EX05", Mark=86}
};
}
I use the left outer join
var result = (from s in classlist
join m in studentMarks on s.StudentID equals m.StudentID
into st_def
from m in st_def.DefaultIfEmpty()
join e in exams on m.ExamTypeID equals e.ExamTypeID into ex_def
from e in ex_def.DefaultIfEmpty()
select new ManualEntryAllDto
{
StudentID = s.StudentID,
StudentName = s.StudentName,
ExamTypeID = m.ExamTypeID,
ExamName = e.ExamType,
MaxMark = e.MaxMark,
Marks = m.Mark
});
I get the results that appear
I however need to flatten it further so I get results like this
StudentID | StudentName | Test 1 | Project | Test 2 | MidTerm | Exams
0101 | Student 1 | 8 | 17 | 12 | 38 | 97
0102 | Student 2 | 9 | 13.7 | 15 | 47 | 91
...
...
In case GetStudentMarks() return an empty list, we still need it to return
StudentID | StudentName | Test 1 | Project | Test 2 | MidTerm | Exams
0101 | Student 1 | 0 | 0 | 0 | 0 | 0
0102 | Student 2 | 0 | 0 | 0 | 0 | 0
...
...
The Other caveat is GetExams() may return N number of rows
I found a function here on stackoverflow to flatten a list but I am not able to use it to get the desired results
public static dynamic[] ToPivotArray<T, TColumn, TRow, TData>(
this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
{
var arr = new List<object>();
var cols = new List<string>();
String rowName = ((MemberExpression)rowSelector.Body).Member.Name;
var columns = source.Select(columnSelector).Distinct();
cols = (new[] { rowName }).Concat(columns.Select(x => x.ToString())).ToList();
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))
}).ToArray();
foreach (var row in rows)
{
var items = row.Values.Cast<object>().ToList();
items.Insert(0, row.Key);
var obj = GetAnonymousObject(cols, items);
arr.Add(obj);
}
return arr.ToArray();
}
private static dynamic GetAnonymousObject(IEnumerable<string> columns, IEnumerable<object> values)
{
IDictionary<string, object> eo = new ExpandoObject() as IDictionary<string, object>;
int i;
for (i = 0; i < columns.Count(); i++)
{
eo.Add(columns.ElementAt<string>(i), values.ElementAt<object>(i));
}
return eo;
}
Any pointers will be great
Solution
Here is a variation of an extension method I have for pivoting IEnumerable<T> that returns a List<dynamic> instead of the original DataTable:
public static class IEnumerableExt {
// pivot an IEnumerable<T> to a new DataTable
// over field creates new column names
// value field is value for new columns
// By fields are row ids and grouping key
public static dynamic PivotOverWithBysToDynamic<TRow>(this IEnumerable<TRow> rows, string OverColName, string WithValueName, params string[] ByRowNames) {
var res = new List<dynamic>();
if (rows.Count() > 0) {
var rowg = rows.GroupBy(row => ByRowNames.Select(f => row.GetValue(f)).ToList(),
row => new { Over = row.GetValue(OverColName).ToString(), With = row.GetValue(WithValueName) },
Make.IESequenceEqual<object>());
foreach (var rg in rowg) {
var rgKey = rg.Key.ToList();
var newr = new ExpandoObject();
var newrMap = (IDictionary<string, object>)newr;
for (int j1 = 0; j1 < ByRowNames.Length; ++j1)
newrMap[ByRowNames[j1]] = rgKey[j1];
foreach (var r in rg)
newrMap[r.Over] = r.With;
res.Add(newr);
}
}
return res;
}
}
This code depends on a lot of other extension methods in order to make getting the values out of the original object properties somewhat efficient (by caching functions that return the value for a property to minimize reflection overhead) and to make comparing sequences easier:
public static class Make {
public static IEqualityComparer<IEnumerable<T>> IESequenceEqual<T>() => new IEnumerableSequenceEqualityComparer<T>();
private class IEnumerableSequenceEqualityComparer<T> : IEqualityComparer<IEnumerable<T>> {
public bool Equals(IEnumerable<T> x, IEnumerable<T> y) =>
Object.ReferenceEquals(x, y) || (x != null && y != null && (x.SequenceEqual(y)));
public int GetHashCode(IEnumerable<T> items) {
var hc = new HashCode();
foreach (var item in items)
hc.Add(item);
return hc.ToHashCode();
}
}
}
//***
// Enhanced Dictionary that auto-creates missing entries with seed lambda based on key
// ala auto-vivification in Perl
//***
public class KeySeedFnDictionary<TKey, TValue> : Dictionary<TKey, TValue> {
Func<TKey, TValue> seedFn;
public KeySeedFnDictionary(Func<TKey, TValue> pSeedFn) : base() {
seedFn = pSeedFn;
}
public KeySeedFnDictionary(Func<TKey, TValue> pSeedFn, IDictionary<TKey, TValue> d) : base() {
seedFn = pSeedFn;
foreach (var kvp in d)
Add(kvp.Key, kvp.Value);
}
public new TValue this[TKey key] {
get {
if (!TryGetValue(key, out var val))
base[key] = (val = seedFn(key));
return val;
}
set => base[key] = value;
}
}
public static class MemberInfoExt {
public static Func<object, object> GetValueFunc(this MemberInfo member) {
switch (member) {
case FieldInfo mfi:
return mfi.GetValue;
case PropertyInfo mpi:
return mpi.GetValue;
case MethodInfo mi:
return (object srcObject) => mi.Invoke(srcObject, null);
default:
throw new ArgumentException("MemberInfo must be of type FieldInfo, PropertyInfo or MethodInfo", nameof(member));
}
}
}
public static class TypeExt {
public static MemberInfo GetPropertyOrField(this Type t, string memberName, BindingFlags bf = BindingFlags.Public | BindingFlags.Instance) =>
t.GetMember(memberName, bf).Where(mi => mi.MemberType == MemberTypes.Field || mi.MemberType == MemberTypes.Property).Single();
}
public static class ObjectExt {
static KeySeedFnDictionary<(Type t, string m), MemberInfo> memberInfoCache = new(tm => tm.t.GetPropertyOrField(tm.m));
static KeySeedFnDictionary<(Type t, string m), Func<object, object>> getFnCache = new(tm => memberInfoCache[(tm.t, tm.m)].GetValueFunc());
public static TRes GetValue<TRes>(this object obj, string memberName) {
var getFn = getFnCache[(obj.GetType(), memberName)];
return (TRes)getFn(obj);
}
public static object GetValue<T>(this T obj, string memberName) => ((object)obj).GetValue<object>(memberName);
}
In order to handle the possibility of GetStudentMarks() returning an empty list, you must change the query to join with the marks last:
var result = (from s in classlist
from e in exams
join m in studentMarks on new { s.StudentID, e.ExamTypeID } equals new { m.StudentID, m.ExamTypeID } into mj
from m in mj.DefaultIfEmpty()
select new ManualEntryAllDto {
StudentID = s.StudentID,
StudentName = s.StudentName,
ExamTypeID = e.ExamTypeID,
ExamName = e.ExamType,
MaxMark = e.MaxMark,
Marks = m?.Mark ?? default
});
Once you have the data (essentially left join plus right join) you can pivot it:
var ans = result.PivotOverWithBysToDynamic("ExamName", "Marks", new[] { "StudentID", "StudentName" });