none
Can't generate EF model from stored procedure RRS feed

  • Question

  • I'm building a simple web application on top of an existing database and cannot get Entity Framework working properly with it. Visual Studio 2013, EF6.

    I've created a stored procedure in the database which takes two parameters (with default values) and returns a single resultset. My goal is to have user input for the two parameters (a date and a short string), then submit the request to the server (WebAPI), return the resultset formatted as JSON, and then render the table client-side via Knockout (eventually, client-side caching, sorting & filtering will come into play).

    I've created the project, and attempted to add the data model from the database, selecting only my stored procedure, following this tutorial: www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx (I can't link it as I'm not verified).

    However, when it finishes, I don't see everything in the Model Explorer that the tutorial says I should; no Complex Type is generated. If I attempt to edit the corresponding Function Import and click Column Information, I get "The selected stored procedure or function returns no columns." This leaves me at the midpoint of the page I linked above and not knowing where to go next. If I execute the stored procedure in SSMS or via Invoke-SqlCmd in PowerShell, I get the expected results, so I know the procedure works and I have rights to execute it.

    Have I done something wrong in Visual Studio or created my stored procedure incorrectly? I really don't know where to go from here.

    • Moved by Tina-Shi Monday, December 8, 2014 2:17 AM the issue is related to the ef
    Friday, December 5, 2014 10:09 PM

All replies

  • Hi AlevyInRoc,

    Thank you for posting in MSDN forum.

    Since it is related to the ADO.NET Entity Framework, we will move this case to the ADO.NET Entity Framework and LINQ to Entities forum, you will get dedicated support from the ADO.NET Entity Framework experts.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 8, 2014 2:16 AM
  • I've created a stored procedure in the database which takes two parameters (with default values) and returns a single resultset. My goal is to have user input for the two parameters (a date and a short string), then submit the request to the server (WebAPI), return the resultset formatted as JSON, and then render the table client-side via Knockout (eventually, client-side caching, sorting & filtering will come into play).

    You can do this without EF. So what's the point of even using EF, if all you are going to be doing is running sprocs?

    Monday, December 8, 2014 4:49 AM
  • Hello ALevyInROC,

    This sounds very strangely, according that tutorial, I created a demo, however, it did as expected as the tutorial describes. Could you please share what database you are using and the stored produce?

    If you are using SQL Server, please have a try with below demo(works for me):

    CREATE TABLE [dbo].[Order] (
    
        [OrderID]    INT           NOT NULL,
    
        [OrderName]  NVARCHAR (50) NOT NULL,
    
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[OrderDetail] (
    
        [OrderDetailID]   INT             NOT NULL,
    
        [OrderDetailName] NVARCHAR (50)   NULL,
    
        [OrderID]         INT             NULL,
    
        [Count]           NUMERIC (18, 6) NULL,
    
        PRIMARY KEY CLUSTERED ([OrderDetailID] ASC),
    
        CONSTRAINT [FK_O_OD] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[Order] ([OrderID]) ON DELETE CASCADE
    
    );
    

    The stored produce:

    CREATE PROCEDURE [dbo].[GetOrderDetailByOrderId]
    
            -- Add the parameters for the stored procedure here
    
            @OrderID int = null
    
        AS
    
        BEGIN
    
            -- SET NOCOUNT ON added to prevent extra result sets from
    
            -- interfering with SELECT statements.
    
            SET NOCOUNT ON;
    
    
            -- Insert statements for procedure here
    
        select o.OrderID, od.OrderDetailID, od.OrderDetailName
    
           from [Order] o 
    
           inner join OrderDetail od on o.OrderID = od.OrderID
    
           where o.OrderID = @OrderID
    
        END
    

    Please have a try and if it is possible, you could try to run your current application and database with a new computer to see if this is caused by your current environment.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 8, 2014 7:18 AM
    Moderator
  • Fred Bao,

    Your demo worked for me - everything was created as expected based on the tutorial. I'm using SQL Server 2008 R2. Here's my (anonymized somewhat) procedure:

    CREATE PROCEDURE UnitAvailability (
    	@SiteID VARCHAR(6) = '123'
    	,@StartDate DATE = '2014-12-15'
    	)
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	CREATE TABLE #CurrentLeases (
    		GSiteID VARCHAR(6) NOT NULL
    		,BuildingID VARCHAR(6) NOT NULL
    		,UnitID VARCHAR(6) NOT NULL
    		,LeaseCount INT NOT NULL
    		,Terminated DATE NULL
    		,CONSTRAINT PK_CurrentLeases PRIMARY KEY CLUSTERED (
    			GSiteID
    			,BuildingID
    			,UnitID
    			,LeaseCount
    			)
    		);
    
    	CREATE INDEX IX_CurLeases ON #CurrentLeases (
    		GSiteID
    		,BuildingID
    		,UnitID
    		) include (LeaseCount);
    
    	INSERT INTO #CurrentLeases
    	SELECT R.GSiteID
    		,R.BuildingID
    		,R.UnitID
    		,R.LeaseCount
    		,R.Terminated
    	FROM Leases R
    	WHERE R.GSiteID = @SiteID
    		AND Leases = (
    			SELECT max(r2.LeaseCount)
    			FROM Leases R2
    			WHERE R2.GSiteID = R.GSiteID
    				AND R2.BuildingID = R.BuildingID
    				AND r2.UnitID = r.UnitID
    			)
    	ORDER BY r.GSiteID
    		,r.BuildingID
    		,r.UnitID
    		,r.LeaseCount;
    
    	SELECT rtrim(ltrim(u.GSiteID)) AS PropId
    		,rtrim(ltrim(u.BuildingID)) AS Building
    		,rtrim(ltrim(u.UnitID)) AS Unit
    		,rtrim(ltrim(u.ADDRESS)) AS StreetAddress
    		,rtrim(ltrim(b.city)) AS City
    		,rtrim(ltrim(b.STATE)) AS STATE
    		,rtrim(ltrim(b.ZIP)) AS ZipCode
    		,rtrim(ltrim(u.Beds)) AS Beds
    		,rtrim(ltrim(u.Bath)) AS Bath
    		,rtrim(ltrim(u.TypeId)) AS UnitType
    		,r.StartRent + sum(c.AddlRent) + sum(pa.AddlRent) AS TOTRENT
    	FROM Properties p
    	JOIN Units u ON p.GSiteID = u.GSiteID
    	JOIN RMBLDG b ON u.GSiteID = b.GSiteID
    		AND b.BuildingID = u.BuildingID
    	JOIN #CurrentLeases CL ON cl.GSiteID = u.GSiteID
    		AND cl.BuildingID = u.BuildingID
    		AND cl.UnitID = u.UnitID
    	JOIN Rates R ON r.GSiteID = p.GSiteID
    		AND r.TypeId = u.TypeId
    	JOIN Addons1 ua ON ua.GSiteID = p.GSiteID
    		AND ua.BuildingID = u.BuildingID
    		AND ua.UnitID = u.UnitID
    	JOIN Addons2 pa ON p.GSiteID = pa.GSiteID
    		AND pa.AddonCode = ua.AddonCode
    	JOIN Addons3 c ON c.TypeId = u.TypeId
    		AND c.GSiteID = p.GSiteID
    		AND ua.AddonCode = c.AddonCode
    	WHERE p.GSiteID = @SiteID
    		AND (
    			(
    				u.USTATUS = 'A'
    				AND (
    					u.IsReady = 'Y'
    					OR u.MadeReady <= @StartDate
    					)
    				)
    			OR (
    				u.ustatus = 'B'
    				AND dateadd(day, p.turndays, cl.Terminated) <= @StartDate
    				)
    			)
    		AND r.ACTIVE = 'y'
    		AND @StartDate >= r.startdt
    		AND @StartDate <= r.enddt
    		AND r.TERM = 12
    		AND r.leasetype = 'N'
    	GROUP BY u.GSiteID
    		,u.BuildingID
    		,u.UnitID
    		,u.ADDRESS
    		,b.city
    		,b.STATE
    		,b.ZIP
    		,u.Beds
    		,u.Bath
    		,u.TypeId
    		,r.StartRent;
    
    	DROP TABLE #CurrentLeases;
    END
    GO
    
    

    Unfortunately I don't have another workstation set up right now with VS 2013 to test against, and can only add my SProc to another copy of the database on the same SQL box (and I get the same results in both) due to security and the size of the "parent" application.


    Wednesday, December 10, 2014 4:30 PM
  • Hello ALevyInROC,

    Your provided stored produce should be a multiple result sets, which after you update the stored produce to the model, it would only generate the first type, in your case, it should be a integer for the insert operation.

    There are two ways to make it work, with raw sql statement query and modifying the edmx file, for details, please check this article:

    http://msdn.microsoft.com/en-us/data/jj691402.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred BaoModerator Tuesday, December 16, 2014 9:52 AM
    • Unmarked as answer by ALevyInROC Tuesday, December 16, 2014 12:07 PM
    Thursday, December 11, 2014 9:52 AM
    Moderator
  • My stored procedure does not result in an insert operation. It returns a single dataset from the last SELECT statement. The INSERT is for a temp table which is used only inside the SProc.
    Tuesday, December 16, 2014 12:09 PM
  • My stored procedure does not result in an insert operation. It returns a single dataset from the last SELECT statement. The INSERT is for a temp table which is used only inside the SProc.

    The sproc is doing more than simple CRUD operations for a single table, which would be used by EF.

    No strored procedure returns a dataset. A strored procedure returns a resultset. Maybe, you need to use the EF backdoor to run the sproc and take the resultset and load it into a dataset.

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    You can either do inline T-SQL or call a strored procedure.

    Tuesday, December 16, 2014 2:33 PM