EF Core The LINQ expression 'DbSet() could not be translated

What am I missing here?

IQueryable<IsActiveCustomerProviderRefDto> search =
    from customerProviderRef in _database.CustomerProviderRef
    join customerTrade in _database.CustomerTrade on customerProviderRef.ExternalRefId equals customerTrade.CustomerProviderRef into ctJoin
    from customerTradeJoin in ctJoin.DefaultIfEmpty()
    select new IsActiveCustomerProviderRefDto
    {
        CustomerProviderRef = customerProviderRef.ExternalRefId
    };

search = search.Where(e => e.CustomerId.Equals(find.customerProviderRef.CustomerId));

return search.FirstOrDefault();

Generates the following error:

Description:The LINQ expression 'DbSet() .Where(c => new IsActiveCustomerProviderRefDto{ CustomerProviderRef = c.ExternalRefId } .CustomerId.Equals("1014706150563885056"))' 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.(Source: Microsoft.EntityFrameworkCore)

I've tried replacing the FirstOrDefault() call with ToList(), etc but makes no difference.

I'm not entirely sure if my query is spot on either, so I'll provide a SQL version of the query I was attempting to achieve (LINQ is not my specialty)...

SELECT *
FROM CustomerProviderRef [R]

LEFT OUTER JOIN [CustomerTrade] [T]
    ON [R].[ExternalRefId] = [T].[CustomerProviderRef]

WHERE
    [T].[Id] IS NULL -- essentially where the left outer join fails.
AND
    [R].[CustomerId] = @CustomerId

Solution

That query looks a bit off, in that you are doing the Where clause after the projection (Select) which I don't believe would compile. The Where clause would expect to be limited to just the values that were selected.

The main issue will be based on what "find" represents in your example. Linking IQueryable queries in EF is possible when EF is happy they can be translated into a single SQL statement, but generally you should avoid it if at all possible. Manually joining DbSets should be reserved for relationships which cannot be represented in the database with actual FK constraints. These are de-normalized relationships, usually hacks to "save space" but result in slow, buggy querying. An example would be something like an Address table with an OwnerType and OwnerId Where OwnerType might be "Customer" or "Business" or several other values, used to define whether the OwnerId should pair to the CustomerId or BusinessId etc. These are slow and buggy because they cannot benefit from FKs or constraints for performance or data integrity.

EF and Linq should not be viewed as a like-for-like replacement for SQL using explicit joins between DbSets. Instead, set up the relationships between entities using Navigation properties with 1-to-many, many-to-one, many-to-many, or 1-to-1 relationships as the database schema structure reflects through FK references. This is what EF is designed to do, to generate SQL to pull back the applicable data from a object model representation of that related data.

based on the query with the relationship (1-to-many between CustomerProviderRef and CustomerTrade on ExternalRefId your Linq query would look more like:

var results = dbContxt.CustomerProviderRef
    .Where(x => x.CustomerId == customerId
       && !x.CustomerTrades.Any())
    .ToList();

This assumes that there are multiple CustomerProviderRef per CustomerID and you want the ones that have no Trades.

If there is only one expected CustomerProviderRef for a given customer you want to load it and determine if it has any trades:

var customerProviderRef = dbContxt.CustomerProviderRef
    .Include(x => x.CustomerTrades)
    .Where(x => x.CustomerId == customerId)
    .Single();

bool hasTrades = customerProviderRef.CustomerTrades.Any();

This eager loads the trades with the customer, which will fetch any trades when we load this customer's data. EF will work out the joins as neded. From there we can check if the customer has any trades, and add Trades to that CustomerTrades collection and call SaveChanges() on the DbContext and EF will create the row and associate the FKs automatically.