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

[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/archi...rocedures.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.In t32" />
with
<ElementType Name="StoredPro cedure2Result"> <!-- bad name ;-p -->
<Column Name="ID" Type="System.In t32" DbType="Int NOT NULL"
CanBeNull="fals e" />
<Column Name="Value" Type="System.In t32" DbType="Int NOT NULL"
CanBeNull="fals e" />
</ElementType>
and rebuild.

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

http://forums.microsoft.com/MSDN/Sho...D=123&SiteID=1

Marc

[Frans Bouma [C# MVP]]

Marc Gravell wrote:

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.In t32" /with
<ElementType Name="StoredPro cedure2Result"> <!-- bad name ;-p -->
<Column Name="ID" Type="System.In t32" DbType="Int NOT NULL"
CanBeNull="fals e" / <Column Name="Value" Type="System.In t32"
DbType="Int NOT NULL" CanBeNull="fals e" / </ElementType>
and rebuild.

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

http://forums.microsoft.com/MSDN/Sho...D=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
ObjectDataSourc e 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
ObjectDataSourc e for binding to my ASP.NET GridView :)

On Dec 4, 6:35 pm, "Marc Gravell" <marc.grav...@g mail.comwrote:

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.In t32" />
with
<ElementType Name="StoredPro cedure2Result"> <!-- bad name ;-p -->
<Column Name="ID" Type="System.In t32" DbType="Int NOT NULL"
CanBeNull="fals e" />
<Column Name="Value" Type="System.In t32" DbType="Int NOT NULL"
CanBeNull="fals e" />
</ElementType>
and rebuild.

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

http://forums.microsoft.com/MSDN/Sho...D=123&SiteID=1

Marc

[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