efcore: Latest builds incompatible with some PostgreSQL array scenarios

@thepra yes, the important point is that this is the same behavior as in 2.x, but the inefficiency was being hidden by client evaluation - it is now much easier to understand what’s going on.

If I’m reading your code correctly, allUsersIds is an array parameter. You can’t join a table with an array, but it seems like the main point of the query is to filter out rows whose ID isn’t in the array. If that’s the case, the query can simply be rewritten as follows:

_userService.AppUserPicturesStatic.Where(a => allUsersIds.Contains(a.AppUserId)).ToList()

Unfortunately Contains on an array currently doesn’t work (that’s what this issue is about), but this is planned to be fixed for 3.1.0.

Another more extreme option is to use the unnest PostgreSQL function to expand the array to a table-like set of rows, and join on that:

CREATE TABLE numbers_table (num INT, name TEXT);
INSERT INTO numbers_table (num, name) VALUES (1, 'name1'), (2, 'name2'), (3, 'name3');
SELECT num, name FROM numbers_table JOIN (SELECT unnest(ARRAY[1, 3]) AS arr_num) AS arr_table ON arr_num = num;

EF Core will not generate this kind of advanced, PostgreSQL-specific SQL, and most probably never will. However, you can use raw SQL to write it yourself and have EF materialize the results back to entity instances.

好大一个坑，好惨

Still, moving to 3.0 have broken many of such similar queries.

This is true and we’re aware of the one-time porting pain that disabling client evaluation may cause. We do believe it’s the better way for EF Core to function, as it makes perf issues much more visible and improves compatibility across versions.

So right now the correct way left to use the .Join() method is to use the virtual properties in the model to generate the query with those entities in mind, am I right?

You should be able to use Join, but you must use it on entity types rather than on array parameters as you tried above.

@thepra - join with client side array is not supported since that join has to be client eval. It has no correlation with PostgreSQL arrays.

Yes. We have many queries like

query.Where(e => e.SiteIds.Contains(site.Id));

and they are failing with

Processing of the LINQ expression 'AsQueryable<Guid>(NavigationTreeExpression
    Value: EntityReferencePost
    Expression: (Unhandled parameter: p).SiteIds)' 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.

Well, it prevents translation of several array operations, including inside JSON documents (the new feature). Specifically, this prevents translating Any and Contains.

It would definitely be a shame to not have this - but I guess it’s not completely critical if this goes into 3.1 instead?