Stored Procedure in LINQ2SQL query

Linq2Sql has a great use of stored procedures – it converts them into methods which you can easily call using standardized .NET syntax. For example if you have SP:

ALTER PROCEDURE MyProcedure(MyParam int) ...

after dragging it into Linq2Sql designer you can call it in your .NET code like this:

Dim aResults = MyDbContext.MyProcedure(2011)

but there are 2 caveats.

  1. If you plan to use stored procedure in a Linq2Sql query – do not use temp tables. It confuses ORM, it cannot determine type of output result and you will get a warning:

    The return types for the following stored procedures could not be detected

    If you need to store temporary data somewhere – use table variables instead.

  2. If you use code above as is and then attempt to use query variable multiple times (e.g. get the total Count of records first and then iterate thru variable in For Each loop or bind it to a control – you will get an error:

    The query results cannot be enumerated more than once

    This happens because underlying stored procedure can be executed only once and result returned by the Linq2Sql method is of type ISingleResult(Of T). The solution is to convert the result to in-memory list:

    Dim aResults = MyDbContext.MyProcedure(2011).ToList()

    This way the result becomes of type Generic.List(Of T) and you’re free to do whatever you need with it as many times as you need.

Leave a Reply

Your email address will not be published. Required fields are marked *