Need help for a complex linq query

Question

Ok so I've got a DataTable here's the schema

        DataTable dt = new DataTable();
        dt.Columns.Add("word", typeof(string));
        dt.Columns.Add("pronunciation", typeof(string));

The table is filled already and I'm trying to make a linq query so that i can output to the console or anywhere something like :

   Pronunciation : akses9~R => (list of words)

I want to output the pronunciations the most common and all the words that use it.

Answer 1

var words = from row in table
            where row.pronunciation == "akses9~R"
            select row.word;
foreach (string word in words)
{
    Console.WriteLine(word);
}

Answer 2

Something like this should give you what you want:

var results = dt.GroupBy(dr => dr.pronunciation);

foreach(var result in results)
{
    Console.Write("Pronunciation : {0} =>", result.Key);
    foreach(var word in result)
    {
        Console.Write("{0} ", word);
    }
    Console.WriteLine();
}

The GroupBy gives you an IGrouping whose Key property will contain the pronunciation and collection itself will contain all the words.

Answer 3

Sounds like you want a group by:

var q =
    from row in dt.Rows.Cast<DataRow>()
    let val = new { Word = (string)row["word"], Pronunciation = (string)row["pronunciation"] }
    group val by val.Pronunciation into g
    select g;

foreach (var group in q)
{
    Console.WriteLine(
    "Pronunciation : {0} => ({1})", 
    group.Key, 
    String.Join(", ", group.Select(x => x.Word).ToArray()));
}