Filter a list based on another list condition

Let's say you have a class like below:

class CustomerInformation
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

As you said, you have two lists, let's say you have two lists like below:

List<CustomerInformation> dbList = new List<CustomerInformation>
        {
            new CustomerInformation{Id=1, FirstName="Raju",   LastName="Ahmed"},
            new CustomerInformation{Id=2, FirstName="Tahira", LastName="Biswas"},
            new CustomerInformation{Id=3, FirstName="Shohag", LastName="Mia"},
            new CustomerInformation{Id=4, FirstName="Saiful", LastName="Islam"}
        };

        List<CustomerInformation> csutomerList = new List<CustomerInformation>
        {
            new CustomerInformation{Id=1, FirstName="Raju",   LastName="Ahmed"},
            new CustomerInformation{Id=2, FirstName="Tahira", LastName="Biswas"},
            new CustomerInformation{Id=3, FirstName="Shohag", LastName="Mia"},
            new CustomerInformation{Id=4, FirstName="Saiful", LastName="Islam"},
            new CustomerInformation{Id=5, FirstName="Anny", LastName="Bishwas"},
            new CustomerInformation{Id=6, FirstName="Kabita", LastName="Roy"},
            new CustomerInformation{Id=7, FirstName="Zahidul", LastName="Emon"}
        };

Now you want to get those DB list that are not present in the customer list with somespecific condition, so just try this:

 var newList = csutomerList.Where(cusItem => !dbList.Any(dbItem => cusItem.Id == dbItem.Id && cusItem.FirstName == dbItem.FirstName && cusItem.LastName == dbItem.LastName));

it will first find out all the data that are present in both lists and then simply deduct them.
Sample Output:

Full code here:

    static void Main(string[] args)
    {
        AvailableData();
        Console.ReadKey();
    }

    public static void AvailableData()
    {
        // Create two lists.
        List<CustomerInformation> dbList = new List<CustomerInformation>
        {
            new CustomerInformation{Id=1, FirstName="Raju",   LastName="Ahmed"},
            new CustomerInformation{Id=2, FirstName="Tahira", LastName="Biswas"},
            new CustomerInformation{Id=3, FirstName="Shohag", LastName="Mia"},
            new CustomerInformation{Id=4, FirstName="Saiful", LastName="Islam"}
        };

        List<CustomerInformation> csutomerList = new List<CustomerInformation>
        {
            new CustomerInformation{Id=1, FirstName="Raju",   LastName="Ahmed"},
            new CustomerInformation{Id=2, FirstName="Tahira", LastName="Biswas"},
            new CustomerInformation{Id=3, FirstName="Shohag", LastName="Mia"},
            new CustomerInformation{Id=4, FirstName="Saiful", LastName="Islam"},
            new CustomerInformation{Id=5, FirstName="Anny", LastName="Bishwas"},
            new CustomerInformation{Id=6, FirstName="Kabita", LastName="Roy"},
            new CustomerInformation{Id=7, FirstName="Zahidul", LastName="Emon"}
        };


        var newList = csutomerList.Where(cusItem => !dbList.Any(dbItem => cusItem.Id == dbItem.Id && cusItem.FirstName == dbItem.FirstName && cusItem.LastName == dbItem.LastName)); 

       foreach (var cust in newList)
        {
            Console.WriteLine("Customer Id :{0} | Customer Name: {1} | Last Name: {2} ",cust.Id,cust.FirstName,cust.LastName);
        }
        Console.ReadKey();

    }
}

class CustomerInformation
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Preliminary

This answer assumes you are using an ORM such as EF or EF Core. The code is not tested, it is only here to give you the right direction to solve your problem. Some open questions will need to be answered to reach your final solution.

Also your question seems contradictory sometimes: you are mentioning client lists, while the function you show returns a List<PrePaymentBalanceQuery>. I assume here we are speaking about client lists, and that I could leave aside some unclear points. Anyway, if I'm off please let me know and I'll either edit this answer or remove it.

Analysing the existing code and the question

Since the code is not doing what you want, let's first understand what it does.

You want to filter your in-memory Client list based on some data stored in your database.

clientList.ForEach(y =>
{
    result = dbList.Where(x =>
      (y.BankCode != x.BankCode) ||
      (y.Agency != x.Agency) ||
      (y.AccountNumber != x.AccountNumber)).ToList();
});

return result;

This code is not filtering a Client list.

What it does is, for each Client:

• Calculate something and store that something into a variable called result.

So on each successive iteration, the content in the variable result is replaced by new content.

In the end, the variable result just contains the calculation made for the last client, which is not what you are trying to achieve.

Conclusion: you shouldn't use ForEach here. ForEach is not a filtering operator. Linq has a filtering operator: Where, which should be used here.

Side note: ForEach is not a Linq method extension. It is a method of the List class. It might seem surprising at first, but it is for a good reason. ForEach is not pure (in the functional programming sense), it will introduce side effects, so it is against the general principles of Linq. Purity is not enforced in Linq, but is generally desirable.

How to try to solve this

Here is a first version filtering the clientList:

var FilteredClientList = clientList.Where(c => !dbList
      .Any(dbc =>
          (c.BankCode == dbc.BankCode)
          && (c.Agency == dbc.Agency)
          && (c.AccountNumber == dbc.AccountNumber))
  );

return FilteredClientList;

What does it do?

It builds a new client list by keeping clients from clientList only if there is no such client in the database.

An in-memory client is considered to be in the database if there exists a database client that has the same Bankcode, the same Agency, and the same AccountNumber.

However, there is a problem. While this works functionally, it makes potentially a lot of calls to your database: one for each client.

To understand this, you have to be aware of the difference between Linq to Objects and Linq to Entities.

See linq to entities vs linq to objects - are they the same?

How to fix this? it depends on your particular situation. If your client list is small, you could keep it that way, and be willing to pay for a SQL query for each of your clients.

If your database list is small, you could simply get it in memory first, and do all the work in memory.

var ClientsFromDb = dbList.
    .Select(c => new 
    {
        c.BankCode,
        c.Agency,
        c.AccountNumber
    })
    .ToList()
    
var FilteredClientList = clientList.Where(c => !ClientsFromDb
      .Any(cc =>
          (c.BankCode == cc.BankCode)
          && (c.Agency == cc.Agency)
          && (c.AccountNumber == cc.AccountNumber))
  );

This might also be improved by the use of a Join operator.

But what if your database client list is too big?

You could first retrieve all the matching clients in your database, in one database query, and then use that in-memory list to filter your actual list.

See Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator for pointers.

A more LINQ approach would be like. It's tested from my side.

 result =
        (from cl in clientList 
         where !dbList.Any(x => x.AccountNumber == cl.AccountNumber && x.BankCode == cl.BankCode && x.Agency == cl.Agency)
         select cl).ToList();