Linq嵌套集合匹配查询报错，如何实现预期的记录匹配？

现有代码与需求 #

Set 1（待匹配的开放交易） #

List<IntraAccountModel> openIntras = new List<IntraAccountModel>()
{
    new IntraAccountModel()
    {
        InAccountID = "JKB1",
        SecurityList = new List<IntraAccountSecurityModel>()
        {
            new IntraAccountSecurityModel()
            {
                SecurityID = "JKH.N000",
                Quantity = 100,
                ExternalReferenceNo="JKH-1234"
            },
            new IntraAccountSecurityModel()
            {
                SecurityID = "HHL.N000",
                Quantity = 100,
                ExternalReferenceNo="HHL-1234"
            }
        }
    },
    new IntraAccountModel()
    {
        InAccountID = "JKB2",
        SecurityList = new List<IntraAccountSecurityModel>()
        {
            new IntraAccountSecurityModel()
            {
                SecurityID = "JKH.N000",
                Quantity = 1000,
                ExternalReferenceNo="JKH-5678"
            }
        }
    }
};

Set 2（已关闭交易） #

List<SecurityInOutValueModel> closedIntras = new List<SecurityInOutValueModel>()
{
    new SecurityInOutValueModel()
    {
        accountNo = "JKB1",
        securityCode = "JKH.N0000",
        quantity = "100",
        externalReferenceNo = "JKH-1234",
        referenceNo = ""
    },
    new SecurityInOutValueModel()
    {
        accountNo = "JKB2",
        securityCode = "JKH.N0000",
        quantity = "1000",
        externalReferenceNo = "JKH-5678",
        referenceNo = ""
    }
};

已完成的模型转换 #

将Set2转换为Set1的模型格式：

var closedTrxns = (from r in closedIntras
    group r by new
    {
        r.accountNo
    } into g
    select new IntraAccountModel()
    {
        InAccountID = g.Key.accountNo,
        SecurityList = g.Select(c => new IntraAccountSecurityModel(){
            SecurityID = c.securityCode,
            Quantity = int.Parse(c.quantity),
            ExternalReferenceNo = c.externalReferenceNo
        }).ToList()
    }).ToList();

预期匹配逻辑（伪SQL） #

需要实现类似以下SQL的内连接逻辑，匹配账户ID、证券ID和数量：

Select
s1.*, s2.SecurityList.SecurityID, s2.SecurityList.Quantity
From Set1 as s1
Inner Join Set2 as s2
on s1.InAccountID = s2.InAccountID
and s1.SecurityList.SecurityID = s2.SecurityList.SecurityID
and s1.SecurityList.Quantity = s2.SecurityList.Quantity

错误代码与报错信息 #

编写的Linq代码出现错误：

var matchedTrxns = openIntras
    .Join(closedTrxns, 
        o1 => o1.InAccountID, 
        c1 => c1.InAccountID,
        (o1, c1) => new IntraAccountModel()
        {
            InAccountID = o1.InAccountID,
            HeaderComment = c1.SecurityList.SecurityID.FirstOrDefault(),
            SecurityList = o1.SecurityList.Any(f=>f.SecurityID.Contains(c1.SecurityList.SecurityID) && f.Quantity == c1.SecurityList.Quantity).ToList()
            
        });
matchedTrxns.Dump();

报错信息：

CS1061 'List<UserQuery.IntraAccountSecurityModel>' 不包含定义为'SecurityID'的成员，也找不到接受类型为'List<UserQuery.IntraAccountSecurityModel>'的第一个参数的可访问扩展方法'SecurityID'（按F4添加程序集引用或导入命名空间）

模型定义 #

public class IntraAccountModel
{
    public int IntraSerialNo { get; set; }
    public DateTime? XsactDate { get; set; }
    public string InAccountID { get; set; }
    public string OutAccountID { get; set; }
    public string SalesCode { get; set; }
    public string HeaderComment { get; set; }
    public DateTime? EntryDate { get; set; }
    public DateTime? ResubmittedDate { get; set; }
    public List<IntraAccountSecurityModel> SecurityList { get; set; }
}

public class IntraAccountSecurityModel
{
    public int IntraSerialNo { get; set; }
    public string SecurityID { get; set; }
    public int Quantity { get; set; }
    public string DetailComment { get; set; }
    public string ExternalReferenceNo { get; set; }
    public DateTime DateLodgedOn { get; set; }
}

public class SecurityInOutValueModel
{
    public string accountNo { get; set; }
    public string referenceNo { get; set; }
    public string externalReferenceNo { get; set; }
    public string status { get; set; }
    public string securityCode { get; set; }
    public string custodianBankName { get; set; }
    public string custodianBankCode { get; set; }
    public string ourSettleAccount { get; set; }
    public string entryBy { get; set; }
    public string updatedBy { get; set; }
    public string actionPendingFor { get; set; }
    public string accountBalanceTypeDisp { get; set; }
    public string quantity { get; set; }
    public string securityInOutDate { get; set; }
}

问题分析与修正方案 #

错误原因 #

1. 集合属性访问错误：c1.SecurityList是List<IntraAccountSecurityModel>集合，直接访问.SecurityID会报错，因为集合本身没有这个属性，需要针对集合中的元素进行操作。
2. 类型转换错误：o1.SecurityList.Any(...)返回的是bool值，不能直接调用.ToList()，应该用Where筛选符合条件的元素后再转集合。

修正后的Linq代码 #

var matchedTrxns = openIntras
    // 先按账户ID匹配Set1和转换后的Set2
    .Join(closedTrxns, 
        open => open.InAccountID, 
        closed => closed.InAccountID,
        (open, closed) => new { Open = open, Closed = closed })
    // 构建结果模型，匹配每个账户下的证券记录
    .Select(x => new IntraAccountModel()
    {
        InAccountID = x.Open.InAccountID,
        // 取第一个匹配的证券ID作为HeaderComment（可按需调整）
        HeaderComment = x.Open.SecurityList
            .Join(x.Closed.SecurityList,
                os => new { SecurityID = os.SecurityID.TrimEnd('0'), os.Quantity }, // 兼容Set2多一个0的情况
                cs => new { SecurityID = cs.SecurityID.TrimEnd('0'), cs.Quantity },
                (os, cs) => os.SecurityID)
            .FirstOrDefault(),
        // 筛选出同时在Open和Closed中匹配的证券记录
        SecurityList = x.Open.SecurityList
            .Join(x.Closed.SecurityList,
                os => new { SecurityID = os.SecurityID.TrimEnd('0'), os.Quantity },
                cs => new { SecurityID = cs.SecurityID.TrimEnd('0'), cs.Quantity },
                (os, cs) => os)
            .ToList()
    });

matchedTrxns.Dump();

关键说明 #

• 证券ID兼容处理：注意到Set1的SecurityID是JKH.N000，而Set2转换后的是JKH.N0000，所以用TrimEnd('0')做兼容匹配，如果实际业务中是精确匹配，可去掉这个处理，直接用os.SecurityID和cs.SecurityID。
• 层级匹配逻辑：先按账户ID关联两个集合，再在每个账户下，通过Join匹配证券ID和数量一致的记录，确保最终的SecurityList只包含完全匹配的证券条目。
• 结果构造：根据需求构造IntraAccountModel，其中HeaderComment可根据实际业务调整取值逻辑。

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