I'm using LLBLGen Pro 2.0.0.0 Final with adapter and .NET 2.0/c# connecting to a SQL Server 2005 dB.

Can anyone tell me how I might create a SortExpression that would generate a SQL expression that looks like the one below?

select  activity.*
from    activity
left outer join account on activity.regardingaccountid = account.recid
left outer join contact on activity.regardingcontactid = contact.recid
order by 
    case
        when activity.regardingaccountid is not null then account.name
        when activity.regardingcontactid is not null then contact.name
        else ''
    end desc

For further background, in my database an Activity can be linked to an Account or a Contact or neither. I have an Activity, Account and Contact entity and for the Activity entity I have create a "Fields on related fields" for the Name of the Account and Contact entities. I have then extended the LLBL Gen generated ActivityEntity class and included a property that looks like this:

        public virtual System.String RegardingName
        {
            get
            {
                if (this.RegardingAccountId != null)
                {
                    return this.RegardingAccountName;
                }
                else if (this.RegardingContactId != null)
                {
                    return this.RegardingContactName;
                }
                else
                {
                    return string.Empty;
                }
            }
        }

Once I have retrieved a collection of Activity entities I bind this property to the column of a listview and within the listview the user can choose to sort by the RegardingName or a number of other bound Activity properties.

Previously the sorting was performed internally within my listview control but I am now going to implement data paging so I need to perform the sorting on the server.

As ever any help would be greatly appreciated.

Chris

order by case when activity.regardingaccountid is not null then account.name when activity.regardingcontactid is not null then contact.name else '' end desc

Use something like:

You will need to use v.2.5 for the CASE support in the DBFunctionCalls

EntityField2 myField = ActivityFields.Id;
myField.ExpressionToApply = myExpression;
SortExpression sorter = new SortExpression(myField | SortOperator.Descending);

Where myExpression is the return of a DBFunctionCall that implement the CASE statement. Please check the CASE support in the LLBLGen Pro manual's section "Using the generated code -> Calling a database function"

Thanks for your response Walaa, looks like this is just the impetus I needed to look at upgrading to v2.5

I've now upgraded to v2.5 but could do with some help getting the sorting working using the CASE statement.

I have tried a simple sort to start with but the CASE is not being used in the generated query.

To give you an idea of the code that I'm using here are some simplified snippets from my control:

this.eclEntities.EntityFactoryToUse = new ActivityEntityFactory();

EntityField2 sortField = ActivityFields.Subject;
sortField.ExpressionToApply =
     new DbFunctionCall("CASE {0} WHEN 'ABC' THEN 1 ELSE 0 END",
     new object[] {ActivityFields.Subject});
ISortExpression sorter = new SortExpression();
sorter.Add(sortField | SortOperator.Descending);

adapter.FetchEntityCollection(eclEntities, null, 0, sorter);

When I use the dubug visualizer against my sorter object I see Pseudo SQL of:

Subject DESC

And the resulting query (I've cut it down so it's easier to read) looks like this with no CASE statement:

exec sp_executesql N'SELECT [dbo].[Activity].[recID] AS [RecId], [dbo].[Activity].[subject] AS [Subject] FROM [dbo].[Activity] ORDER BY [Subject] DESC'

Any suggestions on where I'm going wrong would be very welcome.

Please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12394