LINQ in Powershell

Still waiting on a Powershell script to finish?  I find my self frequently waiting on a Powershell script to finish filtering a large set of csv data I imported with the Import-CSV command.  After doing some digging and talking with a .NET developer, it was recommended that I look into LINQ.  The end result of this research was taking a script that was going to take more than a day to process, down to a few minutes.  I have put a simplified version below.  I hope this benefits you as much as it did me.

Setting up the Data Set

[void][Reflection.Assembly]::LoadWithPartialName("System.Data.DataSetExtensions");

$myDataTable = New-Object System.Data.DataTable;
[void]$myDataTable.Columns.Add( "Column1" );
[void]$myDataTable.Columns.Add( "Column2" );
[void]$myDataTable.Columns.Add( "Column3" );

[void]$myDataTable.Rows.Add( "value1","value2","value3" );
[void]$myDataTable.Rows.Add( "valueA","valueB","valueC" );
[void]$myDataTable.Rows.Add( "valueD","value2","valueE" );
$myDataList = [System.Data.DataTableExtensions]::AsEnumerable($myDataTable);
$myDataList;

step1_table

The above section of code will have basically setup a table of your data to work with.  Now for the fun part, setting up the query.

Setting up a Where Query

$query = [System.Func[System.Data.DataRow,Boolean]]{param($myRow) $myRow.'Column2' -eq 'value2'};
$queryOutput = [System.Linq.Enumerable]::Where($myDataList,$query);
$myOutputArray = [System.Linq.Enumerable]::ToArray($queryOutput);
$myOutputArray;

step2_table_where

Setting up a Groupby Query

$query = [System.Func[System.Data.DataRow,String]]{param($myRow) $myRow.'Column2'};
$queryOutput = [System.Linq.Enumerable]::Groupby($myDataList,$query);
$myOutputArray = [System.Linq.Enumerable]::ToArray($queryOutput);
$myOutputArray;

step3_table_groupby

This is a short how to on LINQ in Powershell, I will add another LINQ section soon.  Hopefully this helps you perform your data parsing much faster.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s