Well LINQ stands for for .net Language INtegrated Query.
LINQ to SQL provides a runtime infrastructure for managing relational data as objects (object relational mapping) without losing the ability to query.
Since PowerShell can’t handle LINQ natively we need to call in some help of LINQPad first to get acquainted with the data and the syntax upfront.
Well the great LinqPad can handle a lot of data sources (even oData see here ) as well as conventional databases using conventional SQL syntax.
The beauty about this tool is that it can convert between different languages and as well between LINQ and conventional SQL by a push on the button !
Back to PowerShell :
To add the LINQ feature to PowerShell we need the help of the LINQ snapin
To install it, run the RegisterPowerQuery.cmd
Make sure you open a CMD prompt as Administrator go to the folder where you extracted the zip.
And run the command.
If that does not work on your machine. Use the RegisterPowerQuery.ps1 that is enclosed and run it as Adminstrator.
Alternatively, use the Windows SDK gacutil.exe command to register all the dlls.
Important : When using Powershell or IDE make sure you are starting the x64 bit version !
Next run the PS Script
CLS add-pssnapin AlexPilotti.PowerQuery # Set your connection string and target path (your temp folder in this sample) $connStr = "Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind" $assemblyParentPath = $Env:Temp # Generate the assembly New-DataModelAssembly $connStr "MyNorthwindModel" $assemblyParentPath "Northwind" # Load your LINQ to SQL DataContext $ctx = New-Object "Northwind.MainDataContext" $connStr # Get the content of the Products table using the generated data model: $ctx.Products # Close the database connection and free al related resources $ctx.Dispose()
Sorry but I did not have a SQL database at hand at the moment to run a live example.