I need to be able to produce the following sql for sql server 2005:

select top 1 
    [EndDate]
from 
    [dbo].[Service]
order by CASE when [EndDate] is null then '12/31/9999' end desc

What I need to happen is for the data to be sorted by end date descending with the nulls showing up first, however I want null to be the value returned.

I am using version 2.6 (2.6.08.0911).

Thanks!

Please post the code you have been working on so far.

Hi Walaa,

Thanks for responding.

I have no code so far because I cannot find any overrides for the SortExpression that meet my needs.

When I create a SortExpression it takes a SortClause, so if I do:

var s =new SortExpression( new SortClause(ServiceFields.EndDate.SetExpression(new DbFunctionCall("case when ({0}) is null then '12/31/9999' end", new object[] { ServiceFields.EndDate })), SortOperator.Descending));

it simply ignores the expression and does the sort on the field.

If I place the expression on the field in the entity/type list and use that field reference in the sort it will sort on the field (using its alias) however the select now returns the '12/31/9999':

var f = ServiceFields.EndDate.SetExpression(
    new DbFunctionCall(
       "case when ({0}) is null then '12/31/9999' end", 
        new object[] { ServiceFields.EndDate })).SetFieldAlias("EndDateField");
var s = new SortExpression(new SortClause(f, SortOperator.Descending));

which produces:

select CASE when [EndDate] is null then '12/31/2999' end as [EndDateField]
from [dbo].[Service]
order by [EndDateField] desc

Thanks.

On the SortClause, you have to set the **EmitAliasForExpressionAggregateField **= false.

ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14331 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15954

Sorry, got pulled away in a completely different direction before I got a chance to respond.

This worked perfectly! Thank you!

You guys rock!