首页 > SqlSugar

执行查询报“”从数据类型 nvarchar 转换为 float 时出错“错误” 返回

SqlSugar 沟通中

 12  387

LiYun. 发布于2025/1/17

悬赏：0 飞吻

有字符串表达式 "key=55 and Convert.ToDouble(Value)>1"转换成Expression后执行查询报错，

Key是int,Value是nvarchar(max)

错误：

09:58:42:198 Microsoft.Data.SqlClient.SqlException (0x80131904): 从数据类型 nvarchar 转换为 float 时出错。 09:58:42:198 at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 09:58:42:198 at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 09:58:42:198 at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 09:58:42:198 at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) 09:58:42:198 at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) 09:58:42:198 at Microsoft.Data.SqlClient.SqlDataReader.ReadAsyncExecute(Task task, Object state) 09:58:42:198 at Microsoft.Data.SqlClient.SqlDataReader.InvokeAsyncCall[T](AAsyncCallContext`1 context) 09:58:42:198 --- End of stack trace from previous location --- 09:58:42:198 at SqlSugar.DbBindAccessory.GetEntityListAsync[T](SqlSugarProvider context, IDataReader dataReader) 09:58:42:198 at SqlSugar.DbBindProvider.DataReaderToListAsync[T](Type type, IDataReader dataReader) 09:58:42:198 at SqlSugar.QueryableProvider`1.GetDataAsync[TResult](Boolean isComplexModel, Type entityType, IDataReader dataReader) 09:58:42:198 at SqlSugar.QueryableProvider`1.GetDataAsync[TResult](KeyValuePair`2 sqlObj) 09:58:42:198 at SqlSugar.QueryableProvider`1._ToListAsync[TResult]()+

生成的SQL语句是

SELECT [Key],[Value] FROM A WHERE (( [Key] = 55 ) AND (CAST([Value] AS FLOAT) > 1 ))

该语句在数据库中正常执行。

请问该怎么处理呢？

热忱回答（12）

fate sta VIP0
2025/1/17

提供实体类

0 回复
fate sta VIP0
2025/1/17

还有代码

0 回复
LiYun. VIP0
2025/1/17

public class A{
[SugarColumn(ColumnName = "Key" ,IsPrimaryKey = true ,IsIdentity = true )]
public int Key{set;get;}

[SugarColumn(ColumnName = "Value" )]
public string Value { get; set; }
}

var srCondition ="Key=55 and Convert.ToDouble(Value)>1";
var queryExpression = string.IsNullOrWhiteSpace( srCondition ) ? null : (Expression<Func<A, bool>>)
DynamicExpressionParser.ParseLambda( typeof( A), typeof( bool ),
srCondition );

var ret=await DbScoped.Sugar.Queryable<A>()
.WhereIF( queryExpression != null, queryExpression )
.ToListAsync();

0 回复
LiYun. VIP0
2025/1/17

@fate sta：
public class A{
[SugarColumn(ColumnName = "Key" ,IsPrimaryKey = true ,IsIdentity = true )]
public int Key{set;get;}

[SugarColumn(ColumnName = "Value" )]
public string Value { get; set; }
}

var srCondition ="Key=55 and Convert.ToDouble(Value)>1";
var queryExpression = string.IsNullOrWhiteSpace( srCondition ) ? null : (Expression<Func<A, bool>>)
DynamicExpressionParser.ParseLambda( typeof( A), typeof( bool ),
srCondition );

var ret=await DbScoped.Sugar.Queryable<A>()
.WhereIF( queryExpression != null, queryExpression )
.ToListAsync();

0 回复
LiYun. VIP0
2025/1/17

@fate sta：
Value在数据库中的类型是nvarchar(max) 其中存储有数字和字符，如“1”，“2.1”，“正常”

0 回复
fate sta VIP0
2025/1/17

你这个用到了第三方组件
按模版提供完整可以重现的DEMO
https://www.donet5.com/Home/Doc?typeId=2366
删掉OBJ和BIN打包上传

0 回复
LiYun. VIP0
2025/1/17

@fate sta：
Test.QueryData.zip
当key对应的Value是非数字类型时就出问题了

0 回复
LiYun. VIP0
2025/1/17

@fate sta：

数据库结构；数据如这种就会报错

0 回复
fate sta VIP0
2025/1/18

周一前解决

0 回复
fate sta VIP0
2025/1/19

SELECT [Id],[Key],[Value] FROM [Atable] WHERE (( [Key] = 1 ) AND (CAST([Value] AS MONEY) > 1
这个sql就报错了，应该不能用varchar max

0 回复
fate sta VIP0
2025/1/19

表不能这么设计， (CAST([Value] AS MONEY) 直接转换失败

0 回复
LiYun. VIP0
2025/1/20

@fate sta：
好的，我在看看吧，感谢解答

0 回复