My join .NetCore 3.1 throws an exception about NavigationExpandingExpressionVisitor, what is that?

Question

I have a .NetCore 3.1 project. I know that there are breaking changes from EF Core 2 to 3 but searching for the solution to this is leading me places that make no sense.

The following works in .NetCore 2.2.

I have a list of user-names that is generated from other queries. I now want to find those user-names in our personnel database with the goal of returning the associated email address for each user-name. A person may elect to use a company email address or supply a different address. If the person.EmailAddress field is empty then the address I need is the username with the company domain appended.

private static List<string> GetEmailAddrsFromBp(PersonnelContext personnelContext, IEnumerable<string> userNames) {
    try {

        var personEmail = (
            from person in personnelContext.Persons
            join userName in userNames
                on person.userName.Trim().ToLower() equals userName.Trim().ToLower()
            where person.ActualEndDate == null
            select person.EmailAddress.Trim().Equals("")
                ? person.userName.Trim().ToLower() + "@myCompany.com"
                : person.EmailAddress.Trim().ToLower()
        ).Distinct().OrderBy(a => a).ToList();

        return personEmail;
    } catch (Exception e) {
        throw new Exception("GetEmailAddrsFromBp: " + e.Message);
    }
}

in 3.1 I get the exception:

Processing of the LINQ expression 'DbSet<Persons>
    .Join(
        outer: __p_0, 
        inner: person => person.userName.Trim().ToLower(), 
        outerKeySelector: userName => userName.Trim().ToLower(), 
        innerKeySelector: (person, userName) => new { 
            person = person, 
            userName = userName
         })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

I do not understand this error. Going to the suggested Microsoft site is not helpful. Other googling has proven unhelpful. What is going on? How do you do "simple" joins now?

Answer 1

I do not understand this error.

The error message of course is not user friendly. The only relevant part is

This may indicate either a bug or a limitation in EF Core.

which can safely be read as "This is either a bug or a limitation in EF Core."

What is going on? How do you do "simple" joins now?

You can do "simple" joins, but not joins to memory collections. In fact joins to memory collections were never really supported. Just EF Core 1.x / 2.x used the so called client evaluation for the things it cannot translate. But implicit client evaluation has been removed in 3.0, and now you are supposed to either find a translatable construct, or switch explicitly to client evaluation through LINQ to Objects (or System.Linq.Async).

Since specifically for joins switching to client evaluation is not efficient, it's better to find/use a translatable query construct. If you use non-equi or multi-key join, you basically have no option. But for single key equi-join there is a construct which is supported in all EF / EF Core version, and it is Enumerable.Contains which translates to SQL IN (val1, val2, ..., valN).

So the solution for you concrete case would be something like this:

userNames = userNames.Select(userName => userName.Trim().ToLower()).Distinct();

var personEmail = (
    from person in personnelContext.Persons
    where userNames.Contains(person.userName.Trim().ToLower())
    // the rest of the query unchanged...