Linq Match Sets

Question

I am working on a Linq query that essentially matches Set 1 against Set 2 for matching records.

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 = ""
    }
};

I converted Set 2 to Set 1 Model like:

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();

My Expectation is on the following Pseudo:

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

My working on LinqPad Ver. 7 is below but getting error:

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();

The error that I am getting on LinqPad is:

CS1061 'List<UserQuery.IntraAccountSecurityModel>' does not contain a definition for 'SecurityID' and no accessible extension method 'SecurityID' accepting a first argument of type 'List<UserQuery.IntraAccountSecurityModel>' could be found (press F4 to add an assembly reference or import a namespace)

How can I achieve this?

Here is my Models:

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; }
}

`HeaderComment = c1.SecurityList.SecurityID.FirstOrDefault(),` <-- I think you want `c1.SecurityList.First().SecurityID` - but using `First` or `FirstOrDefault` here would be incorrect without the list being ordered somehow in a prior step, otherwise the first result is arbitrary and won't be a meaningful header record. — Dai, May 04 '23 at 06:43
`o1.SecurityList.Any(f=>f.SecurityID.Contains(c1.SecurityList.SecurityID)` <-- Don't do this because it'll have horrible runtime complexity - why aren't you using a `HashSet` here? — Dai, May 04 '23 at 06:43
I am getting `Cannot implicitly convert type 'bool' to 'System.Collections.Generic.List` — hiFI, May 04 '23 at 07:07

score 3 · Answer 1 · answered May 04 '23 at 10:01

c1.SecurityList is of type List<IntraAccountSecurityModel>. List does not have an .SecurityID, so you will get an error.

I would presume you want

c1.SecurityList.Select(p => p.SecurityID).FirstOrDefault() or c1.SecurityList.FirstOrDefault()?.SecurityID

i.e. pick the securityID from the first IntraAccountSecurityModel, or null.

If the list is from a database the list will be unordered unless otherwise specified, and that can result FirstOrDefault() giving a more or less random item. This might or might not be an issue for you.

A better alternative might be to use string.Join to combine all the values, or do some special handling if there are many values, like outputting "*" or "JKH.N000 (and 42 more)".

Linq Match Sets

1 Answers1