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

Question

We are migrating some code to use Entity Framework and have a query that is trying to sort on a Nullable field and provides a default sort value is the value is null using the Nullable.GetValueOrDefault(T) function.

However, upon execution it returns the following error:

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

The query looks like:

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

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

NOTE: When I tried, the ?? coalescing operator instead of GetValueOrDefault in the query it does work. So perhaps whatever makes that work could be leveraged?

Answer 1

I believe you found your answer. When you use ??, EF generates SQL using a CASE to select your sort value if the value is null, and then sorts on that.

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

will generate 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

As an aside, I would recommend getting LINQPad which will let you work with your EF models and view the sql being generated. Also, it is helpful to know about the EntityFunctions class and SqlFunctions class as they provide access to several useful functions.