How can I use a stored procedure that uses tables with Entity Framework

Question

I can't seem to read this into Entity Framework and use LINQ because I get this error:

The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window.

I tried googling but the solutions seem way out of my head.. any ways I can get around this? desperate =[

The only way I can think of making this work is to create a new table.. but I can only use stored procedures unfortunately

ALTER PROCEDURE [dbo].[GetGame_FantasyHome]
    -- Add the parameters for the stored procedure here
    @GameDate varchar(8),
    @TricodeHome varchar(3)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #FantasyHome(
        GameKey int,
        PlayerName varchar(50),
        Tricode varchar(3),
        StatString varchar(50),
        Position varchar(20),
        FantasyScore int
    )   

    DECLARE @gameKey int
    SET @gameKey = (SELECT GameKey FROM Games WHERE GameDate=@GameDate
        AND TricodeHome=@TricodeHome)

    INSERT #FantasyHome
    SELECT TOP 1 p.GameKey, p.PlayerName, p.Tricode, p.StatString, p.Position,
        (p.Yards/25 + p.Touchdowns * 6 - p.Interceptions * 2) AS FantasyScore
    FROM GamePassers AS p 
    WHERE p.GameKey=@gameKey AND p.Tricode=@TricodeHome
    ORDER BY FantasyScore DESC, p.Yards DESC

    SELECT *
    FROM #FantasyHome
    ORDER BY FantasyScore DESC

    DROP TABLE #FantasyHome
END

Community · Accepted Answer · 2017-05-23 12:14:40Z

3

· The one way to get these types of stored procedures to work is to edit DBML by hand or write your own method signature for the procedure in a partial class To Handle multiple record set return from stored procedure by LINQ see the link here.

· The second way is to avoid using #temp Table in your stored procedure, instead of you can use Table type variable like below (@TempTable)

More info here. Be sure to read also the article about table variables in T-SQL pointed in that blog post.

EDIT: Check also answers to this SO question for reference.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Sep 30, 2013 at 20:23

Konrad Gadzina

3,4021 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1189352 Over a year ago

YOU'RE THE MAN! turned it into table variables and it worked!!! you seriously seriously saved me hours of wasting time at work trying to find a solution

Konrad Gadzina Over a year ago

Next time remember to start looking for a solution by googling the error message or just a part of it and checking the results. ;)

user1189352 Over a year ago

i actually did.. i think my googling just sucks -.-

Collectives™ on Stack Overflow

How can I use a stored procedure that uses tables with Entity Framework

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related