Return newly inserted row id from stored procedure using LINQ toSQL

Question

I have a stored procedure as following:

ALTER PROCEDURE [dbo].[Addworkshop]
    @title varchar(100),
    @time varchar(10),
    @date date,
    @mandatory int,
    @sid int
AS
BEGIN
    SET NOCOUNT ON;

    insert into Workshop(title, date, time, mandatory, sid)
    values(@title, @date, @time, @mandatory, @sid)

    declare @wid int
    set @wid = scope_identity()
    return @wid
END 

And I call it from my action method in ASP.NET MVC5 C#:

public JsonResult addWorkshop(Workshop workshop)
{
        TLCWREntities context = new TLCWREntities();
        var wid = db.Addworkshop(workshop.title, workshop.time, workshop.date, workshop.mandatory, workshop.sid);
        return Json(wid);
}

This method is called using JSON ajax:

 $.ajax({

     type: 'POST',
     url: '@Url.Action("addWorkshop")', // we are calling json method

     dataType: 'json',

     data: $.toDictionary(wsData),


      success: function (wid) {
                        console.log("success add workshop wid: "+wid);

                    },
      error: function (ex) {
                        console.log("err add workshop");

                    }
                });

The data is successfully inserted to the table and when I execute the stored procedure within SQL Server Management Studio it returns the correct newly inserted row id. However, when I call the stored procedure using JSON ajax and action method it always returns -1.

I am working on Visual Studio 2013 MVC5 C# and SQL Server Studio 2008

Answer 1

with the help of @user2946329 and a few searches on Google, I managed to get the answer. 1. I altered my stored procedure like this:

insert into Workshop(title, date, time, mandatory, sid)
values(@title, @date, @time, @mandatory, @sid)
declare @wid int
set @wid = SCOPE_IDENTITY()
select @wid AS wid

I returned the last inserted row id using select statement instead of return

2. in my Entity Framework Model file I updated model from database then from Model browser I navigated to Function Imports, right click on my function then Edit. I set the return value as Scalar

3. I right click on my model that is affected by the function and select Stored Procedure Mapping

I set the Result column binding and bind the return id with the id of my modelview

Thanks all for helping out