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

1 Answer 1

Reset to default
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.

\$\endgroup\$
5
  • \$\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\$ Commented Mar 19, 2014 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\$ Commented Mar 19, 2014 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\$ Commented Mar 19, 2014 at 23:50
  • \$\begingroup\$ Why have the result variable at all? Instead, you could simply return data.Where(…. \$\endgroup\$ Commented Mar 20, 2014 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\$ Commented Mar 20, 2014 at 0:19

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.