An efficient way to process a filtered set of DataRows
My VB 2008 app requires processing a subset of DataRows from a child table for each row in the parent table. When I first designed the app, I used all of the rows belonging to a particular parent row, so I used drParent.GetChildRows to get the rows, and worked off that. That works very efficiently.
The latest and greatest version of the app requires using filtered subgroups of the rows: the rows can be revised, and I need to process each revision group separately. So a row might indicate that it's active for all revisions, another is active for revisions 0-2, another for 3-end, another for just revision 1, another for 4-end, etc.
This seemed like an ideal situation for a Linq query, so I set up such a query against the DataTable, with WHERE clauses for the parent table linking key and the active revision. But I'm getting a massive performance hit on large databases. It seems to me, looking at the results and what I'm seeing happening with the debugger, that the query is being reprocessed each time a reference is made to it, which means that the time to process my whole file is rising exponentially as the file gets bigger. (One particular function that previously took 10 milliseconds now takes over 500 ms with a 4000-row table.)
So that doesn't seem like a workable solution for me. I need to use a different methodology, but I'm not sure what's the most efficient way. I could use a DataView, but then I lose strong typing, and I'll have to go back through all my code to rewrite every reference to a DataRow. What I'd like is some sort of strongly typed grouping of rows from a DataTable that I can create with different filtering choices with minimal overhead. Suggestions?