如何在C# Dictionary中为单个键添加多个值？解决重复键添加异常问题

Hey there! Let's break down what's going on here and fix that frustrating error.

Why This Error Happens #

C# Dictionary<TKey, TValue> enforces unique keys—you can’t add the same key more than once. Your SQL query uses a LEFT JOIN between leave_types and leave_profile, which means you’ll get multiple rows for the same description (leave type) if it has multiple associated profiles. When your loop tries to add that duplicate leave type key to the dictionary a second time, it throws the duplicate key exception.

The Fix: Use a Dictionary of Lists #

Since one leave type maps to multiple profiles, you need to adjust your dictionary to store a list of profiles for each leave type. That means swapping Dictionary<string, string> for Dictionary<string, List<string>>.

Here’s the revised code with this approach:

// Update dictionary to store lists of profiles per leave type
Dictionary<String, List<String>> myDictionary = new Dictionary<String, List<String>>();
List<String[]> leave_result_list = new List<String[]>();
StringBuilder leave_builder = new StringBuilder();
MySqlCommand leave_type_query = new MySqlCommand("SELECT leave_types.description, leave_profile.name FROM leave_types LEFT JOIN leave_profile on leave_types.type_id = leave_profile.leave_type_id", con);
MySqlDataReader leave_type_reader = leave_type_query.ExecuteReader();

while (leave_type_reader.Read())
{
    string leave_name = leave_type_reader.GetString("description");
    // Handle NULL values from LEFT JOIN (if a leave type has no profiles)
    string leave_profile = leave_type_reader.IsDBNull(leave_type_reader.GetOrdinal("name")) 
        ? string.Empty 
        : leave_type_reader.GetString("name");

    // Check if the leave type already exists in the dictionary
    if (myDictionary.ContainsKey(leave_name))
    {
        // Add the new profile to the existing list
        myDictionary[leave_name].Add(leave_profile);
    }
    else
    {
        // Create a new list, add the profile, then add the key-value pair to the dictionary
        List<string> profiles = new List<string>();
        profiles.Add(leave_profile);
        myDictionary.Add(leave_name, profiles);
    }

    // Keep populating your leave_result_list if you still need it
    string[] leave_type_and_profiles = new string[2];
    leave_type_and_profiles[0] = leave_name;
    leave_type_and_profiles[1] = leave_profile;
    leave_result_list.Add(leave_type_and_profiles);
}

leave_type_reader.Close();

Key Improvements: #

• Dictionary of Lists: Each leave type key now maps to a collection of its profiles, eliminating duplicate key conflicts.
• NULL Safety: The LEFT JOIN might return NULL for leave_profile.name if a leave type has no associated profiles. The code checks for DBNull and replaces it with an empty string to avoid runtime errors.
• Cleaner Logic: Removed unnecessary array conversion steps to make the code more readable and efficient.

内容的提问来源于stack exchange，提问作者Anga Matsuluka