Identical linq for different index

Question

I am parsing excel data and need to create object based on StartDate-EndDate difference.

Is there a better way to have only one linq perform this task and return two different result sets ? Only difference in both linq is just the variable on which its applied.

private Dictionary<string, Dictionary<DateTime, List<DataPoint>>>[] ParseData(ExcelFile file, Dictionary<string, string>[] ConfigMap)
{
 // Extract the information from the excel file
 var excelData = file.GetWorksheetData(sheet: 0, rowStart: 2);
 var monthlyRows = excelData.Rows.Where(r => (DateTime.FromOADate((double)r[EndDateCol]) - DateTime.FromOADate((double)r[StartDateCol])).Days > 1);
 var dailyRows = excelData.Rows.Where(r => (DateTime.FromOADate((double)r[EndDateCol]) - DateTime.FromOADate((double)r[StartDateCol])).Days == 1);
 var monthlyData = monthlyRows.Where(r => r[KeyCol] != null && ConfigMap[0].ContainsKey((string)r[KeyCol]))
 .GroupBy(r => (string)r[KeyCol])
 .ToDictionary(g => g.Key,
 g => g.GroupBy(r => DateTime.FromOADate((double)r[DateCol]))
 .ToDictionary(c => c.Key,
 c => c.Select(r => new DataPoint(DateTime.FromOADate((double)r[StartDateCol]), new Decimal((double)r[PriceCol])))
 .ToList()));
 var dailyData = dailyRows.Where(r => r[KeyCol] != null && ConfigMap[1].ContainsKey((string)r[KeyCol]))
 .GroupBy(r => (string)r[KeyCol])
 .ToDictionary(g => g.Key,
 g => g.GroupBy(r => DateTime.FromOADate((double)r[DateCol]))
 .ToDictionary(c => c.Key,
 c => c.Select(r => new DataPoint(DateTime.FromOADate((double)r[StartDateCol]), new Decimal((double)r[PriceCol])))
 .ToList()));
 return new Dictionary<string, Dictionary<DateTime, List<DataPoint>>>[] { monthlyData, dailyData };
}

• Mathieu Guindon: I love var with a passion, but in this specific context it's not clear what type monthlyRows and dailyRows are.
• Cannon: where enumerable ExcelDataRows

Answer 1

Assuming monthlyRows and dailyRows are both IEnumerable<ExcelDataRow>, a first step could be to extract a method (assuming return type here, I only skimmed through your code):

private Dictionary<DateTime, List<DataPoint>> GetDataDictionary(IEnumerable<ExcelDataRow> data)
{
 var result = data.Where(r => r[KeyCol] != null && ConfigMap[0].ContainsKey((string)r[KeyCol]))
 .GroupBy(r => (string)r[KeyCol])
 .ToDictionary(g => g.Key,
 g => g.GroupBy(r => DateTime.FromOADate((double)r[DateCol]))
 .ToDictionary(c => c.Key,
 c => c.Select(r => new DataPoint(DateTime.FromOADate((double)r[StartDateCol]), new Decimal((double)r[PriceCol])))
 .ToList())); 
 return result;
}

Your ParseData method's body could then look like this:

 // Extract the information from the excel file
 var excelData = file.GetWorksheetData(sheet: 0, rowStart: 2);
 var monthlyRows = excelData.Rows.Where(r => (DateTime.FromOADate((double)r[EndDateCol]) - DateTime.FromOADate((double)r[StartDateCol])).Days > 1);
 var monthlyData = GetDataDictionary(monthlyRows);
 var dailyRows = excelData.Rows.Where(r => (DateTime.FromOADate((double)r[EndDateCol]) - DateTime.FromOADate((double)r[StartDateCol])).Days == 1);
 var dailyData = GetDataDictionary(dailyRows);
 return new Dictionary<string, Dictionary<DateTime, List<DataPoint>>>[] { monthlyData, dailyData };

---

That solves one issue.

Next step would be to break down the LINQ query and make the code self-explanatory as far as why you need to have a dictionary of dictionaries of lists.. and then make that simpler.

• Cannon: That dict of dict is required with some other reasons. Was wondering if I can slice the data based on Date difference and apply the linq to prepare result all in one linq only and get two different results for different conditions. Guess its too much to ask for :)
• Mathieu Guindon: @buffer_overflow make sure you check out our help center to see what CR is all about - it's possible you get answers with alternative implementations, but the answers you should be expecting are answers that identify weak spots in your coding style and in your implementation. But yes, it's possible. Take a look at what type that the Where() method is expecting - that's the type you could take as a parameter to the extracted method if you wanted to supply a condition.
• Cannon: Thanks. Just wondering. As I haven't seen linq implementation which works on two different conditions in same statement and returns two different results say a list or array of size = number of conditions.
• svick: Why have the result variable at all? Instead, you could simply return data.Where(....
• Mathieu Guindon: @svick true, ..I systematically assign those to something, just to I have a chance to break and inspect when debugging, there's no other reason - actually now that I think of it, that's not even a good reason... I should stop doing that?