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.

`sp_RemoveUserFromRole` does not return any rows and can't be mapped to any entity. If you need to return the user being deleted this way, issue a `SELECT` before the `DELETE` or use the `OUTPUT` clause. Alternatively, since you don't appear to need a return value at all (only if anything was deleted or not) use `ExecuteSqlCommand` instead and check the return value, which should reflect the row count. (That might need an explicit `SET NOCOUNT OFF`.) — Jeroen Mostert, May 20 '19 at 12:06
@JeroenMostert is correct. You can only serialize data that comes from SELECT statements. You are only performing a DELETE in the stored procedure. See my answer on how to perform a DELETE, but "audit" the rows that were deleted. — granadaCoder, May 20 '19 at 13:58
Hi @JeroenMostert, Thank you very much for your answer, i got the problem and you help me and i solve the problem. (I use ExecuteSqlCommand rather than SqlQuery) and it is worked very good. — ali, May 21 '19 at 05:32
OT if that query had returned anything, then you would need only that FirstOrDefault, not the ToList or ToArray — Hans Keﬆing, May 21 '19 at 06:04

granadaCoder · Answer 1 · 2022-09-02T12:52:50.733

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.

Hi @granadaCoder, I tried what you did it in the answer but didn't work. — ali, May 21 '19 at 05:00

score 0 · Accepted Answer · answered May 21 '19 at 05:46

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

Yes, you can call ExecuteSqlCommand if you don't care about seeing the individual rows that were deleted. Whether you were concerned about "Seeing the affected deleted rows".... was not clear in your original question. — granadaCoder, May 21 '19 at 13:52

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

2 Answers2