Acumatica Projection关联DAC出现重复数据问题求助

我正在创建一个Projection，用于关联FSSODet与FSAppointmentDet表，以在流程界面中使用。目标还原的SQL语句如下：

SELECT FD.[SrvOrdType] ,FD.[RefNbr]
      ,FD.[OrigSrvOrdNbr],FD.[OrigLineNbr]
      ,FD.[UnitCost],FD.[UnitPrice]
      ,FS.[RefNbr] ,FS.[LineNbr]
      ,FS.[LineRef],FS.[UnitCost]
      ,FS.[UnitPrice],FS.[TranDesc]
      ,FS.[SODetID],FS.[SOID]
From [FSAppointmentDet] FD
   JOIN [FSSODet] FS on FD.OrigSrvOrdNbr = FS.RefNbr 
   AND FS.LineNbr = FD.OrigLineNbr
WHERE fs.UnitPrice <> FD.UnitPrice 

编写的Projection代码如下：

[PXProjection(typeof(Select2<FSAppointmentDet,
    InnerJoin<FSSODet,
    On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
       And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>>,
    Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>), Persistent = true)]
[PXCacheName("FixPriceProjection")]
public class PlyFixPriceProjDAC : IBqlTable
{
    #region Selected
    public abstract class selected : PX.Data.BQL.BqlBool.Field<selected> { }
    [PXBool]
    [PXUIField(DisplayName = "Selected")]
    public virtual bool? Selected { get; set; }
    #endregion

    #region Composite Key
    public class PK : PrimaryKeyOf<PlyFixPriceProjDAC>.By<origSrvOrdNbr, origLineNbr>
    {
        public static PlyFixPriceProjDAC Find(PXGraph graph, string origSrvOrdNbr, int? origLineNbr)
            => FindBy(graph, origSrvOrdNbr, origLineNbr);
    }
    #endregion

    #region From the FSAppointmentDet DAC
    #region FDRefNbr
    [PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.refNbr))]
    public virtual string FDRefNbr { get; set; }
    public abstract class fDRefNbr : PX.Data.BQL.BqlString.Field<fDRefNbr> { }
    #endregion

    #region OrigSrvOrdNbr
    [PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.origSrvOrdNbr))]
    public virtual string OrigSrvOrdNbr { get; set; }
    public abstract class origSrvOrdNbr : PX.Data.BQL.BqlString.Field<origSrvOrdNbr> { }
    #endregion

    #region OrigLineNbr
    [PXDBInt(BqlField = typeof(FSAppointmentDet.origLineNbr))]
    public virtual int? OrigLineNbr { get; set; }
    public abstract class origLineNbr : PX.Data.BQL.BqlInt.Field<origLineNbr> { }
    #endregion

    #region FDUnitCost
    [PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitCost))]
    public virtual decimal? FDUnitCost { get; set; }
    public abstract class fDUnitCost : PX.Data.BQL.BqlDecimal.Field<fDUnitCost> { }
    #endregion

    #region FDUnitPrice
    [PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitPrice))]
    public virtual decimal? FDUnitPrice { get; set; }
    public abstract class fDUnitPrice : PX.Data.BQL.BqlDecimal.Field<fDUnitPrice> { }
    #endregion


    #region InventoryID
    public abstract class inventoryID : PX.Data.BQL.BqlInt.Field<inventoryID> { }

    [StockItem(IsKey = true, BqlField = typeof(FSAppointmentDet.inventoryID), Enabled = false)]
    public virtual Int32? InventoryID { get; set; }
    #endregion

    #endregion

    #region From the FSSODet DAC
    #region FSRefNbr
    [PXDBString(20, IsUnicode = true, BqlField = typeof(FSSODet.refNbr))]
    public virtual string FSRefNbr { get; set; }
    public abstract class fSRefNbr : PX.Data.BQL.BqlString.Field<fSRefNbr> { }
    #endregion

    #region FSLineNbr
    [PXDBInt(BqlField = typeof(FSSODet.lineNbr))]
    public virtual int? FSLineNbr { get; set; }
    public abstract class fSLineNbr : PX.Data.BQL.BqlInt.Field<fSLineNbr> { }
    #endregion

    #region FSLineRef
    [PXDBString(30, IsUnicode = true, BqlField = typeof(FSSODet.lineRef))]
    public virtual string FSLineRef { get; set; }
    public abstract class fSLineRef : PX.Data.BQL.BqlString.Field<fSLineRef> { }
    #endregion

    #region FSUnitCost
    [PXDBDecimal(4, BqlField = typeof(FSSODet.unitCost))]
    public virtual decimal? FSUnitCost { get; set; }
    public abstract class fSUnitCost : PX.Data.BQL.BqlDecimal.Field<fSUnitCost> { }
    #endregion

    #region FSUnitPrice
    [PXDBDecimal(4, BqlField = typeof(FSSODet.unitPrice))]
    public virtual decimal? FSUnitPrice { get; set; }
    public abstract class fSUnitPrice : PX.Data.BQL.BqlDecimal.Field<fSUnitPrice> { }
    #endregion

    #region TranDesc
    [PXDBString(255, IsUnicode = true, BqlField = typeof(FSSODet.tranDesc))]
    public virtual string TranDesc { get; set; }
    public abstract class tranDesc : PX.Data.BQL.BqlString.Field<tranDesc> { }
    #endregion
    #endregion
}

运行后查询到1535条记录，数量正确，但所有记录的数据完全相同。通过以下Graph代码验证，确认记录数正确但数据重复：

public PXProcessing<PlyFixPriceProjDAC> Processing;

var Items = Processing.Select();
var totRecords = Items.Count;
foreach (PlyFixPriceProjDAC item in Items)
{
    var so = item.OrigSrvOrdNbr;
}

请问这是什么原因导致的？

核心原因：主键定义冲突 #

你的Projection DAC中存在两处主键定义矛盾：

1. InventoryID字段被标记了IsKey = true，但该字段并非唯一标识Projection记录的字段——多条不同的关联记录可能共享同一个InventoryID。
2. 复合主键类PK指定origSrvOrdNbr和origLineNbr作为主键，但Acumatica缓存会优先使用标记为Key的字段组合来识别唯一记录。

当缓存遇到相同InventoryID的记录时，会认为是同一个实体，后续记录会覆盖前一条的数据，最终所有记录显示为同一条内容。

修复步骤 #

1. 移除InventoryID字段上的IsKey = true属性，该字段不需要作为主键：

[StockItem(BqlField = typeof(FSAppointmentDet.inventoryID), Enabled = false)]
public virtual Int32? InventoryID { get; set; }

2. 确保复合主键与实际业务逻辑匹配：当前PK类使用origSrvOrdNbr和origLineNbr，这两个字段能通过关联逻辑唯一标识Projection中的记录，保留该定义即可。
3. （可选）若需要更明确的主键定义，可在OrigSrvOrdNbr和OrigLineNbr字段上分别添加IsKey = true属性，与复合主键类保持一致。

验证 #

修复后重新发布项目，运行Graph查询代码，每条记录的数据将正确显示，不再出现重复。

内容的提问来源于stack exchange，提问作者MarkJoel60