Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> ORDER BY with IS NULL
 

Pages: 1
LLBLGen Pro Runtime Framework
ORDER BY with IS NULL
Page:1/1 

  Print all messages in this thread  
Poster Message
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 06-Mar-2019 10:55:50.  
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?

Code:
                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?>(),
                                     });
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 06-Mar-2019 18:28:55.  
Please check the following example:
Code:
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:
Code:
ORDER BY CASE WHEN [NORTHWIND].[dbo].[Orders].[ShippedDate] IS NULL THEN 0 ELSE 1 END ASC, [NORTHWIND].[dbo].[Orders].[OrderDate] ASC


  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 06-Mar-2019 20:29:14.  
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
Code:
.. 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:-

Code:
                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)

  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8065 posts
# Posted on: 07-Mar-2019 07:38:09.  
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):

Code:
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);
    // ...
}


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 07-Mar-2019 09:54:49.  
My version - 4.2.0.0 - doesn't have that property. Isn't it a v5 feature?
  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 07-Mar-2019 10:10:52.  
Found a way around it!

I already had this extension method

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


so I created a similar one

Code:
        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

Code:
.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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37455 posts
# Posted on: 07-Mar-2019 13:20:43.  
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?
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 07-Mar-2019 16:32:18.  
It throws this exception:-
Code:
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
Code:
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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 07-Mar-2019 17:01:59.  
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).
Code:
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);
}

  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 07-Mar-2019 17:45:22.  
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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37455 posts
# Posted on: 08-Mar-2019 11:35:25.  
They're indeed different, as is clear from the implementation! Regular Smiley

.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 Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.