1

Working in a BIML file, I've got 2 DataTables that I am populating via an ExternalDataAccess.GetDataTable call with a SQL query. I am looping over one inside of the other and am trying to 'tie' the 2 together by filtering the inner one on a value from the outer one.

I've gotten around this by coding the following:

foreach (DataRow t in Target.Rows) {
    foreach (DataRow c in Columns.Rows) {
        if (c["Object"].ToString() == t["ReferenceObject"].ToString()) {
            //...
        }
    }
}

However, I would think there is a way to filter the inner foreach loop. I may have 1000's of records in the inner DataSet This is where my lack of experience is shining bright.

foreach (DataRow t in Target.Rows) {
   foreach (DataRow c in Columns.Rows.Where(z => z["Object"].ToString() == t["ReferenceObject"].ToString())) {
       //...
   }
}

I get an error: 'System.Data.DataRowCollection' does not contain a definition for 'Where' and no extension*... I know this isn't valid, but that's essentially what I'm trying to do. Is there a way to filter an inner foreach loop based on a value from the outer foreach loop?

jarretg
  • 51
  • 4

3 Answers3

2

To enumerate the DataRows of a table your need to apply the AsEnumerable extension to the DataTable

foreach(DataRow row in Columns.AsEnumerable()
                .Where(z => z.Field<string>("Object") == 
                            t.Field<string>("ReferenceObject"))
 ......

However I would like to explore the possibility to join the two tables using a DataRelation at the DataSet level if it is possible.

Here an MSDN example on how to navigate the parent/child records of two tables for which you have established a DataRelation

Steve
  • 213,761
  • 22
  • 232
  • 286
  • This didn't seem to work for me ('System.Data.DataTable' does not contain a definition for 'AsEnumerable'...). I should have mentioned that I'm trying to accomplish this via C# code nuggets in a BIML file. Also, I only import the System.Data namespace. – jarretg Feb 12 '15 at 13:52
  • What framework version? The AsEnumerable extension for a datatable could be found in the System.Data.DataSet.Extensions. See here http://stackoverflow.com/questions/3949302/linq-to-dataset-datatable-asenumerable-not-recognized – Steve Feb 12 '15 at 13:59
  • Sorry, I'm new to all this... How do I determine the framework version? I'm using VS2012, with the BIDS Helper add-in. I tried to add the System.Data.DataSetExtensions namespace, but get an error saying "The type or namespace name 'DataSetExtensions' does not exist in the namespace 'System.Data'. – jarretg Feb 12 '15 at 16:35
  • The assembly is named System.Data.DataSetExtensions and you could find it adding a new reference to your project and looking in the Assembly->Framework tab. No need to add a using because the Assembly has its namespace set to System.Data. The framework could be easily found looking at the properties of your project Application Page, Target Framework combo. – Steve Feb 12 '15 at 16:43
1

AsEnumerable and Field come from the System.Data.DataExtensions assembly so you'll need to add a reference to that to your BIML file, plus you'll need to import System.Data to make the namespace available in your BIML code.

E.g.

<#@ assembly name="System.Data.DataSetExtensions" #>
<#@ import namespace="System.Data" #>

Note: I've not specified the fully-qualified assembly name above, to keep the example simple.

However, if you've got >1 version of .NET framework on your machine you'll need to use the fully qualified name for the assembly (so BIML knows which version to use).

You can find the fully qualified names of the assemblies installed using gacutil.

Fire up a command prompt and navigate to navigate to the VC directory in your Visual Studio install location (e.g. C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC).

Then type in:

gacutil /l <assembly name>

This will return the fully-qualified names of each version of the installed assembly. On my machine there were .NET 3.5 and 4.0 versions, I elected to use the latest version with my BIML:

System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL
thesarahryan
  • 71
  • 3
1

Thanks @thesarahryan, I figured this out, just didn't post back on here. I had to change the "Rows" to "Select()"...

foreach (DataRow t in Target.Rows) {
   //foreach (DataRow c in Columns.Rows.Where(z => z["Object"].ToString() == t["ReferenceObject"].ToString())) {
   foreach (DataRow c in Columns.Select().Where(z => z["Object"].ToString() == t["ReferenceObject"].ToString())) {
       //...
   }
}
jarretg
  • 51
  • 4