Filter two lists by one C # property using linq

Question

I have two objects: 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. TransactionRef is in Date|TxnID

I also have a list of 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 TxnDetails based transDetails so that it filters out elements that do not contain TxnDetails from the second list.

This should be the output:

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

I tried this with linq:

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

but it always returns the list as empty. I tried many variations of this query without success. I know that this question was asked before and after searching and testing my solutions, I still can’t get it right.

Can anyone suggest what is wrong with my request?

Note. . I forgot to mention that these lists can contain 1000 entries. Therefore, performance is also important.

Answer 1

It 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 is to store all possible transaction data in a hash setting. Then the search should be close to O (1) (subject to a fair distribution of hash codes) instead of O (n) - bringing the overall complexity of the algorithm from O (n * k) 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 combine two pieces of data in one field? I suggest splitting the TransactionRef field in your Card class into two fields: TransactionDate and TransactionID , to avoid processing strings in requests.

Answer 3

How about this?

 var results = cardDetails.Where( card => !transDetails.Any( trans => card.TransactionRef.EndsWith("|" + trans.TxnDetails))); 

Full demo:

 using System; using System.Linq; namespace Demo { class Card { public string CardID; public string TransactionRef; } class Transaction { public string TxnID; public string TxnDetails; } internal class Program { private static void Main() { var cardDetails = new[] { new Card {CardID = "1", TransactionRef = "20150824|Guid1"}, new Card {CardID = "2", TransactionRef = "20150824|Guid2"}, new Card {CardID = "3", TransactionRef = "20150824|Guid3"} }; var transDetails = new[] { new Transaction {TxnID = "23", TxnDetails = "Guid1"}, new Transaction {TxnID = "24", TxnDetails = "Guid2"} }; var results = cardDetails.Where(card => !transDetails.Any(trans => card.TransactionRef.EndsWith("|" + trans.TxnDetails))); foreach (var item in results) Console.WriteLine(item.CardID + ": " + item.TransactionRef); } } } 

Answer 4

it's just a problem with brackets, == false should appear after )) not the first closing one.

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

The reason is with your actual code, you are just doing the opposite of what you want!

you can also do

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

or any suggested improvements, but your code is basically really close to the correct one;)

Answer 5

If performance is important, I suggest that you first give the Card class a property that returns the part after the '|' character. Depending on how often you want to make this request compared to how often you create a Map, you may even need to allow the constructor to split the Ref transaction into the part before the '|' character and the part after "|".

Whatever method you choose is not important for the request. Suppose the Card class has the property:

 string Guid {get {return ...;} 

I understand that you need a sequence of all Cards from the cardDetails sequence that do not have a Guid, which is equal to any of the TxnDetails transactions in the transDetails sequence.

Or, in other words: if you make a sequence of all used commands in TxnDetails, you want all the cards in CardDetails to have a pointer that is not in the sequence of all used commands.

You can use Any () for this, but that will mean that you need to look for the transDetails sequence for each map you want to check.

Whenever you need to check if a particular element is in a sequence or not, it is better to convert the sequence once to a dictionary or HashSet. Whatever you create depends on whether you only need a key or an element with a key. Create a dictionary / hashset only once and very quickly search for an item using the key.

In our case, we only need a sequence with the guides used, it does not matter in which transaction it is used.

 var usedGuids = transDetails.Select(transDetail => transDetail.TxnDetails).Distinct(); var hashedGuids = new HashSet(usedGuids); 

(I made two statements to make it easier to understand what has been done)

Now that I have a GUID, I can check very quickly if it is used or not:

 bool guidIsUsed = usedGuids.Contains(myGuid); 

So, your sequence of cards in cardDetails with a GUID that is not in transDetails is:

 var hashedGuids = new HashSet(transDetails.Select(transDetail => transDetail.TxnDetails).Distinct()); var requestedCards = cardDetails.Where(card => !hashedGuids.Contains(card.Guid)); 

Answer 6

Using method binding syntax for LINQ:

 List<Card> result = cardDetails.Where( card => !transDetails.Exists( tran => tran.TxnDetails == card.TransactionRef.Split('|')[1] )).ToList(); 

What is wrong with your request?

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

Here is what you wrote:

Find all the cards that satisfy this condition: Is there a Transaction in my transaction list in which this particular transaction has TxnDetails that cannot be found in the TxnDetails of this particular Card?

I see the problem here:

If any transaction has a different TxnId than the card (the probability is quite high), return this card.

So basically you should get all the cards from your request if your transaction list has at least 2 different transaction identifiers.