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

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.

CodePudding user response：

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));

CodePudding user response：

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));