OrderBy on Nullable <int> with default value in Entity Framework

Question

We port some code to use the Entity Framework and have a query that tries to sort by Nullable field and provides a default sort value, this value is null using the Nullable.GetValueOrDefault (T) Function .

However, after execution, it returns the following error:

LINQ to Entities does not recognize the 'Int32 GetValueOrDefault (Int32)' method, and this method cannot be translated into a repository expression.

The request looks like this:

int magicDefaultSortValue = 250;
var query = context.MyTable.OrderBy(t => t.MyNullableSortColumn
                                          .GetValueOrDefault(magicDefaultSortValue));

From this answer, I see that there is a way to provide "translations" in your EDMX . Can we write a similar translation for this coalescing function?

NOTE. When I tried, the coalescing operator ??instead of GetValueOrDefault in the request works. So, is it possible that this work could be used?

Answer 1

I believe that you have found your answer. When you use ??, EF generates SQL, using CASEto select your sort value, if the value null, and then sorts by it.

MyTable.OrderBy (t => t.MyNullableSortColumn ?? magicDefaultSortValue).ToArray();

will create the following sql:

-- Region Parameters
DECLARE p__linq__0 Int = 250
-- EndRegion
SELECT 
[Project1].[MyColumn1] AS [MyColumn1], 
[Project1].[MyNullableSortColumn] AS [MyNullableSortColumn]
FROM ( SELECT 
    CASE WHEN ([Extent1].[MyNullableSortColumn] IS NULL) THEN @p__linq__0 ELSE [Extent1].[MyNullableSortColumn] END AS [C1], 
    [Extent1].[MyColumn1] AS [MyColumn1], 
    [Extent1].[MyNullableSortColumn] AS [MyNullableSortColumn]
    FROM [dbo].[MyTable] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C1] ASC

, LINQPad, EF- sql. , EntityFunctions SqlFunctions, .