LINQ GroupBy with SQLite.EF6 results in "APPLY joins not supported" exception

I have a table VersionedEntities

that looks like this:

+----+--------------+---------+
| Id | Name         | Version |
+----+--------------+---------+
| 1  | FirstEntity  | 1       |
+----+--------------+---------+
| 2  | SecondEntity | 2       |
+----+--------------+---------+
| 1  | ThirdEntity  | 3       |
+----+--------------+---------+

      

        
        
        
      

    

Version

is the primary key.

VersionedEntity

class:

[Table("VersionedEntities")]
public class VersionedEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Key]
    public long Version { get; set; }
}

      

        
        
        
      

    

I want to select the latest version of each Id

, which results in this:

+----+--------------+---------+
| Id | Name         | Version |
+----+--------------+---------+
| 2  | SecondEntity | 2       |
+----+--------------+---------+
| 1  | ThirdEntity  | 3       |
+----+--------------+---------+

      

        
        
        
      

    

I already have a working query when used Microsoft SQL Server

as a database:

List<VersionedEntity> versionedEntities;

using (var dbContext = _createDbContext())
{
    versionedEntities = dbContext.VersionedEntity
        .GroupBy(versionedEntity => versionedEntity.Id)
        .Select(group => group.OrderByDescending(versionedEntity => versionedEntity.Version).FirstOrDefault()).ToList());
}

      

        
        
        
      

    

I want to use SQLite as a database instead, but using SQLite the above query results in NotSupportedException

the message: APPLY joins are not supported

.

I found that only LEFT OUTER JOIN

( source ) is implemented in SQLite . I believe LINQ is GroupBy()

using one of the unimplemented unions.

I would like to know if there is a workaround for this, or if I can rewrite my query to something SQLite compatible.