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

    $result2 = DoWork2
    $result2.GetType()
}

function DoWork1 {
    $result1 = Invoke-Sqlcmd -Query "select top 1 * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables
    #assign to variable then return
    return $result1
}

function DoWork2 {
    #return results without assigning to variable
    return Invoke-Sqlcmd -Query "select top 1 * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables
}

Main

Here is the unexpected output:

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

                                        

CodePudding user response：

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[]