LLBLGen 4.2

I have a table with two Date Fields - StartDateTime which is never null and CompletionDateTime which is nullable.

I want to replace the OrderBy in the query below so that the items are returned in two 'blocks' So CompletionDateTime IS NULL being the first block and CompletionDate IS NOT NULL being the second block. Sort clauses something like this:- 1) (CompletionDateTime IS NULL) DESC 2) CompletionDateTime DESC 3) StartDateTime DESC

How can I do this?

                var query = qf.StockCheck
                    .OrderBy(StockCheckFields.StartDateTime | SortOperator.Descending)
                    .Select(() => new StockCheckSummaryInfo
                                      {
                                          ID = StockCheckFields.ID.ToValue<int>(),
                                          Description = StockCheckFields.Description.ToValue<string>(),
                                          StartDateTime = StockCheckFields.StartDateTime.ToValue<DateTime>(),
                                          TotalCount = StockCheckFields.TotalCount.ToValue<int>(),
                                          SeenCount = StockCheckFields.SeenCount.ToValue<int>(),
                                          LastSeenDateTime = StockCheckFields.LastSeenDateTime.ToValue<DateTime?>(),
                                          CompletionDateTime = StockCheckFields.CompletionDateTime.ToValue<DateTime?>(),
                                          CompletionSummary = StockCheckFields.CompletionSummary.ToValue<string>(),
                                          RemovedCount = StockCheckFields.RemovedCount.ToValue<int?>(),
                                      });

Please check the following example:

IExpression dateExpression = new DbFunctionCall("CASE WHEN {0} IS NULL THEN 0 ELSE 1 END", new object[] { OrderFields.ShippedDate });
IEntityField2 shippedDateField = OrderFields.ShippedDate.SetExpression(dateExpression);

var query = qf.Order.OrderBy(shippedDateField.Ascending(), OrderFields.OrderDate.Ascending());

Which generates:

ORDER BY CASE WHEN [NORTHWIND].[dbo].[Orders].[ShippedDate] IS NULL THEN 0 ELSE 1 END ASC, [NORTHWIND].[dbo].[Orders].[OrderDate] ASC

Doesn't seem to work for me.

First I got an error saying that CompletionDateTime was not allowed twice in the ORDER BY list. When I removed that temporarily, the SQL generated didn't include the CASE at all

.. ORDER BY [CompletionDateTime] ASC, [SmartLibrarian].[dbo].[StockCheck].[StartDateTime] DESC

I even tried temporarily removing that field from the SELECT list but still no CASE was generated though I note that the first clause is not fully qualified so it is trying to do something different.

This is the code I am using:-

                IExpression dateExpression = new DbFunctionCall("CASE WHEN {0} IS NULL THEN 0 ELSE 1 END", new object[] { StockCheckFields.CompletionDateTime });
                var completedDateTimeSortField = StockCheckFields.CompletionDateTime.SetExpression(dateExpression);

                var query = qf.StockCheck
                    .OrderBy(completedDateTimeSortField | SortOperator.Ascending)
                    //.OrderBy(StockCheckFields.CompletionDateTime | SortOperator.Descending)
                    .OrderBy(StockCheckFields.StartDateTime | SortOperator.Descending)

I think it's missing just a property to indicate that the expression should be generated instead of the field value. Try this (I'm using my Northwind example):

var dateExpression = new DbFunctionCall("CASE WHEN {0} IS NULL THEN 0 ELSE 1 END", new object[] { EmployeeFields.HireDate});
            
using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var query = qf.Employee
    .OrderBy(new SortClause(EmployeeFields.HireDate.SetExpression(dateExpression), 
        null, SortOperator.Ascending).SetEmitAliasForExpressionAggregateField(false))

    .OrderBy(EmployeeFields.HireDate | SortOperator.Descending)
    .OrderBy(EmployeeFields.BirthDate | SortOperator.Descending);
    // ...
}

My version - 4.2.0.0 - doesn't have that property. Isn't it a v5 feature?

Found a way around it!

I already had this extension method

        public static IEntityFieldCore CoalesceToZero(this IEntityFieldCore field)
        {
            return BlockFactory.CreateExpressionField(new DbFunctionCall("COALESCE({0}, '0')", new object[] { field }));
        }

so I created a similar one

        public static IEntityFieldCore ToIsNullExpression(this IEntityFieldCore field)
        {
            return BlockFactory.CreateExpressionField(new DbFunctionCall("CASE WHEN {0} IS NULL THEN 0 ELSE 1 END", new object[] { field }));
        }

So I can then use

.OrderBy(StockCheckFields.CompletionDateTime.ToIsNullExpression().Ascending())

Interestingly I had to use .Ascending() because if I changed my new method to return EntityField2 and used "| SortOperator.Ascending", the query bombs.

Hmm, it should work with the | operator too, (as that simply creates a SortClause with the arguments on either side, similar to .Ascending()). Could you elaborate a bit what 'query bombs' means: it throws an error in the runtime or is the SQL wrong?

It throws this exception:-

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException
  HResult=0x80131600
  Message=An exception was caught during the execution of a retrieval query: Invalid column name 'ExF'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses
  StackTrace:
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   at SmartLibrarian.DAL.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehaviour) in F:\src\SmartLibrarian\SmartLibrarian.DAL\Custom\DataAccessAdapter.cs:line 540

Inner Exception 1:
SqlException: Invalid column name 'ExF'.

The SQL output was

SELECT
  [StockCheck].[ID],
  [StockCheck].[Description],
  [StockCheck].[StartDateTime],
  [StockCheck].[TotalCount],
  [StockCheck].[SeenCount],
  [StockCheck].[LastSeenDateTime],
  [StockCheck].[CompletionDateTime],
  [StockCheck].[CompletionSummary],
  [StockCheck].[RemovedCount] 
FROM
  [StockCheck]   
ORDER BY
  [ExF] ASC,
  [StockCheck].[CompletionDateTime] DESC,
  [StockCheck].[StartDateTime] DESC

I think the | operator should maybe call SortClauseProducers.CreateSortClause because that deals with EmitAliasForExpressionAggregateField and the Ascending() doesn't?

Daelmo: I now see the property you meant! it was "SetEmitAliasForExpressionAggregateField()" I couldn't find.

My original example works over here, but maybe you have a more complex example where an alias is used for the fields. So please try the following (It's working over here too).

IExpression datePart = new DbFunctionCall("CASE WHEN {0} IS NULL THEN 0 ELSE 1 END", new object[] { OrderFields.ShippedDate });
IEntityField2 shippedDateField = OrderFields.ShippedDate.SetExpression(datePart);
ISortClause sorter = shippedDateField.Ascending();
sorter.EmitAliasForExpressionAggregateField = false;

var qf = new QueryFactory();

using (var adapter = new DataAccessAdapter())
{

    var query = qf.Order.OrderBy(sorter, OrderFields.OrderDate.Ascending());

    var list = adapter.FetchQuery(query);
}

Walaa

Your original example failed (on my side with my data) for two reasons:- 1) I use multiple OrderBy including the same Field twice 2) EmitAliasForExpressionAggregateField

I think you sorted (2) with your latest sample but I reckon it would still fail on (1)

I am up and working now but I'll try out your code if you want to know if it works or not.

The only issue left is the difference between using | and .Ascending() - that doesn't affect me but Frans might be looking into ensuring they both do the same.

They're indeed different, as is clear from the implementation!

.Ascending()/Descending() set EmitAliasForExpressionAggregateField if the field it's called on has an expression, as that's very likely what you want (especially in queryspec queries). The | operator does no such thing.

I think that's exactly what's going on in your situation.

the '|' operator is lower level than .Ascending() and really a relic of a time when things had to be done with arcane cruft like operator overloads. It's recommended to use the extensionmethods instead of the operator overloads, but alas lots of code still have them.

I think it's not feasible to change the behavior of the operator, as it would be a breaking change. It's however good to know they're indeed different. I haven't realized that