How do I append chunks of xml returned by an EF function

Question

I have a stored procedure that employs FOR XML which is mapped to an ef function. Unfortunately I have found myself caught out by the fact that it only returns chunks of a certain length.

Another SO user pointed me towards this question which rather tantalisingly suggests the answer to my problem, just append the chunks of xml. Unfortunately it didn't go on to say how one might do this. Could anyone enlighten me as to how this might be done. I'm coding in vb.

EDIT

This is the generated function in my entity model:

Public Overridable Function CreateErsSalesAddSubmission (ByVal uname As String, ByVal pword As String, ByVal salesContractRef As String, ByVal auctionId As String, ByVal sntype As String, ByVal action As String) As String
            Dim connection As EntityConnection = DirectCast(DirectCast(Me, IObjectContextAdapter).ObjectContext.Connection, EntityConnection)
            Dim needClose As Boolean = False
            If (connection.State <> System.Data.ConnectionState.Open) Then
              connection.Open()
              needClose = True
            End If

            Dim result As String
            Try
              Using command As EntityCommand = New EntityCommand()
                If DirectCast(Me, IObjectContextAdapter).ObjectContext.CommandTimeout.HasValue Then
                  command.CommandTimeout = DirectCast(Me, IObjectContextAdapter).ObjectContext.CommandTimeout.Value
                End If
                command.CommandType = System.Data.CommandType.StoredProcedure
                command.CommandText = "FishTrackerProfessionalEntities.CreateErsSalesAddSubmission"
                command.Connection = connection
                Dim unameParameter As EntityParameter = New EntityParameter("uname", System.Data.DbType.String)
                If (Not uname Is Nothing)
                  unameParameter.Value = uname
                End If
                command.Parameters.Add(unameParameter)
                Dim pwordParameter As EntityParameter = New EntityParameter("pword", System.Data.DbType.String)
                If (Not pword Is Nothing)
                  pwordParameter.Value = pword
                End If
                command.Parameters.Add(pwordParameter)
                Dim salesContractRefParameter As EntityParameter = New EntityParameter("salesContractRef", System.Data.DbType.String)
                If (Not salesContractRef Is Nothing)
                  salesContractRefParameter.Value = salesContractRef
                End If
                command.Parameters.Add(salesContractRefParameter)
                Dim auctionIdParameter As EntityParameter = New EntityParameter("auctionId", System.Data.DbType.String)
                If (Not auctionId Is Nothing)
                  auctionIdParameter.Value = auctionId
                End If
                command.Parameters.Add(auctionIdParameter)
                Dim sntypeParameter As EntityParameter = New EntityParameter("sntype", System.Data.DbType.String)
                If (Not sntype Is Nothing)
                  sntypeParameter.Value = sntype
                End If
                command.Parameters.Add(sntypeParameter)
                Dim actionParameter As EntityParameter = New EntityParameter("action", System.Data.DbType.String)
                If (Not action Is Nothing)
                  actionParameter.Value = action
                End If
                command.Parameters.Add(actionParameter)

                result = CType(command.ExecuteScalar(), String)
              End Using
            Finally
              If needClose Then
                connection.Close()
              End If
            End Try
            Return result
        End Function

Rather than having it return a scalar value of string (which is being truncated) should I have it return something else , and if so what, and again how does one handle that return. Thanks

Answer 1

I'd modify the function you posted, and just change these lines:

Public Overridable Function CreateErsSalesAddSubmissionXml (ByVal uname As String, ByVal pword As String, ByVal salesContractRef As String, ByVal auctionId As String, ByVal sntype As String, ByVal action As String) As String
...
            using reader as EntityDataReader = command.ExecuteReader()
                If reader.HasRows Then 
                    Do While reader.Read()
                        result = result + reader.GetString(0)
                    Loop 
                End If
            End Using

Replace the line result = CType(command.ExecuteScalar(), String) with this.

You should rename the method (I added Xml to the name, make sure to put it in a file with a different name too so the file doesn't get ovewritten) so that you don't accidentally over-write it if you ever regenerate it using the EF wizards. This seems like it'd probably give you the best balance of properly using the same EF connection logic and overall framework, but still let you properly handle the XML being returned - which EF doesn't seem to really be set up for (and probably doesn't ordinarily need to be).