LINQ query syntax for selectmany + join + let

I want to compare a list of named data dictionaries with actual data read from a data provider. The result should be a flat list in the form: [Table]: [Key] changed from [OldValue] to [NewValue].

I wanted to use linq's query syntax, performance is not needed. Dictionaries with the same name always have the same keys, no checking is needed.

I came up with the following query (you can use it in LINQ-Pad) but I do not have access to table2 in the first join. Error: "The name 'table2' is not available in the current context" (line 8).

Any ideas?

void Main()
{
    var db1 = new[] { new Table { Name = "TableA", Data = new Dictionary<string, string> { { "KeyA", "000" } } } };

    var changes = from table1 in db1
                let table2 = ReadTable(table1.Name)
                from row1 in table1.Data
                join row2 in table2.Data
                    on row1.Key equals row2.Key
                where !row1.Value.Equals(row2.Value)
                select new { Table = table1.Name, Key = row1.Key, From = row1.Value, To = row2.Value };

    changes.Dump(); 
}

Table ReadTable(string Name)
{
    return new Table { Name = "TableA", Data = new Dictionary<string, string> { { "KeyA", "111" } } };
}

class Table
{
    public string Name { get; set; }
    public Dictionary<string, string> Data { get; set; }
}
Jon Skeet
people
quotationmark

The point of a join is to find matching elements in two independent data sources. In other words, the elements in the right hand side of the join can't depend on the element on the "current" left hand side of the join. So you can't join while you're flattening. You just to effectively isolate the "given two tables, find the differences" part, and then flatten those results. I believe this will do what you want:

var changes = from table1 in db1
              let table2 = ReadTable(table1.Name)
              from change in
                  (from row1 in table1.Data
                   join row2 in table2.Data
                     on row1.Key equals row2.Key
                   where !row1.Value.Equals(row2.Value)
                   select new 
                          { 
                             Table = table1.Name, 
                             Key = row1.Key, 
                             From = row1.Value, 
                             To = row2.Value 
                          })
              select change;

people

See more on this question at Stackoverflow