Filter two lists on one property c# using linq

Question

I have two objects namely Card and Transaction:

Card:
public string CardID {get; set;}
public string TransactionRef {get; set;}

Transaction:
public string TxnID {get; set;}
public string TxnDetails {get; set;}

Note: The TransactionRef is of the format Date|TxnID

I also have a list of the two objects List<Card> cardDetails and List<Transaction> transDetails

cardDetails:
{CardID = '1', TransactionRef = '20150824|Guid1'}
{CardID = '2', TransactionRef = '20150824|Guid2'}
{CardID = '3', TransactionRef = '20150824|Guid3'}

transDetails:
{TxnID = '23', TxnDetails = 'Guid1'}
{TxnID = '24', TxnDetails = 'Guid2'}

I want to filter cardDetails using transDetails based on TxnDetails so that it filters out the items which do not contain the TxnDetails from the 2nd list.

This should be the output:

cardDetails:
 {CardID = '3', TransactionRef = '20150824|Guid3'}

I have tried like this using linq:

  cardDetails = cardDetails.Where(x => transDetails.Any(y => x.TransactionRef.Contains(y.TxnDetails) == false)).ToList();

but it always returns the list as blank. I have tried many variants of this query without success. I know this question has been asked before and after searching for them and trying out their solutions I am still unable to get it right.

Can anyone suggest what is wrong with my query?

Note: One thing I forgot to mention is that these lists can contains 1000s of records. So performance is also important.

Answer 1

This should do it

var cards = 
    from card in cardDetails
    let txnDetails = GetTxnDetails(card)
    where ! transDetails.Any(t => t.TxnDetails == txnDetails)
    select card;


static string GetTxnDetails(Card card)
{
    return card.TransactionRef.Split('|')[1];
}

Fiddle: https://dotnetfiddle.net/b9ylFe

---

One way to optimize this a bit would be to store all the possible transaction details in a hash set upfront. The lookup should then be pretty close to O(1) (assuming a fair hashcode distributation) instead of O(n) - bringing the overall complexity of the algorithm from O(n * k) down to O(n + k).

var allTxnDetails = new HashSet<string>(transDetails.Select(t => t.TxnDetails));

var cards = 
    from card in cardDetails
    let txnDetails = GetTxnDetails(card)
    where ! allTxnDetails.Contains(txnDetails)
    select card;

Fiddle: https://dotnetfiddle.net/hTYCbj

Answer 2

This query should do the trick:

// Get all card details whose transactionrefs don't contain txndetails from the second list
cardDetails.Where(cd => transDetails.All(ts => !cd.TransactionRef.EndsWith(ts.TxnDetails)))
    .ToList();

But is there any specific reason why you are combining two pieces of data in one field? I suggest breaking the TransactionRef field in your Card class into two fields: TransactionDate and TransactionID to avoid string manipulation in queries.