Improving conversion from List to List<Dictionary<string,string>> with Linq

I've a Key/Value table in my DB and I would return a List of Dictionary. The following code works fine for me but with a lot of data is not performing.

note: r.name doesn't contains unique value

List<Dictionary<string, string>> listOutput = null;

using (ExampleDB db = new ExampleDB())
{
    var result = (from r in db.FormField
               where r.Form_id == 1
               select new { r.ResponseId, r.name, r.value}).toList();

    listOutput = new List<Dictionary<string, string>>();

    foreach (var element in result)
    {
        listOutput.Add((from x in listOutput
               where x.ResponseId == element.ResponseId
               select x).ToDictionary(x => x.name, x => x.value));
    }
}

return listOutput;

Do you have suggestions on how to improve this code ?

Jon Skeet
people
quotationmark

I suspect you want something like:

List<Dictionary<string, string>> result;

using (var db = new ExampleDB())
{
    result = db.FormField
               .Where(r => r.Form_id == 1)
               .GroupBy(r => r.ResponseId, r => new { r.name, r.value })
               .AsEnumerable()
               .Select(g => g.ToDictionary(p => p.name, p => p.value))
               .ToList();
}

In other words, we're filtering so that r.Form_id == 1, then grouping by ResponseId... taking all the name/value pairs associated with each ID and creating a dictionary from those name/value pairs.

Note that you're losing the ResponseId in the list of dictionaries - you can't tell which dictionary corresponds to which response ID.

The AsEnumerable part is to make sure that the last Select is performed using LINQ to Objects, rather than trying to convert it into SQL. It's possible that it would work without the AsEnumerable, but it will depend on your provider at the very least.

people

See more on this question at Stackoverflow