How to combine 2 LINQ dictionaries into 1?

Question

I have 2 excel files that I have converted into lists. The 1st file has a complete list of all items that I need. However, the 2nd list has a small list of items that need to be changed in the 1st list. Here's how my 1st list is constructed:

IEnumerable<ExcelRow> queryListA = from d in datapullList
                                          select new ExcelRow
                                           {
                                               Company = d.GetString(0),
                                               Location = d.GetString(1),
                                               ItemPrice = d.GetString(4),
                                               SQL_Ticker = d.GetString(15)
                                           };

The 2nd list is constructed in a very similar way:

IEnumerable<ExcelRow> queryListB = from dupes in dupespullList
                                          select new ExcelRow
                                           {
                                               Company = d.GetString(0),
                                               Location = d.GetString(1),
                                               NewCompany = d.GetString(4)
                                           };

So, if there is a company from a particular location in 1st list that matches 2nd list, then the company gets changed to the newcompany name.

Then, my final list should have everything in 1st list but with the changes specified from 2nd list.

I've been struggling with this for a few days now. Let me know if you need more details.

[Update:] I'm pretty new to LINQ and C#. I've found this code on the web regarding Excel reader for Office 2003. How can I create the 1 list (stated above) from all the following classes? My ExcelRow class:

class ExcelRow
{     
    List<object> columns;

    public ExcelRow()
    {
        columns = new List<object>();
    }

    internal void AddColumn(object value)
    {
        columns.Add(value);
    }

    public object this[int index]
    {
        get { return columns[index]; }
    }

    public string GetString(int index)
    {
        if (columns[index] is DBNull)
        {
            return null;
        }
        return columns[index].ToString();
    }

    public int Count
    {
        get { return this.columns.Count; }
    }
}

My ExcelProvider class:

class ExcelProvider : IEnumerable<ExcelRow>
{
    private string sheetName;
    private string filePath;
    private string columnName1;
    private string columnName2;
    private List<ExcelRow> rows;

    public ExcelProvider()
    {
        rows = new List<ExcelRow>();
    }

    public static ExcelProvider Create(string filePath, string sheetName, string columnName1, string columnName2)
    {
        ExcelProvider provider = new ExcelProvider();
        provider.sheetName = sheetName;
        provider.filePath = filePath;
        provider.columnName1 = columnName1;
        provider.columnName2 = columnName2;
        return provider;
    }

    private void Load()
    {            
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=    ""Excel 8.0;HDR=YES;IMEX=1""";            
        connectionString = string.Format(connectionString, filePath);
        rows.Clear();
        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            try
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * FROM [{0}$] WHERE {1} IS NOT NULL AND {2} <> \"{3}\"", sheetName, columnName1, columnName2, null);
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ExcelRow newRow = new ExcelRow();
                            for (int count = 0; count < reader.FieldCount; count++)
                            {
                                newRow.AddColumn(reader[count]);
                            }
                            rows.Add(newRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            { throw ex; }
            finally
            {
                if (conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
            }
        }
    }

    public IEnumerator<ExcelRow> GetEnumerator()
    {
        Load();
        return rows.GetEnumerator();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        Load();
        return rows.GetEnumerator();
    }
}

So, using all this logic, how can I solve my problem?

Answer 1

Loop through queryListA and see if there is a matching company in queryListB. If so, then update the Company property.

Here's the code:

foreach (var companyA in queryListA)
{
  var companyBMatch = queryListB.FirstOrDefault(x => x.Company == companyA.Company && x.Location == companyA.Location);
  if (companyBMatch != null)
    companyA.Company = companyBMatch.NewCompany;
}

Answer 2

I'm sure you can write simpler code to achieve the same goal but I've gone for a way that reduces the number of times you have to iterate through the first and second lists. If performance isn't an issue a simpler method that just searches the dupespullList for each element in datapullList might be appropriate.

var excelRowCreator = new ExcelRowCreator(dupespullList);
var finalRows = excelRowCreator.CreateExcelRows(datapullList);

// ...

public class ExcelRowCreator
{
    /// <summary>
    /// First key is company name, second is location 
    /// and final value is the replacement name.
    /// </summary>
    private readonly IDictionary<string, IDictionary<string, string>> nameReplacements;

    /// <summary>
    /// I don't know what type of objects your initial 
    /// lists contain so replace T with the correct type.
    /// </summary>
    public ExcelRowCreator(IEnumerable<T> replacementRows)
    {
        nameReplacements = CreateReplacementDictionary(replacementRows);
    }

    /// <summary>
    /// Creates ExcelRows by replacing company name where appropriate.
    /// </summary>
    public IEnumerable<ExcelRow> CreateExcelRows(IEnumerable<T> inputRows)
    {
        // ToList is here so that if you iterate over the collection 
        // multiple times it doesn't create new excel rows each time
        return inputRows.Select(CreateExcelRow).ToList();
    }

    /// <summary>
    /// Creates an excel row from the input data replacing
    /// the company name if required.
    /// </summary>
    private ExcelRow CreateExcelRow(T data)
    {
        var name = data.GetString(0);
        var location = data.GetString(1);

        IDictionary<string, string> replacementDictionary;
        if (nameReplacements.TryGetValue(name, out replacementDictionary))
        {
            string replacementName;
            if (replacementDictionary.TryGetValue(location, out replacementName))
            {
                name = replacementName;
            }
        }

        return new ExcelRow
        {
            Company = name,
            Location = location,
            ItemPrice = data.GetString(4),
            SQL_Ticker = data.GetString(15)
        };
    } 

    /// <summary>
    /// A helper method to create the replacement dictionary.
    /// </summary>
    private static IDictionary<string, IDictionary<string, string>> CreateReplacementDictionary(IEnumerable<T> replacementRows)
    {
        var replacementDictionary = new Dictionary<string, IDictionary<string, string>>();
        foreach (var dupe in replacementRows)
        {
            var name = dupe.GetString(0);
            IDictionary<string, string> locationReplacements;
            if (!replacementDictionary.TryGetValue(name, out locationReplacements))
            {
                locationReplacements = new Dictionary<string, string>();
                replacementDictionary[name] = locationReplacements;
            }

            locationReplacements[dupe.GetString(1)] = dupe.GetString(4);
        }

        return replacementDictionary;
    }
}

UPDATE : Packaged as a class and written in visual studio so there shouldn't be any grammatical errors.

Answer 3

//first create a dictionary of comapny whose name has been changed
   var dict = queryListB.ToDictionary(x => x.Company, y => y.NewCompany);

    //loop on the first list and do the changes in the first list
    queryListA.ForEach( x => 
                        {
                            if(dict.Keys.Contains(x.Company))
                                x.Company = dict[x.Company];
                        });