pgsql使用select<>之后后排序(OrderByPropertyName)时命名不正确问题 返回
mr_chi 发布于2024/7/2
# sqlsugar配置， dbType: PostgreSQL
var config = App.GetConfig<ConnectionConfig>("ConnectionConfigs");
config.ConfigureExternalServices = new()
{
// 处理列名
EntityService = (x, p) =>
{
p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);
if (p.IsPrimarykey == false && new NullabilityInfoContext().Create(x).WriteState is NullabilityState.Nullable)
{
p.IsNullable = true;
}
},
// 处理表名
EntityNameService = (x, p) =>
{
p.DbTableName = UtilMethods.ToUnderLine(p.DbTableName);
}
};
# 查询语句
await _userRep
.AsQueryable()
.LeftJoin<RelOrgUser>((u, rou) => u.Id == rou.UserId)
.LeftJoin<SysOrg>((u, rou, o) => rou.OrgId == o.Id)
.Where(u => u.IsAdmin == false)
.WhereIF(!query.NickName.IsNullOrEmpty(), u => u.NickName.Contains(query.NickName))
.WhereIF(query.Sex.HasValue, u => u.Sex == query.Sex)
.WhereIF(query.IsEnable.HasValue, u => u.IsEnable == query.IsEnable)
.WhereIF(query.OrgId.HasValue, (u, rou, o) => rou.OrgId == query.OrgId)
.Select((u, rou, o) => new UserOutput()
{
Name = u.Name,
RoleIds = SqlFunc.Subqueryable<RelRoleUser>()
.LeftJoin<SysRole>((iru, r) => iru.RoleId == r.Id &&
r.IsEnable == true &&
r.IsDelete == false)
.Where((iru, r) => iru.UserId == u.Id)
.ToList(iru => iru.RoleId),
RoleName = SqlFunc.Subqueryable<RelRoleUser>()
.LeftJoin<SysRole>((iru, r) => iru.RoleId == r.Id &&
r.IsEnable == true &&
r.IsDelete == false)
.Where((iru, r) => iru.UserId == u.Id)
.SelectStringJoin((iru, r) => r.Name, ","),
OrgName = o.Name,
}, true)
.MergeTable()
.OrderByPropertyName(query.OrderField, orderByType: query.IsAscending ? OrderByType.Asc : OrderByType.Desc)
.ToPageAsync(query.PageIndex, query.PageSize);
# 生成sql
SELECT
*
FROM
(
SELECT
"u"."name" AS "name",
(
SELECT
string_agg ( ( "r"."name" ) :: TEXT, ',' )
FROM
"rel_role_user" "iru"
LEFT JOIN "sys_role" "r" ON (
( ( "iru"."role_id" = "r"."id" ) AND ( "r"."is_enable" = TRUE ) )
AND ( "r"."is_delete" = FALSE )
)
WHERE
( "iru"."user_id" = "u"."id" )
) AS "rolename",
"o"."name" AS "orgname",
"u"."id" AS app_ext_col_0,
"u"."account" AS "account",
"u"."nick_name" AS "nickname",
"u"."sex" AS "sex",
"u"."phone" AS "phone",
"u"."email" AS "email",
"u"."self_introduction" AS "selfintroduction",
"u"."avatar" AS "avatar",
"u"."error_num" AS "errornum",
"u"."is_enable" AS "isenable",
"u"."create_time" AS "createtime",
"u"."id" AS "id",
"rou"."org_id" AS "orgid"
FROM
"sys_user" "u"
LEFT JOIN "rel_org_user" "rou" ON ( "u"."id" = "rou"."user_id" )
LEFT JOIN "sys_org" "o" ON ( "rou"."org_id" = "o"."id" )
AND ( "o"."is_delete" = FALSE )
AND ( "o"."is_delete" = FALSE )
WHERE
( "u"."is_admin" = FALSE )
AND ( "u"."is_delete" = FALSE )
AND ( "u"."is_delete" = FALSE )
) MergeTable
ORDER BY
"create_time" DESC
LIMIT 10 OFFSET 0
# 问题描述
排序方法中 query.OrderField 赋值为“CreateTime”, Dto实体 UserOutput 的字段接收为“CreateTime”。在生成sql语句时，排序中的“CreateTime”转换为了“create_time”，而Dto中的“CreateTime”转换为了“createtime”最总导致sql语句报错无法查询
热忱回答（11）
-
fate sta VIP0
2024/7/2.Select((u, rou, o) => new UserOutput(){
改成
.Select((u, rou, o) => new {
然后
.ToPageAsync(query.PageIndex, query.PageSize);
改成
.Select<UserOutput>.ToPageAsync(query.PageIndex, query.PageSize);
0 回复 -
fate sta VIP0
2024/7/2
或者
是你转下划线的时候把DTO也转换了，转下划线要排除DTO
0 回复 -
mr_chi VIP0
2024/7/2
喔喔 就是第一个select 先试用匿名对象接收，那我匿名对象里是不是要手动写一遍dto中要接收的属性？
0 回复 -
mr_chi VIP0
2024/7/2
@fate sta：转下划线时包含dto的设置怎么写呀，我没找到对应的文档
0 回复 -
fate sta VIP0
2024/7/2
@mr_chi：那你下划线哪来的，默认是和实体一样的
0 回复 -
fate sta VIP0
2024/7/2
EntityService搜索这个事件一般写在这儿
0 回复 -
mr_chi VIP0
2024/7/2
EntityService = (x, p) =>
{
p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);
if (p.IsPrimarykey == false && new NullabilityInfoContext().Create(x).WriteState is NullabilityState.Nullable)
{
p.IsNullable = true;
}
},
我是这么写的 p.那个属性是dto的我没找到
0 回复 -
mr_chi VIP0
2024/7/2
我是根据https://www.donet5.com/home/Doc?typeId=1221
这个文档写的
0 回复 -
fate sta VIP0
2024/7/2
p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);
这行要处理一下 ，加上逻辑 if 是DTO不进这个逻辑
0 回复 -
fate sta VIP0
2024/7/2
0 回复 -
mr_chi VIP0
2024/7/2
哦哦 那我知道了，我试试
0 回复