I’m trying to use the following query but results in the error message “Translating this query requires the SQL APPLY operation, which is not supported on SQLite.”

   return await (from c in _dbContext.Charter
                 join r in _dbContext.Roster on c.Id equals r.CharterId into rosters
                 select new CharterGridData
           {
               CharterName = c.Name,
               Roster = rosters.ToList()
           }).ToListAsync();

I’ve tried doing things like Roster = _dbContext.Roster.Where(r => r.CharterId == c.Id) but it doesn’t load at all (Roster table has over 2mil records).

Is there any way to do this?