5
\$\begingroup\$

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 };
}
\$\endgroup\$
  • \$\begingroup\$ I love var with a passion, but in this specific context it's not clear what type monthlyRows and dailyRows are. \$\endgroup\$ – Mathieu Guindon Mar 19 '14 at 23:12
  • \$\begingroup\$ where enumerable ExcelDataRows \$\endgroup\$ – Cannon Mar 19 '14 at 23:19
6
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ 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 :) \$\endgroup\$ – Cannon Mar 19 '14 at 23:34
  • \$\begingroup\$ @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. \$\endgroup\$ – Mathieu Guindon Mar 19 '14 at 23:46
  • \$\begingroup\$ 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. \$\endgroup\$ – Cannon Mar 19 '14 at 23:50
  • \$\begingroup\$ Why have the result variable at all? Instead, you could simply return data.Where(…. \$\endgroup\$ – svick Mar 20 '14 at 0:18
  • \$\begingroup\$ @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? \$\endgroup\$ – Mathieu Guindon Mar 20 '14 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.