c# - How can I return a Dictionary<string,Dictionary<int,decimal>> from this method? -
i have linq statement returns iqueryable of class below.
class:
public class suppliersummaryreport { public int year { get; set; } public string suppliername { get; set; } public decimal turnovervalues { get; set; } }
eg: {2012,supplier1,90},{2011,supplier2,95}
however, need convert data dictionary of dictionaries. have extension method me , friend have built, stumped @ final section.
extension method:
public static dictionary<tfirstkey, dictionary<tsecondkey, tvalue>> pivot<tsource, tfirstkey, tsecondkey, tvalue>(this ienumerable<tsource> source, func<tsource, tfirstkey> firstkeyselector, func<tsource, tsecondkey> secondkeyselector, func<tsource, tvalue> value) { var retval = new dictionary<tfirstkey, dictionary<tsecondkey, tvalue>>(); var l = source.tolookup(firstkeyselector); foreach (var item in l) { var dict = new dictionary<tsecondkey, tvalue>(); retval.add(item.key, dict); var subdict = item.tolookup(secondkeyselector); foreach (var subitem in subdict) { dict.add(subitem.key, subitem.value /*insert value here*/); } } return retval; }
we call method so:
public dictionary<string,dictionary<int,decimal>> getsuppliersummaryreportdata(list<int> last5years) { _ctx.database.commandtimeout = 5 * 60; //values - gets data required supplier summary report in hierachical order. e.g - {2012,supplier1,3},{2011,supplier1,4} var values = (from in _ctx.invoices i.turnover == true group new { i.accountname, i.invoicedate.year } summ select new apdata.audit.models.reportmodels.suppliersummaryreport { year = summ.key.year, suppliername = summ.key.accountname, turnovervalues = summ.sum(r => r.vatamount_home ?? 0) }); //accounts - account names var accounts = (from in _ctx.invoices i.turnover == true group new { i.accountname } summ select new { summ.key.accountname }); /*crossjoin - select suppliernames accounts , years last5years , assign each suppliername last 5 years. assign each turnover value null each year. in preparation cross join not suppliers have last 5 year in data */ var crossjoin = accounts.selectmany(a => last5years, (a, y) => new apdata.audit.models.reportmodels.suppliersummaryreport { year = y, suppliername = a.accountname, turnovervalues = 0 }); /*join crossjoin , values together, wherever join empty, assign cross join values. if not assign turnover value a*/ var result = (from cj in crossjoin join v in values on new { cj.year, cj.suppliername } equals new { v.year, v.suppliername } ljoin in ljoin.defaultifempty(new apdata.audit.models.reportmodels.suppliersummaryreport { year = cj.year, suppliername = cj.suppliername, turnovervalues = cj.turnovervalues }) select new apdata.audit.models.reportmodels.suppliersummaryreport { year = cj.year, suppliername = cj.suppliername, turnovervalues = a.turnovervalues }).orderby(r => r.suppliername).thenby(r => r.year); return result.pivot(r => r.suppliername, c => c.year, y => y.turnovervalues);
we need insert decimal value of turnovervalues
third item in dictionary.
expected outcome:
{supplier1, {2012,60}}, {supplier2, {2014,90}}
if needs anymore information, please let me know.
tl;dr, if have ienumerable<suppliersummaryreport>
, you're sure each suppliername, year, turnover
combination unique or can made unique todictionary
makes easy:
var data = new list<suppliersummaryreport> { ... }; // avoiding var verify correct result type dictionary<string, dictionary<int, decimal>> result = data .groupby(ssr => ssr.suppliername) .todictionary(g1 => g1.key, g1 => ( g1.groupby(g2 => g2.year) .todictionary(g3 => g3.key, g3 => g3.first().turnovervalues)) );
maybe g3.first()
should become .single()
or .sum()
or something.
Comments
Post a Comment