C#LeftJoin条件为截取参数

private SqlBuilderCore<Reel_SolderPasteGlueEntity> GetExpression(Reel_SolderPasteGlueQueryInput input)
        {
            var condition = this.GetEntityExpression(input);

            return SqlBuilder
                    .Select<Reel_SolderPasteGlueEntity, ReelEntity, PartMaster_SolderPasteGlueEntity,WorkOrderEntity,ModelEntity>((a, b, c,d,e) =>
                        new
                        {
                            a,
                            b.DateCode,
                            b.Expdate,
                            b.QTY,
                            b.OQTY,
                            RemainExpDate = " ROUND(TO_NUMBER(TRUNC(b.EXPDATE) -TRUNC(SYSDATE)))*24",
                            b.LotCode,
                            c.DurationOfStanding,
                            StandingEndTime = " a.STANDINGSTARTIME + c.DURATIONOFSTANDING/24/60",
                            AuditModelName = e.ModelName,
                            AuditWorkOrder = d.WorkOrder
                            
                        }, _repository.DatabaseType, isEnableFormat: false)
                    .LeftJoin<ReelEntity>((a, b) => a.Id == b.Id)
                    .LeftJoin<PartMaster_SolderPasteGlueEntity>((a, c) => a.Pn == c.Pn)
                    .LeftJoin<WorkOrderEntity>((a,d)=>  "REGEXP_SUBSTR(a.WORKORDER, '^[^,]+', 1, 1) "==d.WorkOrder)
                    .LeftJoin<WorkOrderEntity,ModelEntity>((a,d,e)=>d.ModelId==e.Id)
                    .Where(condition);
        }

我用上述代码,调试生成SQL:

SELECT
    a.*,
    b.DATECODE,
    b.EXPDATE,
    b.QTY,
    b.OQTY,
    ROUND(TO_NUMBER(TRUNC(b.EXPDATE) -TRUNC(SYSDATE)))* 24 AS RemainExpDate,
    b.LOTCODE,
    c.DURATIONOfSTANDING,
    a.STANDINGSTARTIME + c.DURATIONOFSTANDING / 24 / 60 AS StandingEndTime,
    REGEXP_SUBSTR(a.WORKORDER, '^[^,]+', 1, 1) AS AuditWorkOrder,
    e.MODELNAME AS AuditModelName
FROM
    MC_REEL_SOLDERPASTEGLUE a
LEFT JOIN MC_REEL b ON
    a.ID = b.ID
LEFT JOIN MC_PARTMASTER_SOLDERPASTEGLUE c ON
    a.PN = c.PN
LEFT JOIN MC_WORKORDER d ON
    :p__1 = d.WORKORDER
LEFT JOIN MC_MODEL e ON
    d.MODELID = e.ID
WHERE
    a.ID = :p__2

数据库里可以查到数据,代码里没有传到WorkorderEntity 数据,因此没有获取到ModelName,我知道问题出在这条代码:

 .LeftJoin<WorkOrderEntity>((a,d)=>  "REGEXP_SUBSTR(a.WORKORDER, '^[^,]+', 1, 1) "==d.WorkOrder)

但是没有找到修改的办法,用过ChatGpt,但未解决问题~

确保你构造出来的sql语句和你上面贴的是一致的，可以调试的时候打印一下

private SqlBuilderCore<Reel_SolderPasteGlueEntity> GetExpression(Reel_SolderPasteGlueQueryInput input)
{
    var condition = this.GetEntityExpression(input);
 
    return SqlBuilder
        .Select<Reel_SolderPasteGlueEntity, ReelEntity, PartMaster_SolderPasteGlueEntity, WorkOrderEntity, ModelEntity>((a, b, c, d, e) =>
            new
            {
                a,
                b.DateCode,
                b.Expdate,
                b.QTY,
                b.OQTY,
                RemainExpDate = "ROUND(TO_NUMBER(TRUNC(b.EXPDATE) - TRUNC(SYSDATE))) * 24",
                b.LotCode,
                c.DurationOfStanding,
                StandingEndTime = "a.STANDINGSTARTIME + c.DURATIONOFSTANDING / 24 / 60",
                AuditModelName = e.ModelName,
                AuditWorkOrder = d.WorkOrder
            }, _repository.DatabaseType, isEnableFormat: false)
        .LeftJoin<ReelEntity>((a, b) => a.Id == b.Id)
        .LeftJoin<PartMaster_SolderPasteGlueEntity>((a, c) => a.Pn == c.Pn)
        .LeftJoin<WorkOrderEntity>((a, d) => a.WORKORDER.Substring(0, a.WORKORDER.IndexOf(",")) == d.WorkOrder)
        .LeftJoin<WorkOrderEntity, ModelEntity>((a, d, e) => d.ModelId == e.Id)
        .Where(condition);
}