ToDictionary error with LINQ

Question

I am having trouble converting these expressions into lambdas without error (Cannot convert type error):

var excel = new ExcelQueryFactory(@"E:\MAHipotCepaStationProgram.xlsx");

//get list of program names
List<string> testNames = new List<string>();
testNames.AddRange(excel.Worksheet().ToList()
            .Where(s => s["Program #"].Value.ToString() == "Program Title")
            .Select(s => s[1].Value.ToString()));


//get list of program numbers
List<int> testNumbers = new List<int>();
testNumbers.AddRange(excel.Worksheet().ToList()
            .Where(s => s["Program #"].Value.ToString() == "Program #")
            .Select(s => Convert.ToInt32(s[1].Value)));

//combine them
Dictionary<int, string> programs = new Dictionary<int, string>();
for (int x = 0; x < testNames.Count-1; x++)
{
    if (!programs.ContainsKey(Convert.ToInt32(testNumbers[x])))
    {
        programs.Add(Convert.ToInt32(testNumbers[x]), testNames[x]);
    }
    else
    {
        testNumbers[x].Dump("Duplicate Found");
    }
}

We should be looking at something like the following but no matter what I try it won't compile:

Dictionary<string, string> programsDict = excel.Worksheet().ToDictionary(
                                        e => e["Program #"].Value.ToString() == "Program Title")
                                            .Select(s => s[1].Value.ToString()),
                                        f => f.Where(d => d.Value.ToString() == "Program #").ToString());

Any help is much appreciated. Also, here is pic of the excel sheet we're trying to extract the data from, Program Titles and Program Numbers.

Take a look at the `Zip` method in LINQ. If you add an image of what the Excel sheet looks like there may be a better way where you can get both values in one step instead, using an anonymous type or a tuple to capture the two values before the `ToDictionary` call. — Ian Mercer, Jun 16 '18 at 14:25
@Ian Mercer - ok, the spreadsheet has been added. Really curious how the Zip would work here with the ToDictionary call... — Kelly Kleinknecht, Jun 16 '18 at 14:42
"Cannot apply indexing to the Cell" is the error for compiling — Kelly Kleinknecht, Jun 16 '18 at 14:47
@ian mercer var zip = testNames.Zip(testNumbers, (code, state) => code + ": " + state); gives me an IEnumberable string, how would I get IEnumberable string string? — Kelly Kleinknecht, Jun 17 '18 at 01:57

jdweng · Answer 1 · 2018-06-17T01:59:51.727

0

Here is code to create the pivot table :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            List<string> columnHeaders = excel.Worksheet.Select(x => (string)x[0]).ToList();
            columnHeaders = columnHeaders.Distinct().ToList();

            DataTable pivotTable = new DataTable();
            foreach (string columnHeader in columnHeaders)
            {
                if(columnHeader != "")
                   pivotTable.Columns.Add(columnHeader, typeof(string));
            }

            DataRow newRow = null;
            foreach (var row in excel.Worksheet)
            {
                if ((string)row[0] == "Program #")
                {
                    newRow = pivotTable.Rows.Add();
                }
                if((row[0] != null) && ((string)row[0] != ""))
                   newRow[(string)row[0]] = (row[1] == null) ? "" : row[1];  
            }
        }
    }
}

edited Jun 17 '18 at 01:59

answered Jun 16 '18 at 18:08

jdweng

33,250
2
15
20

NotSupportedException: LinqToExcel only provides support for the Distinct() method when it's mapped to a class and a single property is selected. [e.g. (from row in excel.Worksheet() select row.FirstName).Distinct()] @jdweng – Kelly Kleinknecht Jun 16 '18 at 19:06
Then do it in two steps!!! See updated code. – jdweng Jun 16 '18 at 23:42
Object reference not set to an instance of an object. is now the error when I get to this line: newRow[(string)row[0]] = row[1]; @jdweng Thank you for the help! – Kelly Kleinknecht Jun 17 '18 at 01:41
I updated code to handle the null. – jdweng Jun 17 '18 at 02:00

jdweng · Answer 2 · 2018-06-16T15:24:03.733

-1

Try following :

            //first get programs and numbers
            var rows = excel.Worksheet.Where(x => (x[0].ToString() == "Program #") || (x[0].ToString() == "Program Title")).Select(x => new { ColA = x[0].ToString(), ColB = x[1].ToString() }).ToList();

            // now even entries are the numbers and the odd are the titles
            var programTitle = rows.Select((x, i) => new { itemNumber = i / 2, program = x }).GroupBy(x => x.itemNumber).Select(x => new { number = int.Parse(x.FirstOrDefault().program.ColB), title = x.LastOrDefault().program.ColB }).ToList();

            //now create your dictionary
            Dictionary<int, string> programs = programTitle.GroupBy(x => x.number, y => y.title)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());

edited Jun 16 '18 at 15:24

answered Jun 16 '18 at 15:08

jdweng

33,250
2
15
20

Can only use column indexes in WHERE clause when using WorksheetNoHeader is the error I have with that code after adding () to Worksheet – Kelly Kleinknecht Jun 16 '18 at 16:12
You are a wizard! I'm unclear how I check for the duplicate keys now... – Kelly Kleinknecht Jun 16 '18 at 16:22
You can't have duplicate keys, otherwise, you can't create the dictionary. If you have duplicate keys then you would need to use Dictionary> and then change y.FirstOrDefault() to y.List(); – jdweng Jun 16 '18 at 17:52
With the data you have it would be best to create a pivot table. So Program number is 1st column of results, title 2nd column, and steps are the other columns. – jdweng Jun 16 '18 at 17:55
Your dictionary has the key/value pair swapped around from what the OP wanted. – Enigmativity Jun 17 '18 at 02:13
The op liked the code in his response. The code the OP posted that he said was getting exception had the dictionary the ways I posted the code. His comments about what he wanted had the dictionary reversed. So which one is correct? I'm happy that he said I was a wizard. – jdweng Jun 17 '18 at 04:36

ToDictionary error with LINQ

2 Answers2