SqlSugar的表达式树太深后面会无法正确解析 返回
SqlSugar
沟通中
8
334
=_= 发布于2025/11/17
悬赏：0 飞吻
var query = _db.Queryable<Category>()
.FilterDocumentByPermission(permissions)
.Select(m => new CategoryResponseModel(), true)
.MergeTable();
query = query.Where(m => ((((((false || m.CategoryType.Contains("行政")) || m.Name.Contains("行政")) || m.ParentId.Contains("行政")) || m.Id.Contains("行政")) || m.CreateUser.Contains("行政")) || m.UpdateUser.Contains("行政")));
//query = query.GetSearchQuery(request);
RefAsync<int> total = 0;
var table = await query.ToPageListAsync(request.Page, request.Size, total);
FilterDocumentByPermission是一个where条件，里面有比较复杂的判断
下面是生成的sql，很明显后面的where条件的两个参数没有正确解析，当我减少条件时，后面错误的参数也会对应减少，直到正确SELECT COUNT(1)
FROM (
SELECT *
FROM (
SELECT
"categorytype" AS "categorytype",
"name" AS "name",
"parentid" AS "parentid",
"createuser" AS "createuser",
"updateuser" AS "updateuser",
"id" AS "id",
"type" AS "type",
"createtime" AS "createtime",
"updatetime" AS "updatetime",
"active" AS "active",
"processed" AS "processed",
"version" AS "version"
FROM "link_category"
WHERE
(
(
(@Const0 = @SearchPermission1)
AND (
"ownerusers" && ARRAY['admin']
OR (
NOT "relatedusers" IS NULL
OR "relatedusers" = '{}'
AND "relatedusers" && ARRAY['admin']
)
)
)
OR (
(@Const4 = @SearchPermission5)
AND (
(
"ownerusers" && ARRAY['admin']
OR (
NOT "relatedusers" IS NULL
OR "relatedusers" = '{}'
AND "relatedusers" && ARRAY['admin']
)
)
OR (
NOT "relateddepartments" IS NULL
OR "relateddepartments" = '{}'
AND (1 = 2)
)
)
)
OR (
(@Const9 = @SearchPermission10)
AND (
"ownerusers" && ARRAY['admin']
OR (
NOT "relatedusers" IS NULL
OR "relatedusers" = '{}'
AND "relatedusers" && ARRAY['admin']
)
)
OR (@Const13 = @SearchPermission14)
)
)
) AS MergeTable
WHERE
(
1 = 2
OR "categorytype" LIKE concat('%', @MethodConst16, '%')
OR "name" LIKE concat('%', @MethodConst17, '%')
OR "parentid" LIKE concat('%', @MethodConst18, '%')
OR "id" LIKE concat('%', @MethodConst19, '%')
OR "createuser" LIKE concat('%', 'admin0', '%')
OR "updateuser" LIKE concat('%', 'admin1', '%')
)
) AS CountTable;
热忱回答（8）
-
=_= VIP0
2025/11/17
怀疑和pg的扩展有关
sqlFuncExternals.Add(new SqlFuncExternal() { UniqueMethodName = "PgArrayOverlapByString", MethodValue = (expInfo, dbType, expContext) => { if (dbType == DbType.PostgreSQL) { var sourceArray = expInfo.Args[1]?.MemberValue as IEnumerable; if (sourceArray == null) { return " (1=2) "; } List<object> sourceValues = new List<object>(); foreach (var item in sourceArray) { if (item == null) { throw new Exception("传参必须为字符串"); } if (item is not string) { throw new Exception("传参必须为字符串"); } sourceValues.Add(item); } var value = expInfo.Args[0].MemberName; string? inValueString = null; if (sourceValues != null && sourceValues.Count > 0) { inValueString = string.Join(",", sourceValues.Select(m => $"'{m}'")); } if (inValueString.IsNullOrEmpty()) { return " (1=2) "; } else { return string.Format(" ( {0} && ARRAY[{1}] ) ", value, inValueString); } } else { throw new Exception("未实现"); } } });0 回复 -
fate sta VIP0
2025/11/17.FilterDocumentByPermission(permissions)提供具体用例。 我这边根本看到是什么东西。
0 回复 -
fate sta VIP0
2025/11/17
.FilterDocumentByPermission(permissions)提供具体用例。 我这边根本看到是什么东西。
0 回复 -
=_= VIP0
2025/11/17
@fate sta：
return query.Where(m => (userPermissions.MenuPermission.SearchPermission == 1 && (SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, param) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })))) || (userPermissions.MenuPermission.SearchPermission == 2 && (SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, param) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedDepartments) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedDepartments, userPermissions.MenuPermission.SearchDepartmentIds)))) || ((userPermissions.MenuPermission.SearchPermission == 3) && (SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, param) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })))) || userPermissions.MenuPermission.SearchPermission == 4); 这是FilterDocumentByPermission的内容 /// <summary> /// 部门关联 /// </summary> [SugarColumn(ColumnDescription = "部门关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedDepartments { get; set; } = Array.Empty<string>(); /// <summary> /// 用户关联 /// </summary> [SugarColumn(ColumnDescription = "用户关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedUsers { get; set; } = Array.Empty<string>(); /// <summary> /// 所有者 /// </summary> [SugarColumn(ColumnDescription = "所有者", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] OwnerUsers { get; set; } = Array.Empty<string>();
一个比较复杂的嵌套条件0 回复 -
fate sta VIP0
2025/11/17
https://www.donet5.com/Home/Doc?typeId=2366
看不出问题，按模版提供可以重现的DEMO
0 回复 -
=_= VIP0
2025/11/17
@=_=：
var searchPermission = userPermissions.MenuPermission.SearchPermission; var editPermission = userPermissions.MenuPermission.SearchPermission; var userIdArray = new[] { userPermissions.UserId }; if (!userPermissions.IsEdit) { return searchPermission switch { 1 => query.Where(m => SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, userIdArray))), 2 => query.Where(m => SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, userIdArray)) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedDepartments) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedDepartments, userPermissions.MenuPermission.SearchDepartmentIds))), 3 => query.Where(m => SqlFuncByPg.PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!SqlFuncByPg.PgArrayIsNullOrEmpty(m.RelatedUsers) && SqlFuncByPg.PgArrayOverlapByString(m.RelatedUsers, userIdArray))), 4 => query, // 全部可见，不加任何过滤 _ => query.Where(m => false) // 默认不可见 }; } 拆分减少条件之后就正常了0 回复 -
=_= VIP0
2025/11/17
@fate sta：
using FastTools.ExtensionMethods; using SqlSugar; using System.Collections; namespace Test { public class Program { private static void Main(string[] args) { SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.PostgreSQL, ConnectionString = "PORT=5432;DATABASE=link;HOST=localhost;PASSWORD=odoo;USERID=postgres", IsAutoCloseConnection = true, ConfigureExternalServices = new ConfigureExternalServices() { SqlFuncServices = PostgreSQLFuncExternal() } }, config => { config.Aop.OnError = (exp) => { }; }); UserPermissions userPermissions = new UserPermissions(); userPermissions.UserId = "admin"; userPermissions.IsAdmin = true; userPermissions.Name = "超级用户"; userPermissions.MenuName = "类别档案"; userPermissions.IsEdit = false; userPermissions.MenuPermission = new UserMenuPermission() { SearchPermission = 4 }; var userIdArray = new[] { userPermissions.UserId }; var query = db.Queryable<Category>() .Where(m => (userPermissions.MenuPermission.SearchPermission == 1 && (PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!PgArrayIsNullOrEmpty(m.RelatedUsers) && PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })))) || (userPermissions.MenuPermission.SearchPermission == 2 && (PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!PgArrayIsNullOrEmpty(m.RelatedUsers) && PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })) || (!PgArrayIsNullOrEmpty(m.RelatedDepartments) && PgArrayOverlapByString(m.RelatedDepartments, userPermissions.MenuPermission.SearchDepartmentIds)))) || ((userPermissions.MenuPermission.SearchPermission == 3) && (PgArrayOverlapByString(m.OwnerUsers, userIdArray) || (!PgArrayIsNullOrEmpty(m.RelatedUsers) && PgArrayOverlapByString(m.RelatedUsers, new[] { userPermissions.UserId })))) || userPermissions.MenuPermission.SearchPermission == 4) .Select(m => new CategoryResponseModel(), true) .MergeTable(); query = query.Where(m => ((((((false || m.CategoryType.Contains("行政")) || m.Name.Contains("行政")) || m.ParentId.Contains("行政")) || m.Id.Contains("行政")) || m.CreateUser.Contains("行政")) || m.UpdateUser.Contains("行政"))); int total = 0; var table = query.ToPageList(1, 20, ref total); } public static List<SqlFuncExternal> PostgreSQLFuncExternal() { List<SqlFuncExternal> sqlFuncExternals = new List<SqlFuncExternal>(); //公司、部门、协作用户、创建人 //如果公司不是null，并且 sqlFuncExternals.Add(new SqlFuncExternal() { UniqueMethodName = "PgArrayOverlapByString", MethodValue = (expInfo, dbType, expContext) => { if (dbType == DbType.PostgreSQL) { var sourceArray = expInfo.Args[1]?.MemberValue as IEnumerable; if (sourceArray == null) { return " (1=2) "; } List<object> sourceValues = new List<object>(); foreach (var item in sourceArray) { if (item == null) { throw new Exception("传参必须为字符串"); } if (item is not string) { throw new Exception("传参必须为字符串"); } sourceValues.Add(item); } var value = expInfo.Args[0].MemberName; string? inValueString = null; if (sourceValues != null && sourceValues.Count > 0) { inValueString = string.Join(",", sourceValues.Select(m => $"'{m}'")); } if (inValueString.IsNullOrEmpty()) { return " (1=2) "; } else { return string.Format(" ( {0} && ARRAY[{1}] ) ", value, inValueString); } } else { throw new Exception("未实现"); } } }); sqlFuncExternals.Add(new SqlFuncExternal() { UniqueMethodName = "PgArrayIsNullOrEmpty", MethodValue = (expInfo, dbType, expContext) => { if (dbType == DbType.PostgreSQL) { string queryCondition = $" ( {expInfo.Args[0].MemberName} IS NULL OR {expInfo.Args[0].MemberName} = " + "'{}' ) "; return queryCondition; } else { throw new Exception("未实现"); } } }); return sqlFuncExternals; } public static bool PgArrayOverlapByString(object? pgArray, object? sourceArray) { //这里不能写任何实现代码，需要在上面的配置中实现 throw new NotSupportedException("Can only be used in expressions"); } public static bool PgArrayIsNullOrEmpty(object? pgArray) { throw new NotSupportedException("Can only be used in expressions"); } [SugarTable("link_category", "类别档案")] public class Category { /// <summary> /// 唯一编号 /// </summary> [SugarColumn(ColumnDescription = "唯一编号", Length = 50, IsPrimaryKey = true, IsNullable = false)] public string Id { get; set; } = string.Empty; /// <summary> /// 类别类型（涉及到的字段类型） /// </summary> [SugarColumn(ColumnDescription = "类别类型", Length = 50, IsPrimaryKey = true, IsNullable = false)] public string CategoryType { get; set; } = string.Empty; /// <summary> /// 类别名称 /// </summary> [SugarColumn(ColumnDescription = "类别名称", Length = 200, IsPrimaryKey = true, IsNullable = false)] public string Name { get; set; } = string.Empty; /// <summary> /// 上级部门编号（空字符串表示无上级） /// </summary> [SugarColumn(ColumnDescription = "上级部门编号", Length = 50, IsNullable = false)] public string ParentId { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> [SugarColumn(ColumnDescription = "创建人", Length = 50, IsNullable = false)] public string CreateUser { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> [SugarColumn(ColumnDescription = "创建时间", IsNullable = false)] public DateTime CreateTime { get; set; } = DateTime.Now; /// <summary> /// 更新人 /// </summary> [SugarColumn(ColumnDescription = "更新人", Length = 50, IsNullable = true)] public string UpdateUser { get; set; } = string.Empty; /// <summary> /// 更新时间 /// </summary> [SugarColumn(ColumnDescription = "更新时间", IsNullable = true)] public DateTime UpdateTime { get; set; } = DateTime.Now; /// <summary> /// 部门关联 /// </summary> [SugarColumn(ColumnDescription = "部门关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedDepartments { get; set; } = Array.Empty<string>(); /// <summary> /// 用户关联 /// </summary> [SugarColumn(ColumnDescription = "用户关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedUsers { get; set; } = Array.Empty<string>(); /// <summary> /// 所有者 /// </summary> [SugarColumn(ColumnDescription = "所有者", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] OwnerUsers { get; set; } = Array.Empty<string>(); /// <summary> /// 版本号 /// </summary> [SqlSugar.SugarColumn(IsEnableUpdateVersionValidation = true)]//标识版本字段 public long Version { get; set; } = 0; } public class CategoryResponseModel { /// <summary> /// 分类类型 /// </summary> public string CategoryType { get; set; } = string.Empty; /// <summary> /// 类别名称 /// </summary> public string Name { get; set; } = string.Empty; /// <summary> /// 上级类别编号（空字符串表示无上级） /// </summary> public string ParentId { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> public string CreateUser { get; set; } = string.Empty; /// <summary> /// 更新人 /// </summary> public string UpdateUser { get; set; } = string.Empty; /// <summary> /// 唯一编号 /// </summary> public string Id { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> public DateTime CreateTime { get; set; } = DateTime.Now; /// <summary> /// 更新时间 /// </summary> public DateTime UpdateTime { get; set; } = DateTime.Now; /// <summary> /// 版本 /// </summary> public long Version { get; set; } = 0; } public class UserPermissions { /// <summary> /// 用户编号 /// </summary> public string UserId { get; set; } = string.Empty; /// <summary> /// 用户姓名 /// </summary> public string Name { get; set; } = string.Empty; /// <summary> /// OpenId /// </summary> public string OpenId { get; set; } = string.Empty; /// <summary> /// 菜单名称 /// </summary> public string MenuName { get; set; } = string.Empty; /// <summary> /// 是否是编辑 /// </summary> public bool IsEdit { get; set; } = false; /// <summary> /// 是否是管理员 /// </summary> public bool IsAdmin { get; set; } = false; /// <summary> /// 用户的所有单据权限 /// </summary> public UserMenuPermission MenuPermission { get; set; } = new UserMenuPermission(); } public class UserMenuPermission { /// <summary> /// 部门编号 /// </summary> public List<string> SearchDepartmentIds { get; set; } = new List<string>(); /// <summary> /// 部门编号 /// </summary> public List<string> EditDepartmentIds { get; set; } = new List<string>(); /// <summary> /// 查询权限 /// </summary> public int SearchPermission { get; set; } = 1; /// <summary> /// 编辑权限 /// </summary> public int EditPermission { get; set; } = 0; } } } 这是完整的复现0 回复 -
=_= VIP0
2025/11/17
@=_=：
0 回复