LINQ - Get records from one table, with second table multi record matching

Question

I am working on a system where I am given a SQL database where there are no relationships (please dont get me started on this).

A table that I have is bank accounts, id, sort code, account number, name. A second table that I have is a payments table, this has 6 fields, for account number and sort code, but I only need to match on one pair (sort code and account number)

So, I have a query that gets all the bank accounts like this

var bankAccounts =
            _databaseContext.BankAccounts
                .Where(accounts => model.BankAccountIds
                    .Any(x => x == accounts.Id))
                .ToList();

I am building a query and

_databaseContext.Payments.Where(x => bankAccounts.Any(b => b.AccountNumber == x.AccountNumber) 
                                  && bankAccounts.Any(b => b.SortCode == x.SortCode));

However, when I run this I get the error

ystem.InvalidOperationException: The LINQ expression 'DbSet() .Where(p => bankAccounts_0 .Any(b => b.AccountNumber == p.AccountNumber) && bankAccounts_0 .Any(b => b.SortCode == p.SortCode))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

So, what I need is to be able to write the query in such a way it gets me all the payments that are made where the sort code and account number match in the payments table to the bank accounts table

And I do realise that if we had the relationship between the tables this would be much simpler, however as contractor, I have little clout as to how they architect things

-- Edit

In Datacontext in OnModelCreating I was also doing this, but when seeding the data this dies - just building now to get the exception it throws

 modelBuilder.Entity<BankAccount>()
            .HasMany(payment => payment.Payments)
            .WithOne(bankAccount => bankAccount.BankAccountDetails)
            .HasPrincipalKey(x => new {x.AccountNumber, x.SortCode});

Then when running the code I get this

System.InvalidOperationException: Unable to track an entity of type 'BankAccount' because alternate key property 'AccountNumber' is null. If the alternate key is not used in a relationship, then consider using a unique index instead. Unique indexes may contain nulls, while alternate keys may not.

Answer 1

Realistically, you're looking for an SQL like:

SELECT *
FROM Payments p
WHERE 
  EXISTS(
    SELECT null 
    FROM BankAccounts ba
    WHERE  
      ba.BankAccountId IN (some,list,of,guids,from,your,model) AND 
      ba.AccountNumber = p.AccountNumber AND
      ba.SortCode = p.SortCode
  )

Your having downloaded the bank acounts (ToList()ed them) first actually actively cripples EF's ability to do this; it can't see a way to create the coordinated subquery any more, as it's no longer a database-based collection but a collection from the client side. As far as I know, EF supports no notion of building a value set from complex client-provided data and joining to it.

If you keep your "these certain bank accounts" operation as a database-based enumerable your EF might be able to translate it to something like the above :

_databaseContext.Payments.Where(pay => 
    _databaseContext.BankAccounts.Where(
      ba => model.BankAccountIds.Any(mba => mba == ba.Id)
    ).Any(ba => 
        ba.AccountNumber == pay.AccountNumber &&
        ba.SortCode == pay.SortCode
    )
);

The .Any(mba => mba == ba.Id) will translate to IN so long as it's truly a simple prop; you could also use model.BankAccountIds.Contains(ba.Id) but in either case the model literally needs a list of bank account guids for this restriction to translate. The .Any(ba => can translate to a coordinated EXISTS.

Even if it does work, I'd say you have some data modelling issues to work out - having to write a full client app like this is a headache..

---

You noted that "it doesn't work", but in my tests (which are as close as I can get them to the scenario you posted) there wasn't any problem at all:

On the left you can see my tables: BankAccounts and Payments. They both have AccountNumber and SortCode which are never the PK, they have no FKs, the payments Ac/Sort may be null. In the middle is the query I recommended you, with the only fudge above it being me making something like what I assume your model is: a root object that holds a property called BankAccountIds that is a collection of guids. On the right, you can see the generated query; it's come out fairly identically to the SQL posted initially. I use EF Core 5

Examine what is different between what I've set up, and what you have. Post your models. Post the exact query that doesn't work

Answer 2

what you try to do is equivalent to ID in (1,2,3,6,..),. To get that the code must useContains, not Any.

There's no way to convert that List.Any(x.id) to SQL.

• First, there are no lists or arrays in T-SQL, so EF Core couldn't send that array to the server.
• Second, bankAccounts contains complex objects, not values. EF Core would have to generate a table type with all relevant fields and send it to the server for use in a subquery.

In T-SQL we'd write:

Select * 
From Payments
Where AccountNumber in (....) AND SortCode in (...)

To do the same in LINQ we need Contains. To use it the list of values should contain individual simple values:

var accNumbers=bankAccounts.Select(b => b.AccountNumber).ToList();
var sortCodes=bankAccounts.Select(b => b.SortCode).ToList();

var payments = _databaseContext.Payments.Where(x =>
        accNumbers.Contains(x.AccountNumber) 
        && sortCodes.Contains(x.SortCode));

Answer 3

New version, after clarificaiton in comments: I think you could do it as one query instead of materialize the bankAccounts -list first, maybe try some like this:

_databaseContext.Payments
    .Where(p=>_databaseContext.BankAccounts.Where(accounts => model.BankAccountIds.Contains(accounts.Id) && accounts.AccountNumber==p.AccountNumber  && accounts.SortCode==p.SortCode ))

---

I think the problem is that LINQ cannot translate the list of bankAccounts to SQL, try to make a list of the AccountNumbers and SortCode as List<string> and do something like this:

var bankAccounts =
            _databaseContext.BankAccounts
                .Where(accounts => model.BankAccountIds.Contains(accounts.Id))
                .ToList();

var accountNumbers=bankAccounts.Select(x=>x.AccountNumber).ToList();
var sortCodes=bankAccounts.Select(x=>x.SortCode).ToList();

_databaseContext.Payments.Where(x => accountNumbers.Contains(x.AccountNumber) && sortCodes.Contains(x.SortCode));