EF Core Linq query could not be translated when using other than equals operator

When trying to translate a Linq query with EF Core we get the error

Translating this query requires APPLY operation in SQL which is not supported on SQLite

We tried several things but we get this error as soon as we use any operator other than "==" when we compare the dates y.count.SubmittedDate and x.SubmittedDate. In other words.. the query can be translated when using the following comparisation: y.count.SubmittedDate == x.SubmittedDate.

var test = await Db.Set<TblCounts>()
            .AsSplitQuery()
            .Join(Db.TblCountingLocations, count => count.Id, countingLocation
                => countingLocation.FkCount, (count, countingLocation) => new
            {
                count,
                countingLocation
            })
            .Select(x => new
            {
                x.count.SubmittedDate,
                x.countingLocation.FkLocation,
            }).Distinct()
            .SelectMany(x =>
                    Db.Set<TblCounts>().Join(Db.TblCountingLocations, count=> count.Id, countingLocation
                        => countingLocation.FkCount, (count, countingLocation) => new
                    {
                        count,
                        countingLocation
                    })
                        .Where(y => y.count.SubmittedDate < x.SubmittedDate // this comparisation leads to the problem
                                  && y.countingLocation.FkLocation == x.FkLocation)
                    .OrderByDescending(y => y.count.SubmittedDate)
                    .Take(1),
                (x, y) => new
                {
                    countSubmittedDate = y.count.SubmittedDate,
                    x.FkLocation
                }).ToListAsync();