Hi,

When I do an OrderBy on a field that I aliased it will throw the following exception:

[NullReferenceException: Object reference not set to an instance of an object.]
   SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean appendAlias, String containingObjectName, String actualContainingObjectName) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:283
   SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:1063
   SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:327
   SD.LLBLGen.Pro.ORMSupportClasses.SortExpression.ToQueryText(Boolean aliasesForExpressionsAggregates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\SortExpression.cs:165
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendOrderByClause(ISortExpression sortClauses, QueryFragments destination, IQuery query) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:1628
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\DynamicQueryEngine.cs:649
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:1126
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\DynamicQueryEngine.cs:699
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CreateQueryFromElements(ITransaction transactionToUse, QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:2282
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:640
   SD.LLBLGen.Pro.QuerySpec.SelfServicing.SelfServicingExtensionMethods.FetchAsDataTable(IDao dao, DynamicQuery query, DataTable destination, ITransaction transaction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QuerySpec\SelfServicingSpecific\SelfServicingExtensionMethods.cs:459
(... our code)

this is the query:

  query.Select(CompanyFields.CompanyId, CompanyFields.Name.As("Company Name"), ProductFields.ProductId, ProductFields.Name,
                            Functions.CountRow().As("Orderitem Count"),
                            (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As("Revenue"),
                            OrderitemFields.Quantity.Sum().As("Quantity"))
                .From(queryFactory.Orderitem
                            .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                            .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                            .LeftJoin(OrderitemEntity.Relations.ProductEntityUsingProductId)
                            .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId))
                .Where(OrderFields.Type == (int)orderType)
                .GroupBy(CompanyFields.CompanyId, CompanyFields.Name, ProductFields.ProductId, ProductFields.Name)
                .OrderBy("Revenue".Ascending());

It does work fine without the 'OrderBy'.

What am I missing?

LLBLGen Pro 4.1.13.1213

• Please post the code before that (i.e. what contains the 'query' variable at that moment).
• Please update to the newest runtime library version (4.1.14.0327).

Hi,

I've updated to 4.1.14.0327, the same happened.

The code:

Creating the Query: (ColumnNames is a class with constant strings for the column names)

 private DynamicQuery RetrieveProductTopXStats(OrderType orderType, string orderBy, int maxResults)
        {
            var queryFactory = new QueryFactory();
            DynamicQuery query = queryFactory.Create();

            query.Select(CompanyFields.CompanyId, CompanyFields.Name.As("Company Name"), ProductFields.ProductId, ProductFields.Name,
                            Functions.CountRow().As("Orderitem Count"),                         
                            (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As(ColumnNames.TotalRevenue),
                            OrderitemFields.Quantity.Sum().As(ColumnNames.TotalQuantity))
                .From(queryFactory.Orderitem
                            .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                            .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                            .LeftJoin(OrderitemEntity.Relations.ProductEntityUsingProductId)
                            .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId))
                .Where(OrderFields.Type == (int)orderType)
                .GroupBy(CompanyFields.CompanyId, CompanyFields.Name, ProductFields.ProductId, ProductFields.Name)
                // Posted thread to get sorting from the DB: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=22726
                .OrderBy(ColumnNames.TotalRevenue.Ascending());
            
            if (this.filter != null)
            {
                query.Where(OrderFields.Created >= this.filter.From);
                query.Where(OrderFields.Created <= this.filter.Till);
                query.Where(OrderFields.CompanyId == this.filter.CompanyId);

                if (this.filter.DeliverypointIds != null && this.filter.DeliverypointIds.Count > 0)
                    query.Where(OrderFields.DeliverypointId == this.filter.DeliverypointIds);

                if (this.filter.DeliverypointgroupIds != null && this.filter.DeliverypointgroupIds.Count > 0)
                    query.Where(DeliverypointFields.DeliverypointgroupId == this.filter.DeliverypointgroupIds);

                if (!this.filter.IncludeFailedOrders)
                    query.Where(OrderFields.ErrorCode == 0);
            }

            return query;
        }

Then fetching it:

 TypedListDAO dao = new TypedListDAO();         
            DynamicQuery query;
            if (entityType == EntityType.ProductEntity)
            {
                query = this.RetrieveProductTopXStats(orderType, columnName, topX);
            }
            else
            {
                query = this.RetrieveCategoryTopXStats(orderType, columnName, topX);
            }
            DataTable queryResult = dao.FetchAsDataTable(query);

Will look into it. You refer to a column which should be referable through the system you use.

There's no support for fields which are solely named by their field names and not having a source.

I realize the usability of "string".Ascending() is therefore limited, but it is what it is. The main issue is that an object alias has to be specified, and you don't do that. There is one way to fix this which is making the query a subquery and order on the values of the subquery. I've given the method below. This way the order by has a source specification and therefore will not cause a problem.

private DynamicQuery RetrieveProductTopXStats(OrderType orderType, string orderBy, int maxResults)
{
    var qf = new QueryFactory();
    var q=qf.Create()
            .Select(CompanyFields.CompanyId.Source("g"), 
                    qf.Field("Company Name").Source("g"), 
                    ProductFields.ProductId.Source("g"), 
                    ProductFields.Name.Source("g"),
                    qf.Field("Orderitem Count").Source("g"),  
                    qf.Field(ColumnNames.TotalRevenue).Source("g"),
                    qf.Field(ColumnNames.TotalQuantity).Source("g")
            )
            .From(
                qf.Create()
                    .Select(CompanyFields.CompanyId, 
                        CompanyFields.Name.As("Company Name"), 
                        ProductFields.ProductId, 
                        ProductFields.Name,
                        Functions.CountRow().As("Orderitem Count"),                         
                        (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As(ColumnNames.TotalRevenue),
                        OrderitemFields.Quantity.Sum().As(ColumnNames.TotalQuantity))
                    .From(qf.Orderitem
                                .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                                .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                                .LeftJoin(OrderitemEntity.Relations.ProductEntityUsingProductId)
                                .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId))
                    .Where(OrderFields.Type == (int)orderType)
                    .GroupBy(CompanyFields.CompanyId, CompanyFields.Name, ProductFields.ProductId, ProductFields.Name)
                    .As("g"))
            .OrderBy(qf.Field(ColumnNames.TotalRevenue).Source("g").Ascending());
    
    if (this.filter != null)
    {
        q.Where(OrderFields.Created >= this.filter.From);
        q.Where(OrderFields.Created <= this.filter.Till);
        q.Where(OrderFields.CompanyId == this.filter.CompanyId);

        if (this.filter.DeliverypointIds != null && this.filter.DeliverypointIds.Count > 0)
            q.Where(OrderFields.DeliverypointId == this.filter.DeliverypointIds);

        if (this.filter.DeliverypointgroupIds != null && this.filter.DeliverypointgroupIds.Count > 0)
            q.Where(DeliverypointFields.DeliverypointgroupId == this.filter.DeliverypointgroupIds);

        if (!this.filter.IncludeFailedOrders)
            q.Where(OrderFields.ErrorCode == 0);
    }

    return q;
}

Another way to solve it is to simply specify the expression again: (in your original query)

 .OrderBy( (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().Ascending());

Thanks for looking in to this one as well, will try tomorrow morning first thing.

Tried the second option, which I would think would give two queries in the database, but it just sticks to one, so feel like a solid option.

Results - Quantity:

SELECT TOP(@p2) [DatabaseName].[dbo].[company].[companyid], 
                [DatabaseName].[dbo].[company].[name] 
                AS [Company Name], 
                [DatabaseName].[dbo].[product].[productid], 
                [DatabaseName].[dbo].[product].[name], 
                Count(*) 
                AS [Orderitem Count], 
                Sum(( [DatabaseName].[dbo].[orderitem].[productpricein] * 
                          [DatabaseName].[dbo].[orderitem].[quantity] )) 
                AS [Revenue], 
                Sum([DatabaseName].[dbo].[orderitem].[quantity]) 
                AS [Quantity] 
FROM   (((( [DatabaseName].[dbo].[order]  
            RIGHT JOIN [DatabaseName].[dbo].[orderitem]  
                    ON [DatabaseName].[dbo].[order].[orderid] = 
                       [DatabaseName].[dbo].[orderitem].[orderid]) 
          LEFT JOIN [DatabaseName].[dbo].[company]  
                 ON [DatabaseName].[dbo].[company].[companyid] = 
                    [DatabaseName].[dbo].[order].[companyid]) 
         LEFT JOIN [DatabaseName].[dbo].[product]  
                ON [DatabaseName].[dbo].[product].[productid] = 
                   [DatabaseName].[dbo].[orderitem].[productid]) 
        LEFT JOIN [DatabaseName].[dbo].[deliverypoint]  
               ON 
[DatabaseName].[dbo].[deliverypoint].[deliverypointid] = [DatabaseName].[dbo].[order].[deliverypointid]) 
WHERE  (( [DatabaseName].[dbo].[order].[type] = @p3 
          AND [DatabaseName].[dbo].[order].[created] >= @p4 
          AND [DatabaseName].[dbo].[order].[created] <= @p5 
          AND [DatabaseName].[dbo].[order].[companyid] = @p6 
          AND [DatabaseName].[dbo].[deliverypoint].[deliverypointgroupid] 
              IN ( 
              @p7 
              ) 
          AND [DatabaseName].[dbo].[order].[errorcode] = @p8 )) 
GROUP  BY [DatabaseName].[dbo].[company].[companyid], 
          [DatabaseName].[dbo].[company].[name], 
          [DatabaseName].[dbo].[product].[productid], 
          [DatabaseName].[dbo].[product].[name] 
ORDER  BY [quantity] DESC 

And by revenue

SELECT TOP (@p2) [DatabaseName].[dbo].[Company].[CompanyId]
    ,[DatabaseName].[dbo].[Company].[Name] AS [Company Name]
    ,[DatabaseName].[dbo].[Product].[ProductId]
    ,[DatabaseName].[dbo].[Product].[Name]
    ,COUNT(*) AS [Orderitem Count]
    ,SUM(([DatabaseName].[dbo].[Orderitem].[ProductPriceIn] * [DatabaseName].[dbo].[Orderitem].[Quantity])) AS [Revenue]
    ,SUM([DatabaseName].[dbo].[Orderitem].[Quantity]) AS [Quantity]
FROM (
    (
        (
            (
                [DatabaseName].[dbo].[Order] 
                RIGHT JOIN [DatabaseName].[dbo].[Orderitem]  ON [DatabaseName].[dbo].[Order].[OrderId] = [DatabaseName].[dbo].[Orderitem].[OrderId]
                ) LEFT JOIN [DatabaseName].[dbo].[Company]  ON [DatabaseName].[dbo].[Company].[CompanyId] = [DatabaseName].[dbo].[Order].[CompanyId]
            ) LEFT JOIN [DatabaseName].[dbo].[Product]  ON [DatabaseName].[dbo].[Product].[ProductId] = [DatabaseName].[dbo].[Orderitem].[ProductId]
        ) LEFT JOIN [DatabaseName].[dbo].[Deliverypoint]  ON [DatabaseName].[dbo].[Deliverypoint].[DeliverypointId] = [DatabaseName].[dbo].[Order].[DeliverypointId]
    )
WHERE (
        (
            [DatabaseName].[dbo].[Order].[Type] = @p3
            AND [DatabaseName].[dbo].[Order].[Created] >= @p4
            AND [DatabaseName].[dbo].[Order].[Created] <= @p5
            AND [DatabaseName].[dbo].[Order].[CompanyId] = @p6
            AND [DatabaseName].[dbo].[Deliverypoint].[DeliverypointgroupId] IN (@p7)
            AND [DatabaseName].[dbo].[Order].[ErrorCode] = @p8
            )
        )
GROUP BY [DatabaseName].[dbo].[Company].[CompanyId]
    ,[DatabaseName].[dbo].[Company].[Name]
    ,[DatabaseName].[dbo].[Product].[ProductId]
    ,[DatabaseName].[dbo].[Product].[Name]
ORDER BY SUM(([DatabaseName].[dbo].[Orderitem].[ProductPriceIn] * [DatabaseName].[dbo].[Orderitem].[Quantity])) DESC

Thanks.