EF Core | Input string was not in a correct format but I am not parsing any strings to integers?-CodePudding

I am trying to authenticate a username and password in my API controller.

private User? Authenticate(UserLogin userLogin)
{
    string username = userLogin.Username;
    string password = userLogin.Password;

    var currentUser = _db.User.FirstOrDefault(a =>
        a.Username.ToLower() == username.ToLower() &&
        a.Password == password
    );

    if (currentUser != null)
    {
        return currentUser;
    }

    return null;
}

The above function is used in this API controller endpoint:

[AllowAnonymous]
[HttpPost("Login")]
public IActionResult Login([FromBody] UserLogin userLogin)
{
    var user = Authenticate(userLogin);
    if (user != null)
    {
        var token = Generate(user);
        return Ok(token);
    }

    return BadRequest("User not found");
}

My entity models are as follows:

public class UserLogin : IdBase
{
    [Column(TypeName = "nvarchar(60)")]
    [Required(ErrorMessage = "Username field is required.")]
    public string Username { get; set; }
    [Column(TypeName = "nvarchar(128)")]
    [Required(ErrorMessage = "Password field is required.")]
    public string Password { get; set; }
}

public class User : UserLogin
{
    [Column(TypeName = "nvarchar(70)")]
    [Required(ErrorMessage = "Name field is required.")]
    public string Name { get; set; }
    [Column(TypeName = "nvarchar(255)")]
    [Required(ErrorMessage = "Email field is required.")]
    public string Email { get; set; }
    [Column(TypeName = "text")]
    [Required(ErrorMessage = "Email field is required.")]
    public string AssignedRole { get; set; }
}

Odd behaviour that I do not understand:

When the username and password match what exists in the database, the following exception is returned to me in Postman:

System.FormatException: Input string was not in a correct format.

When the username and password do not match what exists in the database, I receive the expected 400 Bad Request "User not found" from the line:

return BadRequest("User not found");

Important thing to note:

When I change the query to read from a list instead of from the database like this:

var currentUser = UserConstant.Users.FirstOrDefault(a =>
                a.Username.ToLower() == username.ToLower() &&
                a.Password == password

Where UserConstant is a static list of type User:

public class UserConstant
{
    public static List<User> Users = new List<User>()
    {
        new User()
        {
            Name = "***",
            Username = "***",
            Email = "***",
            Password = "***",
            AssignedRole = "***"
        },
        new User()
        {
            Name = "***",
            Username = "***",
            Email = "***",
            Password = "***",
            AssignedRole = "***"
        }
    };
}

The code works as expected, authenticating the user and returning a token to me in Postman.

Why am I writing this question?

I have seen a lot of questions across multiple sites about the above exception. However most, if not all of what I have looked at concerning this issue is people trying to parse strings that do not contain any valid integers into integers.

As far as I am aware, nowhere in my case do I do such thing.

Every single property in the models I have used is of type string and has a data annotation of nvarchar with the exception of the AssignedRole property in the User model which has a data annotation of text.

This is reflected in the columns of the User table within my database:

Therefore, it is completely beyond me as to why I am experiencing this issue. My best guess is that there is something abstracted beneath Entity Framework that is trying to parse the input string to an integer but I have not a clue what would case EF to do this.

Update:

IdBase class:

public class IdBase
{
    [Key]
    [Column(TypeName = "nvarchar(36)")]
    [Required(ErrorMessage = "Id field is required.")]
    public int Id { get; set; }
}

_db.User.ToList() returns the same exception:

System.FormatException: Input string was not in a correct format

Full stack trace:

System.FormatException: Input string was not in a correct format.
   at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)
   at lambda_method24(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method25(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at AssetBlox.Controllers.LoginController.Authenticate(UserLogin userLogin) in D:\GitHub\AssetBlox\Controllers\LoginController.cs:line 66
   at AssetBlox.Controllers.LoginController.Login(UserLogin userLogin) in D:\GitHub\AssetBlox\Controllers\LoginController.cs:line 28
   at lambda_method19(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

CodePudding user response：

remove ToLower() from your query:

var currentUser = UserConstant.Users.FirstOrDefault(a =>
            a.Username == username &&
            a.Password == password);

The text comparison in SQL is not case sensitive and you probably have some null Usernames in the table where you should not.

CodePudding user response：

seems like you have used nvarchar in your entity model and varchar in database. And also defined your id field with nvarchar which should be int or long both in database and entity model. so can you please check after making this minor change.