Populating column with data from DataRelation child datatable

Question

If this has been answered before, please point me to it; I was not able to find anything alike.

My situation:

• 1 datagridview
• bound by BindingSource to table1 from DataSet (EF)
• table1 has datarelation to table2 (one-to-one)

Now, I need to display one or more columns from the child relationship, while keeping the ability to append, update and delete rows from the ParentTable (=table1).

I have tried pointing a BindingSource to the DataRelation, however, this only shows data from one table. As another option, I'm now trying to create a Calculated Column with an expression, looking up Child data using:

[table1].Columns.Add("[columnname]", GetType(System.String), _
    "Child([datarelation name]).[columnname from childtable]")

However, this fails with 'Cannot interpret token 'Child' at position 1.'. I guess this is due to the program expecting multiple results. So, my question is:\

1. Can I change this to expect just 1 result, and report this, to have the new Column reporting the child data?
2. Is there some best practice I'm not seeing?

Answer 1

The reference to the child column must be in an aggregate function because child relationships may return multiple rows. If your parent row maps to one single child row, you can also use some aggregate functions such as Min,Max,Sum,Avg to get the same result as the column value itself. For example this will work:

[table1].Columns.Add("[columnname]", GetType(System.String), "Sum(Child([datarelation name]).[columnname from childtable])")