Linq: #Temp tables in SPROCS do not have return values in generatedmethods

[R. K. Wijayaratne]

Hi everyone,

I have a SPROC which selects records into a MSSQL #temp table and then
selects the records from that. I drag this SPROC onto the right-hand
pane of the DBML diagram, but the method that gets generated for the
SPROC does not have a return type. Any ideas why this is?

I am referring to Scott Guthrie's blog article here:

http://weblogs.asp.net/scottgu/arch...-retrieving-data-using-stored-procedures.aspx

 

[Marc Gravell]

I have checked, and yes - this appears to be the case even for simple
cases.
I would guess that this is because the metadata for temp-tables is *in
the general case* too weak (since the table could be external to the
SP, or could have multiple create points [either of which would also
cause constant SP recompiles]).

If your data-volumes aren't immense, and you don't use an index on the
temp-table, then perhaps consider using a table-variable instead [
DECLARE @varname TABLE (...) ]; the metadata is much stronger and it
works as expected.

If table-variables aren't an option, then you can also edit the dbml
directly: right-click; Open With...; XML Editor; locate your SP and
replace e.g.
<Return Type="System.Int32" />
with
<ElementType Name="StoredProcedure2Result"><!-- bad name ;-p -->
<Column Name="ID" Type="System.Int32" DbType="Int NOT NULL"
CanBeNull="false" />
<Column Name="Value" Type="System.Int32" DbType="Int NOT NULL"
CanBeNull="false" />
</ElementType>
and rebuild.

Note that there is also a LINQ-specific forum that you may find
useful:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1

Marc

 

[Frans Bouma [C# MVP]]

I have checked, and yes - this appears to be the case even for simple
cases. I would guess that this is because the metadata for
temp-tables is *in the general case* too weak (since the table could
be external to the SP, or could have multiple create points [either
of which would also cause constant SP recompiles]).

If your data-volumes aren't immense, and you don't use an index on
the temp-table, then perhaps consider using a table-variable instead
[ DECLARE @varname TABLE (...) ]; the metadata is much stronger and
it works as expected.

If table-variables aren't an option, then you can also edit the dbml
directly: right-click; Open With...; XML Editor; locate your SP and
replace e.g. <Return Type="System.Int32" /> with
<ElementType Name="StoredProcedure2Result"><!-- bad name ;-p -->
<Column Name="ID" Type="System.Int32" DbType="Int NOT NULL"
CanBeNull="false" /> <Column Name="Value" Type="System.Int32"
DbType="Int NOT NULL" CanBeNull="false" /> </ElementType>
and rebuild.

Note that there is also a LINQ-specific forum that you may find
useful:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1

Marc

It's due to an issue in sqlserver. See my reply in the adonet
newsgroup on this server.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[R. K. Wijayaratne]

Hi Marc

Thanks for your insightful reply. Great tip on editing the dbml file,
wasn't aware that you could directly edit it. I did use a @temp table
variable and it worked. However after binding to the grid directly I
had trouble sorting and paging. I also could not bind the method to an
ObjectDataSource and from memory the error given was 'This method(?)
does not support server side paging(?)' or something along those
lines. Anyway ended up reverting to good old a DataSet and an
ObjectDataSource for binding to my ASP.NET GridView

 

[Marc Gravell]

Fair enough; the paging etc probably would have been solvable, but
obviously it isn't going to be able to inject sort/page code directly
into an SP. But if you're happy...

Marc