Getting a Sotored Procedure to return multiple Rows into a Linq Table Object

francoisdotnet

Hi, this one is got the better of me ... I have set the SP's output in the dbml file to the table's data class, but when i try: Dim c As New dc_phyDataContext(StoredConnectionString) Dim d As List(Of phy_data) = c.phy_data_formonth(YearVal, MonthVal).ToList I get: System.InvalidCastException: Specified cast is not valid. at System.Data.SqlClient.SqlBuffer.get_Boolean() at System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i) at Read_phy_dailydata(ObjectMaterializer`1 ) at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at ..(reference to the above code).. End goal is to have the table object contain the data for databinding purposes in order for me to later call SubmitChanges. Any ideas on what might be wrong? Or maybe another way? rgds, f

Howard Richards

Your question seems similar to the previous post - unable to get a resultset from a stored procedure. I've tested creating a simple SP in Northwind and I get it returning the results of the SELECT. I even added a second select to return multiple resultsets and it worked after I modified the dbml file. Can you post the section of your dbml file for the procedure phy_data_formonth ?

'Howard

francoisdotnet

I know it does seem similar, I read through the previous post in detail but it does not seem like the same problem. (Or maybe it is? :confused:)

<FunctionAttribute(Name:="dbo.phy\_data\_onmonth")> \_
Public Function phy\_data\_onmonth(<Parameter(DbType:="Int")> ByVal yearvalue As System.Nullable(Of Integer), <Parameter(DbType:="Int")> ByVal monthvalue As System.Nullable(Of Integer)) As ISingleResult(Of phy\_data)
    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), yearvalue, monthvalue)
    Return CType(result.ReturnValue, ISingleResult(Of phy\_data))
End Function

(Not sure if you wanted the code, XML or SQL?) My SP doesn't return multiple "datasets", only multiple rows of the phy_data table (of the year and month in the parameters), it is a pretty normal SELECT SP. So instead of wanting to do a Linq query I rather want the SP to return the data to my phy_data Linq to SQL object. rgds, f

francoisdotnet

It seems like it is an issue with datatypes not being the same between the result of the SP and the table data class, there seem to be a mismatch...

Howard Richards

I did want the XML, but from the code you posted it looks like the LINQ SP function is correctly formed. The problem here is that you are assuming that a SP returns the same as a LINQ table entityset. It does not, so .ToList() won't work. Stored Procedures return a System.Data.Linq.ISingleResult object. To convert this into the rows you expected, use this code:

'Get the ISingleResult from function
Dim resultset = c.db.phy_data_onmonth(YearVal, MonthVal)
'Get the collection of values
Dim d As List(Of phy_data) = resultset.GetResults(of phy_data)

'Howard