提问人：mml 提问时间：4/19/2022 最后编辑：mml 更新时间：4/19/2022 访问量：856
System.InvalidCastException：无法在 MySqlConnector.Core.Row.GetInt32 （Int32 序号） 处将 VarChar 转换为 Int32
System.InvalidCastException: Can't convert VarChar to Int32 at MySqlConnector.Core.Row.GetInt32(Int32 ordinal)
问：
我有一个 API，其中有一个称为 Token Controller 的控制器，它可以验证用户并提供令牌。
问题是它一直工作到现在，我没有改变任何东西，所以我不知道发生了什么。请帮忙。
这是我的 Token 控制器的代码
public TokenController(IConfiguration configuration, ICrUserInfoService crUserInfoService, IPasswordService passwordService)
{
_configuration = configuration;
_crUserInfoService = crUserInfoService;
_passwordService = passwordService;
}
[HttpPost]
public async Task<IActionResult> Authentication(UserLogin login)
{
//if it is a valid user
var validation = await IsValidUser(login);
if (validation.Item1)
{
var token = GenerateToken(validation.Item2);
return Ok(new { token });
}
return NotFound("Unvalid User");
}
private async Task<(bool, CrUserInfo)> IsValidUser(UserLogin login)
{
var user = await _crUserInfoService.GetLoginByCredentials(login);
bool isValid;
if (user == null)
{
isValid = false;
}
else
{
isValid = _passwordService.Check(user.Password, login.Password);
}
return (isValid, user);
}
private string GenerateToken(CrUserInfo user)
{
//Header
var symmetricSecurityKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_configuration["Authentication:SecretKey"]));
var signingCredentials = new SigningCredentials(symmetricSecurityKey, SecurityAlgorithms.HmacSha256);
var header = new JwtHeader(signingCredentials);
//Claims
var claims = new[]
{
new Claim(ClaimTypes.Name, user.Username),
new Claim("User", user.Displayname),
new Claim(ClaimTypes.Role, user.Role.ToString()),
};
//Payload
var payload = new JwtPayload
(
_configuration["Authentication:Issuer"],
_configuration["Authentication:Audience"],
claims,
DateTime.Now,
DateTime.Now.AddMinutes(30)
);
var token = new JwtSecurityToken(header, payload);
return new JwtSecurityTokenHandler().WriteToken(token);
}
}
正如我所说，到目前为止它工作正常，这是错误
System.InvalidCastException: Can't convert VarChar to Int32
at MySqlConnector.Core.Row.GetInt32(Int32 ordinal) in /_/src/MySqlConnector/Core/Row.cs:line 223
at MySqlConnector.MySqlDataReader.GetInt32(Int32 ordinal) in /_/src/MySqlConnector/MySqlDataReader.cs:line 242
at lambda_method23(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
at Crijoya.Infrastructure.Repositories.CrUserInfoRepository.GetLoginByCredentials(UserLogin login) in C:\Users\USER\source\repos\Api Crijoya\Crijoya.Data\Repositories\CrUserInfoRepository.cs:line 15
at Crijoya.Core.Services.CrUserInfoService.GetLoginByCredentials(UserLogin userLogin) in C:\Users\USER\source\repos\Api Crijoya\Crijoya.Model\Services\CrUserInfoService.cs:line 20
at Api_Crijoya.Controllers.TokenController.IsValidUser(UserLogin login) in C:\Users\USER\source\repos\Api Crijoya\Api Crijoya\Controllers\TokenController.cs:line 51
at Api_Crijoya.Controllers.TokenController.Authentication(UserLogin login) in C:\Users\USER\source\repos\Api Crijoya\Api Crijoya\Controllers\TokenController.cs:line 40
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
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>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_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>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
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.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
我不确定问题出在代码中，因为我几分钟前说过它正在工作并给出正确的令牌。请帮忙，我不知道如何处理这个问题。
编辑：
我的实体 CrUserInfo 的存储库，其中包含用户和密码
public CrUserInfoRepository(db_crijoyaContext context) : base(context) { }
public async Task<CrUserInfo> GetLoginByCredentials(UserLogin login)
{
return await _entities.FirstOrDefaultAsync(x => x.Username == login.User);
}
这是我的实体 CrUserInfo
public partial class CrUserInfo : BaseEntity
{
// public int IdUserInfo { get; set; }
public int IdUser { get; set; }
public string Displayname { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public RoleType Role { get; set; }
public virtual CrUser IdUserNavigation { get; set; }
}
我的 DTO 实体
public class CrUserInfoDto
{
public int Id { get; set; }
public int IdUser { get; set; }
public string Displayname { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public RoleType Role { get; set; }
}
我的数据库
这是脚本
CREATE TABLE `cr_userInfo` (
`id_userInfo` int(6) unsigned NOT NULL AUTO_INCREMENT,
`id_user` int(6) unsigned NOT NULL,
`displayname` varchar(50) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL,
`role` varchar(30) NOT NULL,
PRIMARY KEY (`id_userInfo`) USING BTREE,
UNIQUE KEY `id_user` (`id_user`) USING BTREE,
CONSTRAINT `cr_userInfo_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `cr_user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
这是它在停止工作之前所做的
ONMODEL创建代码：
modelBuilder.Entity<CrUser>(entity =>
{
entity.HasKey(e => e.Id)
.HasName("PRIMARY");
entity.ToTable("cr_user");
entity.HasIndex(e => e.IdCompany, "company");
entity.Property(e => e.Id)
.HasColumnType("uint(6) unsigned")
.HasColumnName("id_user");
entity.Property(e => e.Active)
.HasColumnType("tinyint(1) unsigned")
.HasColumnName("active")
.HasDefaultValueSql("'1'");
entity.Property(e => e.IdCompany)
.HasColumnType("int(6) unsigned")
.HasColumnName("id_company");
entity.Property(e => e.Warehouse)
.HasColumnType("int(6)")
.HasColumnName("warehouse")
.HasDefaultValueSql("'NULL'");
entity.HasOne(d => d.IdCompanyNavigation)
.WithMany(p => p.CrUsers)
.HasForeignKey(d => d.IdCompany)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("cr_user_ibfk_1");
});
modelBuilder.Entity<CrUserInfo>(entity =>
{
entity.HasKey(e => e.Id)
.HasName("PRIMARY");
entity.ToTable("cr_userInfo");
entity.HasIndex(e => e.IdUser, "id_user")
.IsUnique();
entity.Property(e => e.Id)
.HasColumnType("uint(6) unsigned")
.HasColumnName("id_userInfo");
entity.Property(e => e.Displayname)
.IsRequired()
.HasMaxLength(50)
.HasColumnName("displayname");
entity.Property(e => e.IdUser)
.HasColumnType("int(6) unsigned")
.HasColumnName("id_user");
entity.Property(e => e.Password)
.IsRequired()
.HasMaxLength(255)
.HasColumnName("password");
entity.Property(e => e.Role)
.IsRequired()
.HasMaxLength(30)
.HasColumnName("role");
entity.Property(e => e.Username)
.IsRequired()
.HasMaxLength(50)
.HasColumnName("username");
entity.HasOne(d => d.IdUserNavigation)
.WithOne(p => p.CrUserInfo)
.HasForeignKey<CrUserInfo>(d => d.IdUser)
.HasConstraintName("cr_userInfo_ibfk_1");
});
答：
2赞
ProgrammingLlama
4/19/2022
#1
您必须从以下位置更新代码：OnModelCreating
entity.Property(e => e.Role)
.IsRequired()
.HasMaxLength(30)
.HasColumnName("role");
为了表明它应该是：string
entity.Property(e => e.Role)
.IsRequired()
.HasMaxLength(30)
.HasColumnName("role")
.HasConversion<string>();
根据文档，这应该会在数据库类型和 C# 中自动转换：varcharRoleType
例如，枚举到字符串的转换用作上面的示例，但当提供程序类型使用泛型类型配置为字符串时，EF Core 实际上会自动执行此操作：
HasConversion
评论
GetLoginByCredentialsvarcharstringMy database fields are string这就是错误所抱怨的。至少有一个字段不是字符串。也许是身份证？请发布类和表架构（语句）CrUserInfoCREATE TABLECrUserOnModelCreating