Filtering Parent table on child records

[Guest]

Good day,

I have a DataSet with 2 tables, Parent & Child, that are connected by a
DataRelation. I would like to filter the Parent table to display only those
records which have a record in the Child table that satisfies a certain
condition.

Any suggestions?

Cheers,

Helen

 

[Cor Ligthert [MVP]]

Helen,

Normally you have to get the childrows from the parent and than to loop to
those.
http://msdn2.microsoft.com/en-US/library/system.data.datarow.getchildrows.aspx

I hope this helps,

Cor

 

[W.G. Ryan [MVP]]

Like Cor mentions, GetChildRows is your best bet. You can also use a
DataView and reset the RowFilter property which is sometimes a little
cleaner syntactically but less performant. Overall I'd use Cor's approach

 

[Guest]

The trouble is that you have to first call GetChildRows() on each parent row,
which I was hoping to avoid.

In my case I think it will be faster to filter the child table, then walk
through the results to get the parent row, building a DataView on them.

I was hoping there was a more SQL-like way, to do it all in one step.

Thanks for your responses.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com

Like Cor mentions, GetChildRows is your best bet. You can also use a
DataView and reset the RowFilter property which is sometimes a little
cleaner syntactically but less performant. Overall I'd use Cor's approach

 

[Cor Ligthert [MVP]]

Helen,

There are mostly for profesionals no one step solutions, just because they
don't perform as we wish.
(Don't forget that SQL was created as a simple plain language for endusers).

But there will be Linq in future which should cover your question.

Cor

The trouble is that you have to first call GetChildRows() on each parent
row,
which I was hoping to avoid.

In my case I think it will be faster to filter the child table, then walk
through the results to get the parent row, building a DataView on them.

I was hoping there was a more SQL-like way, to do it all in one step.

Thanks for your responses.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com

 

[Guest]

Hi Cor,

I find from the docs that you can use only aggregate functions on the Child
in the RowFilter property of the parent, and these do not allow a condition.

I was going to make a suggestion that a future version of ADO.NET expand
this functionality, but find that it has already been done. Until then ...

Laters

Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com

 

[W.G. Ryan [MVP]]

Helen:

I'm not sure you are correct if I understand you correctly. YOu can
aggregate any field you want irrespective of child or parent and you can
also put conditions on it. However since you mention the documentation, we
may be talking about a different situation. DataTable.Compute can do what
you mention
http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute.aspx

 

[Helen Warn]

As far as I can see, you run into exactly the same problem. A filter
condition that references a Child column must be an aggregate function, and
the argument to the aggregate function can only be a column name, not an
expression using the column name.

cheers,

Helen

 

[Christian.Kuntz]

I ran into the same problem and because I wondered why an aggregate shouldn't be appropriate for the given probelm, I tried it out successfully.

If I didn't get the problem wrong, that should be one (!) solution:

I have a checkbox "Hide rows with no matches" on a Form with two data grids; a relation exists.

The event handler looks like this:

this.Cursor = Cursors.WaitCursor;
if (cbHideXrows.Checked)
{
string Filter = "Count(Child("
+ _ds.Relations[0].RelationName
+ ")."
+ _ds.Relations[0].ParentColumns[0].ColumnName
+ ") > 0";
dvLeft.RowFilter = Filter;
}
else
{
dvLeft.RowFilter = "";
}
///////////////////////////////////////
dvLeft is the view behind the left DataGrid.
It can be determined with the following code, assuming that the datasource of leftGrid is already set to _ds.Tables[0]
///////////////////////////////////////
CurrencyManager cmLeft = (CurrencyManager)leftGrid.BindingContext[_ds.Tables[0]];

dvLeft = (DataView)cmLeft.List;

///////////////////////////////////////
Any comments appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com