c# - Dapper 未能将 SQL uniqueidentifier 类型读取为字符串

Question

我有以下类(class):

public class User
    {
        public string Id { get; set; }
        public string UserName { get; set; }
        public string DisplayName { get; set; }
        public DateTime RegistrationDateTime { get; set; }
        public DateTime LastLoginDateTime { get; set; }
        public bool IsAdmin { get; set; }

        public IEnumerable<Session> Sessions { get; set; }

        public class Session
        {
            public string Id { get; set; }
            public string UserId { get; set; }
            public User User { get; set; }
            public DateTime StartDateTime { get; set; }
            public DateTime EndDateTime { get; set; }
        }
    }

在我的数据库中， Id设置为 uniqueidentifier数据类型。

当我在 Dapper 实现的情况下执行以下操作时，会出现以下错误消息:

public User.Session UpsertSession(User.Session session)
        {
            var item = SqlConnection.QuerySingle<User.Session>("[dbo].[UserUpsertSession]",
                new { session.UserId }, commandType: CommandType.StoredProcedure);
            return item;
        }

错误:

System.Data.DataException
  HResult=0x80131501
  Message=Error parsing column 0 (Id=689bfce8-333c-431e-8a35-60f85153d0c5 - Object)
  Source=Dapper
  StackTrace:
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
   at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1199
   at Dapper.SqlMapper.QuerySingle[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 783
   at Database.Managers.UserManager.UpsertSession(Session session) in C:\VSTS\Strategic Development\Applications\NotificationSystem\Main\Data\Managers\UserManager.cs:line 63
   at NotificationSystem.Web.Infrastructure.UsageTrackingActionFilter.OnActionExecuted(ActionExecutedContext context) in C:\VSTS\Strategic Development\Applications\NotificationSystem\Main\Web\Infrastructure\UsageTrackingActionFilter.cs:line 20
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()

Inner Exception 1:
InvalidCastException: Object must implement IConvertible.

这似乎是类型之间的某种映射问题。我想其他人也遇到过这种情况，但我无法在任何地方找到解决方案。

Answer 1

看来关键是用Guid?在您的实体类中，如下所示。

public class User
    {
        public string Id { get; set; }
        public string UserName { get; set; }
        public string DisplayName { get; set; }
        public DateTime RegistrationDateTime { get; set; }
        public DateTime LastLoginDateTime { get; set; }
        public bool IsAdmin { get; set; }

        public IEnumerable<Session> Sessions { get; set; }

        public class Session
        {
            //public string Id { get; set; }
            public Guid? Id { get; set; }
            public string UserId { get; set; }
            public User User { get; set; }
            public DateTime StartDateTime { get; set; }
            public DateTime EndDateTime { get; set; }
        }
    }

将值发送到服务时，必须先将其转换为字符串，然后再发送:

public User.Session UpsertSession(string userId, Guid? sessionId = null)
        {
            var item = SqlConnection.QuerySingle<User.Session>("[dbo].[UserUpsertSession]",
                new { userId, sessionId = sessionId?.ToString() }, commandType: CommandType.StoredProcedure);
            return item;
        }