Why am I getting SqlNullException when getting Users.ToListAsync() when it is not null

Question

I've been trying to find the solution to this problem but I can't seem to find one. In this controller, I'm trying to get list of users to be inserted in User Roles View Model to check user's roles (Admin, Student or Counselor). But I kept getting this error and I couldn't find what is that get_Int32() as stated in error below.

Appreciate it if anyone are able to help me solve this problem.

This is my UserRolesController:

public async Task<IActionResult> Index()
{
    var users = await _userManager.Users.ToListAsync(); //it gets the list of users - this one got error null
    var userRolesViewModel = new List<UserRolesViewModel>(); //the model

    foreach (ApplicationUser user in users)
    {
        // inserting user data from database to view model
        var thisViewModel = new UserRolesViewModel
            {
                UserId = user.Id,
                Email = user.Email,
                FirstName = user.FirstName,
                LastName = user.LastName,
                Roles = await GetUserRoles(user)
            };
        userRolesViewModel.Add(thisViewModel);
    }

    return View(userRolesViewModel);
}

private async Task<List<string>> GetUserRoles(ApplicationUser user)
{
    return new List<string>(await _userManager.GetRolesAsync(user));
}

My UserRolesViewModel:

public class UserRolesViewModel
{
    public string UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public IEnumerable<string> Roles { get; set; }
}

This is my user's table

CREATE TABLE [Identity].[User] 
(
    [Id]                   NVARCHAR(450)     NOT NULL,
    [UserName]             NVARCHAR(256)     NULL,
    [NormalizedUserName]   NVARCHAR(256)     NULL,
    [Email]                NVARCHAR(256)     NULL,
    [NormalizedEmail]      NVARCHAR(256)     NULL,
    [EmailConfirmed]       BIT               NOT NULL,
    [PasswordHash]         NVARCHAR(MAX)     NULL,
    [SecurityStamp]        NVARCHAR(MAX)     NULL,
    [ConcurrencyStamp]     NVARCHAR(MAX)     NULL,
    [PhoneNumber]          NVARCHAR(MAX)     NULL,
    [PhoneNumberConfirmed] BIT               NOT NULL,
    [TwoFactorEnabled]     BIT               NOT NULL,
    [LockoutEnd]           DATETIMEOFFSET(7) NULL,
    [LockoutEnabled]       BIT               NOT NULL,
    [AccessFailedCount]    INT               NOT NULL,
    [FirstName]            NVARCHAR(MAX)     NULL,
    [LastName]             NVARCHAR(MAX)     NULL,
    [ProfilePicture]       VARBINARY(MAX)    NULL,
    [UsernameChangeLimit]  INT               DEFAULT ((0)) NOT NULL,
    [Fullname]             NVARCHAR(50)      NULL,
    [Reg_no]               VARCHAR(50)       NULL,
    [Faculty]              VARCHAR(50)       NULL,
    [Programme]            VARCHAR(50)       NULL,
    [YearSem]              NVARCHAR(10)      NULL,
    [Cgpa]                 VARCHAR(10)       NULL,
    [Religion]             VARCHAR(50)       NULL,
    [Country]              VARCHAR(50)       NULL,
    [Race]                 VARCHAR(50)       NULL,
    [Address]              VARCHAR(100)      NULL,
    [Siblings]             INT               NULL,
    [BirthOrder]           INT               NULL,
    [Hobby]                NVARCHAR(50)      NULL,
    [Ambition]             NVARCHAR(50)      NULL,
    [Birthdate]            DATE              NULL,
    [About]                NVARCHAR(MAX)     NULL,

    CONSTRAINT [PK_User] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

CREATE NONCLUSTERED INDEX [EmailIndex]
ON [Identity].[User]([NormalizedEmail] ASC);
GO

CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [Identity].[User]([NormalizedUserName] ASC) 
WHERE ([NormalizedUserName] IS NOT NULL);

The error that I'm getting:

Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
Microsoft.Data.SqlClient.SqlDataReader.GetInt32(int i)
lambda_method(Closure , QueryContext , DbDataReader , ResultContext , int[] , ResultCoordinator )
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
UserManagement.MVC.Controllers.UserRolesController.Index() in UserRolesController.cs +
var users = await _userManager.Users.ToListAsync();

Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask.get_Result()
System.Runtime.CompilerServices.ValueTaskAwaiter.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker. g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
< Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context) Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext) Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext) Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

I couldn' find your GetUserRoles function. We can't help without it — Serge
I don't understand how it works _userManager.GetRolesAsync(user)); At first I thought that _userManager is your dbContext. Could you show UserManager class maybe partialy - GetRolesAsync and Users — Serge

pakeha_by pakeha_by · Accepted Answer · 2020-11-13T02:45:36

You have exception in EntityFramework in that line:

var users = await _userManager.Users.ToListAsync();

Based on stack trace, most likely reason is that User class has got Integer property but underlying column has got NULL value in the DB table. You need to declare User property as nullable.

Why am I getting SqlNullException when getting Users.ToListAsync() when it is not null

1 Answers