[Suggestion of Sample] EF.Functions.OrderBy accepts ternary operator as parameter

[DiogenesBR]

This is not really a Issue, but after working all my Saturday on it, I need to share my use of the RowNumber Function.

A found this package trying to solve a specific rule of the translations in the application I work.

For each Item, there are multiple translation records, but not all languages are filled for each item, then I have take the first translation based on the rule below.

First try to get the translation in the user's language If there no translation in the user's language, try to get the application default language If it also don't have a translation, get the any available

Using the a usual EF query it makes a subquery in the translation table for each Item translated.

When I found this library at first I thought that I would need use expression in the OrderBy parameter, because the name "column" of the parameter is kind of misleading I even started to write a feature request, but before sending I tried understand how it worked, and spend some hours in the fiddling with sample code, trying to do it myself, but at the end I discover that there no need to make any change.

I just need to write a ternary operator as the parameter and the EF translated it for me.

The code ended like this:

var userLanguage = 2; //Portuguese
var applicationLanguage = 1; //English

var productList = await ctx.ProductTranslations
                            .Where(pt=> pt.Field == ProductTranslation.FieldEnum.Name)
                            .Select(pt=> new
                            {
                            pt.ProductId,
                            pt.Product,
                            Name = pt.Text,
                            LangOrder = EF.Functions.RowNumber(
                                pt.ProductId,
                                EF.Functions.OrderBy(
                                    pt.LanguageId == userLanguage ? 0 //user's language has priority 0
                                    : pt.LanguageId == applicationLanguage ? 1 //application language has priority 1
                                    : 2 )) //Any other language has priority 2
                            })
                            .AsSubQuery()
                            .Where(i => i.LangOrder == 1)
                            .OrderBy(l => l.Name)
                            .ToListAsync();

The Output Query is this:

      SELECT [t].[ProductId], [t].[Id], [t].[Name], [t].[LangOrder]
      FROM (
          SELECT [p].[ProductId], [p0].[Id], [p].[Text] AS [Name], ROW_NUMBER() OVER(PARTITION BY [p].[ProductId] ORDER BY CASE
              WHEN [p].[LanguageId] = @__userLanguage_2 THEN 0
              WHEN [p].[LanguageId] = @__applicationLanguage_3 THEN 1
              ELSE 2
          END) AS [LangOrder]
          FROM [demo].[ProductTranslations] AS [p]
          INNER JOIN [demo].[Products] AS [p0] ON [p].[ProductId] = [p0].[Id]
          WHERE [p].[Field] = N'Name'
      ) AS [t]
      WHERE [t].[LangOrder] = 1
      ORDER BY [t].[Name]

I my test is available on the Fork https://github.com/diogenesdirkx/Thinktecture.EntityFrameworkCore

Thank you

[PawelGerr]

The "column" can be virtually any expression, which is translatable by EF. To keep the expression in RowNumber simple you can use multiple projections (Select).

var productList = await ctx.ProductTranslations
                            .Where(pt=> pt.Field == ProductTranslation.FieldEnum.Name)
                            .Select(pt=> new
                            {
                                Translation = pt,
                                Priority =  pt.LanguageId == userLanguage ? 0 //user's language has priority 0
                                            : pt.LanguageId == applicationLanguage ? 1 //application language has priority 1
                                            : 2 //Any other language has priority 2
                            })
                            .Select(i=> new
                            {
                                i.Translation.ProductId,
                                i.Translation.Product,
                                Name = i.Translation.Text,
                                LangOrder = EF.Functions.RowNumber(
                                    i.Translation.ProductId, // partition by ProductId
                                    EF.Functions.OrderBy(i.Priority)) // order the partition by Priority
                            })
                            .AsSubQuery()
                            .Where(i => i.LangOrder == 1)
                            .OrderBy(l => l.Name)
                            .ToListAsync();

I've added the example to docs: Examples.

Thank you for your support!

[IanKemp]

It would be useful to have the generated SQL in the examples too!

[PawelGerr]

done