pgsql使用select<>之后后排序(OrderByPropertyName)时命名不正确问题 返回

SqlSugar 处理完成
11 113
该叫什么 mr_chi 发布于2周前
悬赏:0 飞吻

# 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

  • .Select((u, rou, o) => new UserOutput(){

         改成

        .Select((u, rou, o) => new {


    然后


    .ToPageAsync(query.PageIndex, query.PageSize);

    改成

    .Select<UserOutput>.ToPageAsync(query.PageIndex, query.PageSize);

    0 回复
  • 或者


    是你转下划线的时候把DTO也转换了,转下划线要排除DTO

    0 回复
  • 喔喔 就是第一个select 先试用匿名对象接收,那我匿名对象里是不是要手动写一遍dto中要接收的属性?

    0 回复
  • @fate sta:转下划线时包含dto的设置怎么写呀,我没找到对应的文档

    0 回复
  • @mr_chi:那你下划线哪来的,默认是和实体一样的

    0 回复
  • EntityService搜索这个事件一般写在这儿

    0 回复
  • 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 回复
  • 我是根据https://www.donet5.com/home/Doc?typeId=1221

    这个文档写的

    0 回复
  •         p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);


    这行要处理一下 ,加上逻辑 if 是DTO不进这个逻辑

    0 回复
  • image.png

    0 回复
  • 哦哦 那我知道了,我试试

    0 回复