score:2

Accepted answer

Check client and server side evaluation here: https://docs.microsoft.com/en-us/ef/core/querying/client-eval

EF Core supports partial client evaluation in the top-level projection (essentially, the last call to Select()). If the top-level projection in the query can't be translated to the server, EF Core will fetch any required data from the server and evaluate remaining parts of the query on the client. If EF Core detects an expression, in any place other than the top-level projection, which can't be translated to the server, then it throws a runtime exception.

Your ToList and then Any can't be translated to sql and thus you get the error.

This would work

var results = _context.Tb1
                .Join(_context.Tb2, oc => oc.OptionId, o => o.OptionId, (oc, o) => new { OptionCost = oc, Option = o })
                .AsEnumerable()
                .Where(x => x.Tb2.Val1 == 1 
                    && x.Tb2.Val2 == null 
                    && ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))

BUT it would first fetch everything from the server and then apply the where clause, resulting to poor performance.

You could make it a bit better like this

var results = _context.Tb1
                .Join(_context.Tb2, oc => oc.OptionId, o => o.OptionId, (oc, o) => new { OptionCost = oc, Option = o })
                .Where(x => x.Tb2.Val1 == 1 
                    && x.Tb2.Val2 == null)
                .AsEnumerable()
                 .Where(ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))

Related Articles