CODEWITHSUNDEEP
sql-server-2008entity-frameworkstored-proceduresedmx
jaekie
jaekie

Reputation: 2303

Entity Framework no columns from simple query

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!

Upvotes: 0

Views: 258

Answers (3)

Diego
Diego

Reputation: 36146

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

Upvotes: 0

juergen d
juergen d

Reputation: 204756

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

Upvotes: 1

GrahamJRoy
GrahamJRoy

Reputation: 1643

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

Upvotes: 1

Related Questions