Improperly created an Apply Join

Question

Given the following sqlite table

CREATE TABLE `ComponentNameLookup` (
    `Id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `ComponentId`   INTEGER NOT NULL,
    `ComponentName` TEXT NOT NULL,
    `Culture`   TEXT
);

insert into ComponentNameLookup
    (Id,ComponentId,ComponentName,Culture)
values
    (1,  0, 'Logger',                  NULL  ),
    (2,  1, 'Transport',               NULL  ),
    (3,  2, 'Error Handler',           NULL  ),
    (4,  3, 'Persistance',             NULL  ),
    (5,  0, 'Registrador',            'es-ES'),
    (6,  1, 'Transporte',             'es'   ),
    (7,  2, 'Controlador de errores', 'es-ES'),
    (8,  3, 'Persistencia',           'es-ES'),
    (9,  1, 'Транспорт',              'ru'   ),
    (10, 2, 'Обработчик ошибок',      'ru-RU')

And the Linq Query

void Main()
{
    string cultureString = Thread.CurrentThread.CurrentCulture.Name;
    string languageName = Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName;

    cultureString = "es-ES";
    languageName = "es";

    var localized = context.ComponentNameLookups
        .Where(x => x.Culture == cultureString || x.Culture == languageName || x.Culture == null)
        .GroupBy(c => c.ComponentId)
        .Select(g => new KeyValue<int?, string>{
            Key = g.Key,
            Value = g
                .OrderByDescending(x => x.Culture.Length)
                .Select(c => c.ComponentName)
                .FirstOrDefault(),
        })
    ;
    localized.ToArray();
}

public class KeyValue<T1, T2> {
    public T1 Key;
    public T2 Value;
}

I get the error

System.Data.Entity.Core.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.NotSupportedException: APPLY joins are not supported

Something like this shouldn't need to be an APPLY JOIN. Is there any other way I can do this query using LINQ?

Answer 1

I can't test on SqlLite, but the query has (although different) issue with MySQL (it runs fine with SqlServer), so you can try replacing

.OrderByDescending(x => x.Culture.Length)

with

.Where(c => c.Culture.Length == g.Max(e => e.Culture.Length))

which fixes MySQL issue and eliminates the OUTER APPLY from SqlServer query, so it might work for SqlLite as well.