The data reader is incompatible. A member of the type, 'RoleId', does not have a corresponding column in the data reader with the same name

Question

Stored procedure works and deletes what I want but I still get this error after deleting:

The data reader is incompatible with the specified 'AMSIdentity.Models.RemoveRoleFromUserViewModel'. A member of the type, 'RoleId', does not have a corresponding column in the data reader with the same name.

I need to run the code without this error in the above

This code using ASP.NET MVC 5 and EF6 code first approach; I tried to use this code but always throws this error after delete.

This is the action method that I use

public ActionResult RemoveRoleFromUserConfirmed(string UserName, string RoleId)
{
        if (UserName == null && RoleId == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }

        SqlParameter param1 = new SqlParameter("@RoleId", RoleId);
        SqlParameter param2= new SqlParameter("@UserName", UserName);

        var remove = Identitydb.Database.SqlQuery<RemoveRoleFromUserViewModel>("admin.sp_RemoveUserFromRole @RoleId, @UserName",
            ((ICloneable)param1).Clone(),
            ((ICloneable)param2).Clone()).ToArray().ToList().FirstOrDefault();

        if (remove == null)
        {
            return HttpNotFound();
        }

        return RedirectToAction("Roles");
}

This is the view model that I use :

public class RemoveRoleFromUserViewModel
{
    [Key]
    [DisplayName("Role Id")]
    public string RoleId { get; set; }

    [DisplayName("Username")]
    public string UserName { get; set; }
}

This is the stored procedure code:

ALTER PROCEDURE [Admin].[sp_RemoveUserFromRole] 
    @RoleId NVARCHAR(50), 
    @UserName NVARCHAR(50)
AS
BEGIN
    DELETE FROM AspNetUserRoles 
    WHERE UserId = (SELECT Id 
                    FROM AspNetUsers 
                    WHERE UserName = @UserName) 
      AND RoleId = @RoleId 
END

I expect that this code will delete role from the specific user.

Answer 1

When you perform a DELETE in the stored procedure, you need to "audit" what got deleted. Then perform a SELECT on that audit-table.

You are taking advantage of the OUTPUT feature of sql server.

see:

https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017

and/or

https://www.sqlservercentral.com/articles/the-output-clause-for-insert-and-delete-statements

Below is a generic example of the TSQL you need.

DROP TABLE IF EXISTS [dbo].[Patient]
GO

CREATE TABLE [dbo].[Patient]
(
    [PatientKey]                            BIGINT          NOT NULL IDENTITY(1, 1),
    [PatientUniqueIdentifier]               VARCHAR(256)    NOT NULL,
    [CreateDate]                            DATETIMEOFFSET NOT NULL,
    CONSTRAINT [UC_Patient_PatientUniqueIdentifier] UNIQUE (PatientUniqueIdentifier)
)

/* now insert 3 sets of rows, with different create-dates */
INSERT INTO dbo.Patient (PatientUniqueIdentifier, [CreateDate]) SELECT TOP 10 NEWID() , '01/01/2001' from sys.objects
INSERT INTO dbo.Patient (PatientUniqueIdentifier, [CreateDate]) SELECT TOP 10 NEWID() , '02/02/2002' from sys.objects
INSERT INTO dbo.Patient (PatientUniqueIdentifier, [CreateDate]) SELECT TOP 10 NEWID() , '03/03/2003' from sys.objects

SELECT 'SeedDataResult' as Lable1, * FROM dbo.Patient

/* everything above is just setting up the example */
/* below would be the "guts"/implementation of your stored procedure */


DECLARE @PatientAffectedRowsCountUsingAtAtRowCount BIGINT
DECLARE @PatientAffectedRowsCountUsingCountOfOutputTable BIGINT

    DECLARE @PatientCrudActivityAuditTable TABLE ( [PatientUniqueIdentifier] VARCHAR(256), DatabaseKey BIGINT , MyCrudLabelForKicks VARCHAR(16));


        /* now delete a subset of all the patient rows , your delete will be whatever logic you implement */
        DELETE FROM [dbo].[Patient]
        OUTPUT  deleted.PatientUniqueIdentifier , deleted.PatientKey , 'mydeletelabel'
        INTO    @PatientCrudActivityAuditTable  ([PatientUniqueIdentifier] ,DatabaseKey , MyCrudLabelForKicks )
        WHERE
            CreateDate = '02/02/2002'

        /*you don't need this update statement, but i'm showing the audit table can be used with delete and update and insert (update here) */
        /*
        UPDATE [dbo].[Patient]
        SET     CreateDate = '03/03/2003'
        OUTPUT  inserted.PatientUniqueIdentifier , inserted.PatientKey, 'myupdatelabel'
        INTO    @PatientCrudActivityAuditTable  ([PatientUniqueIdentifier] ,DatabaseKey , MyCrudLabelForKicks)
        FROM    [dbo].[Patient] realTable
        WHERE CreateDate != '03/03/2003'
        */

        /* optionally, capture how many rows were deleted using @@ROWCOUNT */
        SELECT @PatientAffectedRowsCountUsingAtAtRowCount = @@ROWCOUNT

        /* or, capture how many rows were deleted using a simple count on the audit-table */
        SELECT @PatientAffectedRowsCountUsingCountOfOutputTable = COUNT(*) FROM @PatientCrudActivityAuditTable


SELECT 'ResultSetOneForKicks' as Label1,  'Rows that I Deleted' as MyLabel_YouCanRemoveThisColumn, DatabaseKey , PatientUniqueIdentifier FROM @PatientCrudActivityAuditTable

/* if so inclined, you can also send back the delete-COUNTS to the caller.  You'll have to code your IDataReader (ORM, whatever) to handle the multiple return result-sets */
/* most people will put the "counts" as the first result-set, and the rows themselves as the second result-set ... i have them in the opposite in this example */
SELECT 'ResultSetTwoForKicks' as Label1,   @PatientAffectedRowsCountUsingAtAtRowCount as '@PatientAffectedRowsCountUsingAtAtRowCountCoolAliasName' , @PatientAffectedRowsCountUsingAtAtRowCount as '@PatientAffectedRowsCountUsingAtAtRowCountCoolAliasName'

In my example, you would write the dotNet serialize code...(whatever flavor you use, raw IDataReader, ORM tool, whatever) against the PatientKey and PatientUniqueIdentifier columns coming back from the @PatientSurrogateKeyAudit table.

Answer 2

Hi All,

I got the answer from @Jeroen Mostert, The solution is to use the (ExecuteSqlCommand) rather than (SqlQuery) because I will never return data, I only execute the stored procedure with two parameters.

• This is the answer

SqlParameter param1 = new SqlParameter("@RoleId", RoleId);
SqlParameter param2= new SqlParameter("@UserName", UserName);

//I change this line from SqlQuery to ExecuteSqlCommand
var remove = Identitydb.Database.ExecuteSqlCommand("admin.sp_RemoveUserFromRole @RoleId, @UserName", param1, param2);

Thank you very much @Jeroen Mostert.
Regards,
Ali Mosaad
Software Developer