LINQ to XML selecting elements similar to SQL IN clause

Question

I have some XML that contains Payment information including details on the Invoices the payments are being applied to. I would like to use LINQ to XML to gather only those payments that are applied to certain invoices (111,222,333) for example. If it were SQL I could use the IN (111,222,333) keyword but I am not sure how to do the same in LINQ.

<Payment>
    <PaymentId>1</PaymentId>
    <Total>50</Total>
    <Invoice>
        <Id>111</Id>
        <Amount>20</Amount>
    </Invoice>
    <Invoice>
        <Id>555</Id>
        <Amount>30</Amount>
    </Invoice>
</Payment>
<Payment>
    <PaymentId>2</PaymentId>
    <Total>20</Total>
    <Invoice>
        <Id>222</Id>
        <Amount>20</Amount>
    </Invoice>
</Payment>
<Payment>
    <PaymentId>3</PaymentId>
    <Total>80</Total>
    <Invoice>
        <Id>888</Id>
        <Amount>80</Amount>
    </Invoice>
</Payment>

LINQ

var result = xml.Select(x => x.Element("Payment"))
               Where(x => x.Element("Id").Value.Contains("111","222","333"))

In this example, I would like to select "PaymentId 1" and "PaymentId 2" since they apply to invoices that have Ids that match 111, 222, or 333.

pfx · Accepted Answer · 2019-03-07T12:38:26.600

You can do a match/intersect against a list of these wellknown Id's.

List<string> ids = new List<string> { "111", "222", "333" };

var result = xml
    .Elements("Payment")
    .Where(p => {
        var invoiceIds = p.Elements("Invoice").Elements("Id").Select(o => o.Value);
        return ids.Intersect(invoiceIds).Any();
    }
);

Full code and NetFiddle.

const string XML = @"
    <root>
        <Payment>
            <PaymentId>1</PaymentId>
            <Total>50</Total>        
            <Invoice>
                <Id>555</Id>
                <Amount>30</Amount>
            </Invoice>
            <Invoice>
                <Id>111</Id>
                <Amount>30</Amount>
            </Invoice>
        </Payment>
        <Payment>
            <PaymentId>2</PaymentId>
            <Total>20</Total>
            <Invoice>
                <Id>222</Id>
                <Amount>20</Amount>
            </Invoice>
        </Payment>
        <Payment>
            <PaymentId>3</PaymentId>
            <Total>80</Total>
            <Invoice>
                <Id>888</Id>
                <Amount>80</Amount>
            </Invoice>
        </Payment>
    </root>
    ";

XElement xml = XElement.Parse(XML);
List<string> ids = new List<string> { "111", "222", "333" };

var result = xml
    .Elements("Payment")
    .Where(p => {
        var invoiceIds = p.Elements("Invoice").Elements("Id").Select(o => o.Value);
        return ids.Intersect(invoiceIds).Any();
        }
    );

foreach (var item in result)
{
    Console.WriteLine("PaymentId: {0}", (string)item.Element("PaymentId"));
}

I see .. so I needed to find the Id element value within the collection of known Ids. I was going about it the reverse way .. and it didn't work! Thanks! — webworm, Mar 06 '19 at 22:23
When I try this I get an `Object reference not set to an instance of an object` error on the `ids` List. — webworm, Mar 06 '19 at 23:12
@webworm Made an improvement, didn't notice that a payment can have multiple invoice-ids. — pfx, Mar 07 '19 at 11:34

LINQ to XML selecting elements similar to SQL IN clause

1 Answers1