PowerShell 5.1 Why are these 2 functions returning different types

Using a similar example from the previous Q&A, to reproduce the same behavior:

function Invoke-SqlExample {
    $dtt = [System.Data.DataTable]::new()
    [void] $dtt.Columns.Add('Name')
    $row = $dtt.NewRow()
    $row.Name  = "Hello"
    $dtt.Rows.Add($row)
    , $dtt
}

function Main {
    $result1 = DoWork1
    $result1.GetType()

    $result2 = DoWork2
    $result2.GetType()
}

function DoWork1 {
    $result1 = Invoke-SqlExample
    [pscustomobject]@{
        Function = $MyInvocation.MyCommand.Name
        Type     = $result1.GetType().Name
    } | Out-Host
    return $result1

    # Immediate fixes:
    #     return , $result1
    #     Write-Output $result1 -NoEnumerate
    #     $PSCmdlet.WriteObject($result1, $false) !! Only if Advanced Function
}

function DoWork2 {
     return Invoke-SqlExample
}

Main

The output you would get from this is:

Function Type
-------- ----
DoWork1  DataTable


IsPublic IsSerial Name            BaseType
-------- -------- ----            --------
True     False    DataRow         System.Object
True     True     DataTable       System.ComponentModel.MarshalByValueComponent

We can see that the unrolling of the DataTable is only done when previously assigned to a variable, even though the variable ($result1 in DoWork1 is still of the type DataTable).

This could be explained as, DoWork2 happens in a single pipeline as opposed to DoWork1 which happens in two pipelines, first the output from Invoke-SqlExample is collected in a variable, and then emitted as output (this is where the unrolling is triggered). This is based on assumptions, and may not be entirely correct.

As iRon suggested in his helpful comment from the prior answer, an immediate fix to have DoWork1 return the DataTable instance untouched (unrolled), we can use the comma operator , which will wrap the DataTable instance in an array which is then lost during enumeration (output from the function) since it is an array of one element. The other alternative would be using Write-Output -NeEnumerate. As last alternative we can also use $PSCmdlet.WriteObject(..., $false) only if the function is an advanced one.

Adding a similar example that demonstrates the same behavior, this one was provided by mclayton in his helpful comment:

function test {
    , [Collections.Generic.List[string]]@(
        'hello'
        'world'
    )
}

(& { test }).GetType()          # => List`1
(& { $a = test; $a }).GetType() # => Object[]

To complement Santiago Squarzon's helpful answer and mclayton's helpful comments:

Note:

• The use of return is incidental to the behavior, given that return ... is merely syntactic sugar for ...; return, i.e. outputting ..., followed by exiting the scope; at the end of a script or function, return is never required, as the function / script is exited anyway. Any statement in a script or function can produce output, due to PowerShell's implicit output behavior - see this answer.

The behavioral difference comes down this:

• Invoke-Sqlcmd -Query ... is a command, so whatever it outputs is output as-is to the success output stream of the pipeline in which the enclosing function runs.

  • A command in PowerShell is a unit of functionality you invoke by name, which comprises cmdlets, scripts, functions, external programs, and script blocks.

  • Commands invariably involve a pipeline whose success output stream they directly write to - whether or not multiple commands are connected with |.

• By contrast, $result1 is an expression, and if an expression evaluates to a value of a .NET type that PowerShell considers an enumerable,^[1] it is enumerated when it is sent to the success output stream (of a pipeline), which in the case of a a collection means sending its elements one by one; note that the original collection type is therefore lost in the process.

  • As for the rationale for performing such enumeration: It is in line with the fundamental nature of PowerShell pipelines as streams of individual objects of indeterminate length - see this answer.

  • An expression in PowerShell is a construct that involves any combination of variable references, literals, most operators, and .NET method calls. In an of itself, an expression does not involve a pipeline, and collections and enumerables are treated as themselves. (An expression may have nested pipelines, however, if commands participate in it.)

  • A pipeline - and therefore enumeration - does get involved when an expression is used in the following contexts:

    • When you enclose the expression in $(...), the subexpression operator or @(...), the array-subexpression operator; e.g. $(1+2) or @(1+2)

      • Note: (...), the grouping operator does not do that when applied to an expression: in the context of a larger expression, it merely clarifies precedence. However, when applied to a command instead (only one supported, e.g., (Get-ChildItem).Count), it runs the command to completion in a nested pipeline, collects its output object(s) and either returns them as-is, if there's only one, otherwise as an [object[]] array.

    • When you pipe the expression to a command, using |, the pipeline operator; e.g.
      1..3 | ForEach-Object { 1 + $_ }. Note that expressions are only allowed as the first segment of pipeline.

    • When you output the expression - whether implicitly or explicitly (with the rarely necessary Write-Output), whether in combination with return or not - from inside a command, such as in the case of outputting $result1 from your DoWork1 function.

The upshot:

• If you store a command's output in a variable ($result1 = Invoke-Sqlcmd -Query ...), and later output that variable's value to the success output stream (return $result1 or just $result1 or Write-Output $result1), you potentially introduce an additional layer of enumeration.

  • To prevent enumeration, you can use the techniques mentioned in Santiago's answer (transitory single-element array wrapper trick constructed with unary , - , $return1 - or Write-Output's -NoEnumerate switch - Write-Output -NoEnumerate $return1)

• However, this only matters for commands that output collections as a whole, as a single object, instead of streaming collections, i.e enumerating them and outputting their elements, one by one, which is the typical case.

  • Commands that output collections as single objects are rare, not least because the general expectation of PowerShell commands is that they indeed stream their output.

  • Invoke-SqlCmd -OutputAs DataTables is such a command, only because PowerShell - perhaps surprisingly - considers System.Data.DataTable instances enumerable - it conceives of it as a collection of rows, which it enumerates in the pipeline by default.^[1]

^{[1] See the bottom section of this answer for an overview of what types PowerShell considers enumerable. In short: PowerShell has several exceptions where it considers types that declare themselves to be enumerable not enumerable, and one opposite exception: System.Data.DataTable does not declare itself enumerable, yet PowerShell enumerates it, namely as the collection of its rows stored in the .Rows property; Santiago provided the relevant source-code link.}