如何让Select预先处理再进行查询

[aaa930811]

我想让select里面的判断先进行完之后再执行SQL操作，否则会生成Case When 的SQL语句，这里我的语句进行Case When 操作之后有BUG，所以我想先判断完然后再生成SQL查询，该如何操作？

     var mediaKeys = _dbClient.Queryable<MediaMst>().InnerJoin<OOSKey>((m, o) => m.MediaUID == o.MediaUID)
         .Where((m, o) => o.IsPrimary && o.Status == 1)
         .Select((m, o) => new MediaKey
         {
             AccessKey = _oSSUseCrypt ? DecryptAccessKey(o.AccessKey) : o.AccessKey,
             SecretKey = _oSSUseCrypt ? DecryptSecretKey(o.SecretKey) : o.SecretKey,
         }, true)
         .ToList();

其中DecryptAccessKey和DecryptSecretKey是自定义的SqlFunc

[aaa930811]

貌似可以通过使用动态表达式的写法，还有其他更好的方法吗？

     Expression<Func<MediaMst, OOSKey, MediaKey>> selectExp;
     if (_oSSUseCrypt)
     {
         selectExp = (m, o) => new MediaKey()
         {
             AccessKey = DecryptAccessKey(o.AccessKey),
             SecretKey = DecryptSecretKey(o.SecretKey)
         };
     }
     else
     {
         selectExp = (m, o) => new MediaKey()
         {
             AccessKey = o.AccessKey,
             SecretKey = o.SecretKey
         };
     }

     var mediaKeys = _dbClient.Queryable<MediaMst>().InnerJoin<OOSKey>((m, o) => m.MediaUID == o.MediaUID)
         .Where((m, o) => o.IsPrimary && o.Status == 1)
         .Select(selectExp, true)
         .ToList();

[DotNetNext]

SELECT中不支持自定义方法吧 DecryptAccessKey(o.AccessKey)

[DotNetNext]

正确应该这样写

[DotNetNext]

 var mediaKeys = _dbClient.Queryable<MediaMst>().InnerJoin<OOSKey>((m, o) => m.MediaUID == o.MediaUID)
     .Where((m, o) => o.IsPrimary && o.Status == 1)
     .Select((m, o) => new MediaKey
     {
         AccessKey =  o.AccessKey,
         SecretKey = o.SecretKey,
     }, true)
    .Mappper(it=>{
         it.AccessKey = _oSSUseCrypt ? it.AccessKey :DecryptAccessKey( it.AccessKey),
          it.SecretKey = _oSSUseCrypt ? it.AccessKey: DecryptSecretKey( it.SecretKey)
     })
     .ToList();

[aaa930811]

SELECT中不支持自定义方法吧 DecryptAccessKey(o.AccessKey)

啊不是支持的吗，可以使用

[aaa930811]

 var mediaKeys = _dbClient.Queryable<MediaMst>().InnerJoin<OOSKey>((m, o) => m.MediaUID == o.MediaUID)
     .Where((m, o) => o.IsPrimary && o.Status == 1)
     .Select((m, o) => new MediaKey
     {
         AccessKey =  o.AccessKey,
         SecretKey = o.SecretKey,
     }, true)
    .Mappper(it=>{
         it.AccessKey = _oSSUseCrypt ? it.AccessKey :DecryptAccessKey( it.AccessKey),
          it.SecretKey = _oSSUseCrypt ? it.AccessKey: DecryptSecretKey( it.SecretKey)
     })
     .ToList();

这个是调用C#的方法吧？我需要调用原生SQL方法

          var expMethods = new List<SqlFuncExternal>
          {
              new SqlFuncExternal()
              {
                  UniqueMethodName = "DecryptAccessKey",
                  MethodValue = (expInfo, dbType, expContext) =>
                  {
                      if (dbType == DbType.MySql)
                      {
                          return string.Format("CAST(AES_DECRYPT({0},'123') AS CHAR(100))", expInfo.Args[0].MemberName);
                      }
                      else if (dbType == DbType.SqlServer)
                      {
                          return string.Format("Convert(nvarchar(255), DecryptByPassPhrase('0574', {0}, 32423, 'xxx'))", expInfo.Args[0].MemberName);
                      }
                      else
                          throw new Exception("未实现");
                  }
              },
              new SqlFuncExternal()
              {
                  UniqueMethodName = "DecryptSecretKey",
                  MethodValue = (expInfo, dbType, expContext) =>
                  {
                      if (dbType == DbType.MySql)
                      {
                          return string.Format("CAST(AES_DECRYPT({0},'123') AS CHAR(100))", expInfo.Args[0].MemberName);
                      }
                      else if (dbType == DbType.SqlServer)
                      {
                          return string.Format("Convert(nvarchar(255), DecryptByPassPhrase('365', {0}, 2342, 'xxx'))", expInfo.Args[0].MemberName);
                      }
                      else
                          throw new Exception("未实现");
                  }
              }
          };

 _dbClient = new SqlSugarScope(new ConnectionConfig()
 {
     DbType = config.DbType,
     ConnectionString = config.ConnectionString,
     IsAutoCloseConnection = true,
     ConfigureExternalServices = new ConfigureExternalServices()
     {
         SqlFuncServices = expMethods//set ext method
     }
 }

[DotNetNext]

   .Select((m, o) => new MediaKey
         {
             AccessKey = _oSSUseCrypt==true ? DecryptAccessKey(o.AccessKey) : o.AccessKey,
             SecretKey = _oSSUseCrypt==true  ? DecryptSecretKey(o.SecretKey) : o.SecretKey,
         }, true)

如果扩展方法那就直接这样用_oSSUseCrypt加上==true

[aaa930811]

   .Select((m, o) => new MediaKey
         {
             AccessKey = _oSSUseCrypt==true ? DecryptAccessKey(o.AccessKey) : o.AccessKey,
             SecretKey = _oSSUseCrypt==true  ? DecryptSecretKey(o.SecretKey) : o.SecretKey,
         }, true)

如果扩展方法那就直接这样用_oSSUseCrypt加上==true

这样会生成CASE When的语句，执行有BUG，即使判断条件是true但是也没有走那个判定结果，很奇怪

[DotNetNext]

AOP看生成的SQL

[DotNetNext]

能有case when就对了

[aaa930811]

能有case when就对了

不想在数据库里做判断，想判断完了再去查询数据库

[DotNetNext]

select中又不是where 在数据库里加case when问题不大

[DotNetNext]

你不想加就if else表达式吧，暂时没有好办法 如果你想方便就扔select里面 还有更好的方案 你发新的issue这个先关闭。

[aaa930811]

select中又不是where 在数据库里加case when问题不大

主要是生成的语句执行有BUG

[DotNetNext]

生成的问题你发出来，你不发出来我怎么知道哪有BUG

[DotNetNext]

我这边测试都正常的

[aaa930811]

稍等

[DotNetNext]

代码+AOP打印的SQL

[aaa930811]

代码：
     var mediaKeys = _dbClient.Queryable<MediaMst>().InnerJoin<OOSKey>((m, o) => m.MediaUID == o.MediaUID)
         .Where((m, o) => o.IsPrimary && o.Status == 1)
         .Select((m, o) => new MediaKey()
         {
             AccessKey = _oSSUseCrypt == true ? DecryptAccessKey(o.AccessKey) : o.AccessKey,
             SecretKey = _oSSUseCrypt == true ? DecryptSecretKey(o.SecretKey) : o.SecretKey,
         }, true)
         .ToList();

生成的SQL：

SELECT ( CASE  WHEN ( 1 = 1 ) THEN CAST(AES_DECRYPT(`o`.`AccessKey`,'T0mtaw@0574') AS CHAR(100))  ELSE `o`.`AccessKey` END ) AS `AccessKey` , ( CASE  WHEN ( 1 = 1 ) THEN CAST(AES_DECRYPT(`o`.`SecretKey`,'Tomt@w365') AS CHAR(100))  ELSE `o`.`SecretKey` END ) AS `SecretKey` ,`m`.`MediaUID` AS `MediaUID` ,`m`.`MediaName` AS `MediaName` ,`m`.`MediaHost` AS `MediaHost` ,`m`.`Path` AS `Path` ,`m`.`PathType` AS `MediaType` ,`o`.`VisitIP` AS `VisitIP` FROM `MediaMst` `m` Inner JOIN `OOSKey` `o` ON ( `m`.`MediaUID` = `o`.`MediaUID` )   WHERE ( ( `o`.`IsPrimary`=1 ) AND( `o`.`Status` = 1 ))

放进数据库查询的结果：

但是实际结果应该是：

[DotNetNext]

( CASE WHEN ( 1 = 1 ) THEN CAST(AES_DECRYPT(o.AccessKey,'T0mtaw@0574') AS CHAR(100)) ELSE o.AccessKey END ) AS AccessKey

这个SQL没错吧1=1是true走了加密逻辑

[DotNetNext]

_oSSUseCrypt == true ? DecryptAccessKey(o.AccessKey) : o.AccessKey.ToString() 我知道了可能是casewhen类型不同，第二个参数加个.ToString()

[DotNetNext]

把前casewhen2边都是字符串类型

[DotNetNext]

SQL我看了是没错的，可能你需要小调整一下就行了比加TOSTRING等

[aaa930811]

SQL我看了是没错的，可能你需要小调整一下就行了比加TOSTRING等

好的原来是这样，我试试

[aaa930811]

SQL我看了是没错的，可能你需要小调整一下就行了比加TOSTRING等

可以了，谢谢，这个还真的不容易知道原因