Entity Framework no columns from simple query

Question

I have the following stored procedure:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetData]
    @taskName VARCHAR(205) = NULL
AS
BEGIN
    SELECT  *
    FROM    [dbo].[Table] WITH (NOLOCK)
    WHERE   @taskName IS NULL 
            OR @taskName = '' 
            OR Name LIKE '%' + @taskName + '%'
    ORDER BY Name
END

Now I created an File.edmx generated model, selected the GetData stored procedure, when I do a function import and I get "Get Column Information", it says

The selected stored procedure returns no columns

I am dbo_owner on the database and it is my user that is in the app.config on generation, and I am even storing the password in app.config (temporarily), when I run the procedure from Management Studio, it shows the columns..

I'm puzzled!

score 1 · Answer 1 · answered May 16 '12 at 15:14

1

You need to specify the field names in your select statement rather than just using the *

answered May 16 '12 at 15:14

GrahamJRoy

1,603
5
26
56

Added all the columns instead of *, deleted the Stored Procedure in the Model, re-generated, still same problem, says it returns no columns – jaekie May 16 '12 at 15:19

score 1 · Answer 2 · answered May 16 '12 at 15:29

1

try

ALTER PROCEDURE [dbo].[GetData]
@taskName VARCHAR(205) = NULL
AS
BEGIN
    exec ('SELECT * FROM [dbo].[Table] WITH (NOLOCK) WHERE ' + @taskName + 'IS NULL OR ' + @taskName + ' = \'\' OR Name LIKE \'%' + @taskName + '%\' ORDER BY Name')
END
GO

answered May 16 '12 at 15:29

juergen d

201,996
37
293
362

I tried this, didn't work.. but being puzzled again, I deleted the EDMX and tried again, works now, musta been a fluke – jaekie May 16 '12 at 15:36

score 0 · Answer 3 · answered May 16 '12 at 15:50

0

I would try the same process but using only

SELECT  *
FROM    [dbo].[Table] WITH (NOLOCK)

instead of the full query. Then you can alter your proc to add the where.

Sometimes EF has problems identifying the return columns due to the were clause

answered May 16 '12 at 15:50

Diego

34,802
21
91
134

Entity Framework no columns from simple query

3 Answers3