Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar while executing a stored procedure with Entity Framework

Question

I am getting error

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

while executing a stored procedure with Entity Framework.

This is my stored procedure:

CREATE PROCEDURE [dbo].[usp_RolesList]   
    (@WhereCond VARCHAR(50))
AS
BEGIN
    SET NoCount ON

    DECLARE @SQLQuery AS VARCHAR(MAX)
    SET @SQLQuery = 'Select * From dbo.AspNetRoles ' + @WhereCond

    EXECUTE sp_Executesql @SQLQuery
END

and this is my c# code that I am using for executing the results

var idParam = new SqlParameter
                    {
                        ParameterName = "WhereCond",
                        Value = "where Id > 0"
                    };
var courseList = ctx.Database.SqlQuery<UserRoles>("exec usp_RolesList @WhereCond ", idParam).ToList<UserRoles>();

now getting that errors - $exception {"An error occurred while reading from the store provider's data reader. See the inner exception for details."} System.Data.Entity.Core.EntityCommandExecutionException - InnerException {"Conversion failed when converting the nvarchar value 'ca546b0d-e4b1-4146-a879-9e760f5dd925' to data type int."} System.Exception {System.Data.SqlClient.SqlException} — Ali Nafees

levent levent · Accepted Answer · 2017-02-25T11:54:42

AspNetRoles table creating with this default script.. if u no changed it..

CREATE TABLE [dbo].[AspNetRoles](
[Id] [nvarchar](128) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

so try this...

var idParam = new SqlParameter
                {
                    ParameterName = "WhereCond",
                    Value = "where not Id is null" //it will be work but it makes no sense because it is primarkey field
                };
var courseList = ctx.Database.SqlQuery<UserRoles>("exec usp_RolesList @WhereCond ", idParam).ToList<UserRoles>();

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar while executing a stored procedure with Entity Framework

1 Answers