where语句拼接错误 返回
hua0129 发布于2022/12/4
SELECT Id,Name,Price,CreateTime,CustomId FROM Orders WHERE id = @Conditid0 AND id = @Conditid1 OR ( id = @Conditid1002 AND id = @Conditid2002
替换后为：
SELECT Id,Name,Price,CreateTime,CustomId FROM Orders WHERE id = ? AND id = ? OR ( id = ?002 AND id = ? )
原因应该是：@Conditid1 @Conditid1002 这两个是包含关系，导致替换问题
热忱回答（14）
-
fate sta VIP0
2022/12/4提供完整用例
0 回复 -
hua0129 VIP0
2022/12/4
只是用例
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
namespace OrmTest
{
public class Demo1_Queryable
{
......
private static void QueryConditions()
{
Console.WriteLine("");
Console.WriteLine("#### Query Conditions Start ####");
SqlSugarClient db = GetInstance();
/*** By expression***/
//id=@id
var list = db.Queryable<Orders>().Where(it => it.Id == 1).ToList();
//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable<Orders>().Where(it => it.Id == 1 || it.Name.Contains("jack")).ToList();
//Create expression
var exp = Expressionable.Create<Orders>()
.And(it => it.Id == 1)
.Or(it => it.Name.Contains("jack")).ToExpression();
var list3 = db.Queryable<Orders>().Where(exp).ToList();
/*** By sql***/
//id=@id
var list4 = db.Queryable<Orders>().Where("id=@id", new { id = 1 }).ToList();
//id=@id or name like '%'+@name+'%'
var list5 = db.Queryable<Orders>().Where("id=@id or name like @name ", new { id = 1, name = "%jack%" }).ToList();
/*** By dynamic***/
//id=1
var conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" , FieldValueConvertFunc=it=>Convert.ToInt32(it) });//id=1
var student = db.Queryable<Orders>().Where(conModels).ToList();
//Complex use case
List<IConditionalModel> Order = new List<IConditionalModel>();
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc = it => Convert.ToInt32(it) });//id=1
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1", FieldValueConvertFunc = it => Convert.ToInt32(it) });// id like '%1%'
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty });
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.In, FieldValue = "1,2,3" });
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NotIn, FieldValue = "1,2,3" });
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NoEqual, FieldValue = "1,2,3" });
//conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNot, FieldValue = null });// id is not null
conModels.Add(new ConditionalCollections()
{
ConditionalList = new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()// (id=1 or id=2 and id=1)
{
//new KeyValuePair<WhereType, ConditionalModel>( WhereType.And ,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
new KeyValuePair<WhereType, ConditionalModel> (WhereType.Or,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" , FieldValueConvertFunc = it => Convert.ToInt32(it) }),
new KeyValuePair<WhereType, ConditionalModel> ( WhereType.And,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" ,FieldValueConvertFunc = it => Convert.ToInt32(it)})
}
});
var list6 = db.Queryable<Orders>().Where(conModels).ToList();
就是最后 这句 var list6 = db.Queryable<Orders>().Where(conModels).ToList(); 报出的错误
具体出错的代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
namespace SqlSugar
{
public abstract partial class SqlBuilderProvider : SqlBuilderAccessory, ISqlBuilder
{
......
public KeyValuePair<string, SugarParameter[]> ConditionalModelToSql(List<IConditionalModel> models, int beginIndex = 0)
{
if (models.IsNullOrEmpty()) return new KeyValuePair<string, SugarParameter[]>();
StringBuilder builder = new StringBuilder();
List<SugarParameter> parameters = new List<SugarParameter>();
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
var mainIndex = 0;
var indexTree = 0;
foreach (var model in models)
{
if (model is ConditionalModel)
{
var item = model as ConditionalModel;
if (item.CustomConditionalFunc != null)
{
var colIndex = mainIndex + beginIndex;
var colType = colIndex == 0 ? "" : "AND";
var custom = item.CustomConditionalFunc.GetConditionalSql(item, colIndex);
parameters.AddRange(custom.Value);
builder.AppendFormat(" "+colType + " "+custom.Key);
mainIndex++;
continue;
}
else if (item.FieldName == $"[value=sql{UtilConstants.ReplaceKey}]")
{
builder.Append(item.FieldValue);
continue;
}
// var index = mainIndex + beginIndex;
// var type = index == 0 ? "" : "AND";
var index = mainIndex + "_" + beginIndex; //debug
var type = index == "0_0" ? "" : "AND";
if (beginIndex > 0)
{
type = null;
}
string temp = " {0} {1} {2} {3} ";
string parameterName = string.Format("{0}Condit{1}{2}", sqlBuilder.SqlParameterKeyWord, item.FieldName, index);
if (parameterName.Contains("."))
我做了一些修改，可以通过测试。
0 回复 -
fate sta VIP0
2022/12/4
晚点验证一下
0 回复 -
fate sta VIP0
2022/12/4
我本地最新版本没有重现你说的BUG
0 回复 -
fate sta VIP0
2022/12/4
0 回复 -
fate sta VIP0
2022/12/4
完整用例
var conModels = new List<IConditionalModel>(); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc = it => Convert.ToInt32(it) });//id=1 List<IConditionalModel> Order = new List<IConditionalModel>(); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc = it => Convert.ToInt32(it) });//id=1 conModels.Add(new ConditionalCollections() { ConditionalList = new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()// (id=1 or id=2 and id=1) { //new KeyValuePair<WhereType, ConditionalModel>( WhereType.And ,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }), new KeyValuePair<WhereType, ConditionalModel> (WhereType.Or,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" , FieldValueConvertFunc = it => Convert.ToInt32(it) }), new KeyValuePair<WhereType, ConditionalModel> ( WhereType.And,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" ,FieldValueConvertFunc = it => Convert.ToInt32(it)}) } }); var list6 = db.Queryable<Order>().Where(conModels).ToList();0 回复 -
fate sta VIP0
2022/12/4
参数和值都对的
0 回复 -
fate sta VIP0
2022/12/4
程序也通过的
0 回复 -
hua0129 VIP0
2022/12/4
我也不知道什么原因，只是根据现象做出推测：
可以看到原始的sql ，有两个 ID，@Conditid1 和 @Conditid1002， 不是惟一的，而是包含关系。
如果使用@Conditid1 替换的话，一次会替换两个，正好是现在的现象。
所以是不是每个 ID 编号应该唯一，这样 字符串替换，就不会出现这个现象。
0 回复 -
fate sta VIP0
2022/12/4
@hua0129：你是用的什么工具看的SQL，手动替换算法有问题吧
0 回复 -
fate sta VIP0
2022/12/4
替换后的SQL只供参与，ORM执行到数据库是参数化的
0 回复 -
fate sta VIP0
2022/12/4
以参数化的为准
0 回复 -
hua0129 VIP0
2022/12/5
我的SQL 是一边调试，一边通过 Console.WriteLine 输出的。
我是用 vscode调试的。
vs我也用，但是看调试信息，没有vscode方便。
0 回复 -
hua0129 VIP0
2022/12/5
@fate sta：是以参数化为准，但是我这个报错就是因为 sql错误导致的， SQL 出现 "?002", 所以我才会调试，寻找原因。
我个人以为，如果想彻底解决类似的问题， 所有的参数ID最好是唯一的。
目前的id格式是 @Condit_field_###_###， 要保证唯一， field 部分长度要保证一致。否则遇上特殊情况，比如两个字段分别为， abc ， abc_1. 那么参数 名 可能为 @Condit_abc_1_1, @Condit_abc_1_1_1, 这样 在替换参数为 “？” 时，就会比较复杂。
当前的算法中，还没有 “_”, 参数名称可能为包含关系的情况就更多了。
0 回复