Question about linq2sql

Question

I have some legacy code with linq2sql classes. DataContext class has following definition for stored procedure:

 [Function(Name="dbo.sp_Goods_SelectBySection")]
        public ISingleResult<sp_Goods_SelectBySectionResult> sp_Goods_SelectBySection([Parameter(Name="SectionId", DbType="Int")] System.Nullable<int> sectionId)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), sectionId);
            return ((ISingleResult<sp_Goods_SelectBySectionResult>)(result.ReturnValue));
        }

But when i am trying create same procedure in my own project, designer creating procedure with different implementation:

[Function(Name="dbo.sp_Goods_SelectBySection")]
        public int sp_Goods_SelectBySection([Parameter(Name="SectionId", DbType="Int")] System.Nullable<int> sectionId)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), sectionId);
            return ((int)(result.ReturnValue));
        }

And i cannt change type in method properties.

Here is code of stored procedure.

ALTER PROCEDURE [dbo].[sp_Goods_SelectBySection]
(
    @SectionId INT
)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT 
        Goods.Id,
        Goods.[Name],
        TypeId,
        Makerid,
        Price,
        [Description],
        Term,
        Types.Name AS TypeName,
        Makers.Name AS MakerName,
        GoodsImage.SmallImageUrl AS MainImageUrl,
        Goods.IsDeleted,
        Goods.Rang
    FROM Goods 
         INNER JOIN Types ON (Types.Id = Goods.TypeId)
         INNER JOIN Makers ON (Makers.Id = Goods.MakerId)
         LEFT JOIN GoodsImage ON ( GoodsImage.GoodId = Goods.Id AND GoodsImage.IsMain = 1 AND GoodsImage.IsDeleted = 0 )
    WHERE 
        Types.SectionId = @SectionId
        AND Goods.IsDeleted  = 0
    ORDER BY Rang ASC       
END

Why? And how i can create procedure which returning ISingleResult instead of int?

Note i have many such procedures and modify by hand each of them is not good idea i think.

---

I guess it's designer problem. Because i created new test project and it work ok. Thanks to all who helped me. Especially to Andras Zoltan.

Answer 1

Sorry, slightly misunderstood your question but I'll keep my initial comments

Remember that stored procedures return a result set, not a value; unless you count the value returned in a return statement. Therefore you need a type to wrap around the columns and rows that are returned.

You can change the return type to a table if you know the SP returns a rowset that maps to one of your table types. But you are not going to be able to just return 'an int'.

See this link: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/1667a989-08a0-402c-9358-e4637406a75f (sorry it's over on MSDN forums - not SO :$) and the linked topic from that http://msdn.microsoft.com/en-us/library/bb386975.aspx

Update

L2S uses the equivalent of SQL reflection to figure what an SP returns in order to auto-generate the result.

If the SP doesn't SELECT, or if it only has a RETURN in it, you will get a basic int return type for your mapped function. This kind of thing can happen legitimately or erroneously if the SP uses IF statements to fork and return different results accordingly; L2S cannot possibly figure out a single return type that can satisfy all possible outcomes, and so it just plumps for one of them.

As @JohnOpincar mentions in his comments, there is a question mark over whether the SP you've posted here is actually the one you've mapped - since the names are not the same? My guess is that you've dragged on the wrong SP - and that's only got a RETURN in it, or it returns multiple things and L2S has borked.

Update 2

Okay - so you've changed the names in the source. My recommendation is to delete the SP from the L2S designer (and any other manual attempt you may have for this SP), and drag it back on again from the Server Explorer, making sure it's the same server/db you drag it from. With the SQL you've posted, L2S will most definitely be able to figure out what the result set is as it's very simple.