Slow Order By on localized field

[brunorafaelcosta]

When trying to order by a localized property, the query executed by EntityFramework is very slow.
I've the Categories table with 9 millions of rows.

var categories = this.Categories
.Select(c => new
{
ID = c.ID,
Name = c.Name
})
.Localize(new CultureInfo("fr-Fr")
.OrderBy(x => x.Name)
.Skip(1000).Take(100)
.ToList();

Maybe you guys have a solution.
Thanks in advance

[popcatalin81]

@brunorafaelcosta could you post the execution plan for the Query ? Info here The plan would give a hint if tweaking the Linq generated would help or the problem is somewhere else.

[brunorafaelcosta]

Here is the execution plan.
The Categories table is actually Roles but the generated query stills the same.
I tried to save it as an image but is to big.

execplan.zip

Thanks

[popcatalin81]

@brunorafaelcosta Thank you for the example.

It does look like at fault here (for this large query) is the fact that for each translated property a Linq subquery is generated. It this in this scenario would greatly help to generate a single join instead of a bunch of subqueries.

I will explore this and try to find a way to generate a single join, as replacement to the subqueries. I expect that will improve SQL query plan generation as well.

[brunorafaelcosta]

Thanks

[popcatalin81]

I've done some experiments trying to see if tweaking Linq query generation would help.

To give some more precise examples the take the following query:

var categories = ctx.Categories
    .Select(c => new CategoryDto { Id = c.Id, Name = c.Name, Description = c.Description })
    .Localize(new CultureInfo("fr-FR"))
    .ToList();

The localize extension method effectively rewrites the query into the following query:

var categories = ctx.Categories
    .Select(c => new CategoryDto
    {
        Id = c.Id,
        Name = c.Localizations.Where(l => l.Language.IsoCode == "fr").Select(l => l.Name).FirstOrDefault() ?? c.Name,
        Description = c.Localizations.Where(l => l.Language.IsoCode == "fr").Select(l => l.Description).FirstOrDefault() ?? c.Description,
    })
    .ToList();

So the translated query has two subqueries, one for each traslated property Name and Description.

The generated SQL by EntityFrameowrk Core, looks like this:

SELECT [c].[Id], COALESCE((
    SELECT TOP(1) [l].[Name]
    FROM [CategoryLocalizations] AS [l]
    INNER JOIN [Languages] AS [l.Language] ON [l].[LanguageId] = [l.Language].[Id]
    WHERE ([l.Language].[IsoCode] = N'fr') AND ([c].[Id] = [l].[CategoryId])
), [c].[Name]) AS [Name], COALESCE((
    SELECT TOP(1) [l0].[Description]
    FROM [CategoryLocalizations] AS [l0]
    INNER JOIN [Languages] AS [l.Language0] ON [l0].[LanguageId] = [l.Language0].[Id]
    WHERE ([l.Language0].[IsoCode] = N'fr') AND ([c].[Id] = [l0].[CategoryId])
), [c].[Description]) AS [Description]
FROM [Categories] AS [c]

Also in the SQL one subquery is generated for each Linq Subquery.

To try to convinge EF to generate a join, I've tried tweaking the Linq generation in the following way. Use and intermediate Select to create an anonymous object with the Entity and coresponding Translation Entity, and do the projection afterwards:

var categories = ctx.Categories
    .Select(c => new { Entity = c, Localization = c.Localizations.FirstOrDefault(l => l.Language.IsoCode == "fr") })
    .Select(x => new CategoryDto(x.Entity.Id, x.Localization.Name ?? x.Entity.Name, x.Localization.Description ?? x.Entity.Description))
    .ToList();

However the generated SQL is almost identical.

The actual fix is to write the query with a join, a LEFT JOIN to be more particular. Using Linq method comprehension it's written like this:

var q = from c in ctx.Categories
	join l in ctx.CategoryLocalizations on c.Id equals l.CategoryId into cl
	from p in cl.Where(x => x.Language.IsoCode == "fr").DefaultIfEmpty()
	select new {c.Id, Name = p.Name ?? c.Name, Description = p.Description ?? c.Description};

This method produces a very nice SQL

SELECT [c].[Id], COALESCE([t].[Name], [c].[Name]) AS [Name], COALESCE([t].[Description], [c].[Description]) AS [Description]
FROM [Categories] AS [c]
LEFT JOIN (
    SELECT [l].*
    FROM [CategoryLocalizations] AS [l]
    INNER JOIN [Languages] AS [l.Language] ON [l].[LanguageId] = [l.Language].[Id]
    WHERE [l.Language].[IsoCode] = N'fr'
) AS [t] ON [c].[Id] = [t].[CategoryId]

But translates to the nasty Group join method syntax:

var categories = ctx.Categories
    .GroupJoin(ctx.CategoryLocalizations, c => c.Id, l => l.CategoryId, (c, l) => new { Entity = c, Localizations = l })
    .SelectMany(
        l => l.Localizations.Where(x => x.Language.IsoCode == "fr").DefaultIfEmpty(),
        (c, l) => new CategoryDto(c.Entity.Id, l.Name ?? c.Entity.Name, l.Description ?? c.Entity.Description)
    )
    .ToList();

So writing this way is way more complicated, but doable in the end. However since the Navigation propery is not used, that means the DbSet ctx.CategoryLocalizations toghether with the join selectors c => c.Id and l => l.CategoryId must be provided to the translation visitor.

Because of this I will investigate having and additional Translate method (which can also be used when there's no direct navigation property) in the following form:

var categories = ctx.Categories
    .LocalizeJoin(ctx.CategoryLocalizations, c.Id, l => l.CategoryId)
    .Select(c => new CategoryDto { Id = c.Id, Name = c.Name, Description = c.Description })
    .ToList();

This is a rather complex rewrite so I expect to find availability for it in the next weeks.

Any suggestions or PRs are welcome.