- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Null reference exception with QuerySpec OrderBy string value
Joined: 01-Feb-2005
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
Joined: 01-Feb-2005
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);
Joined: 17-Aug-2003
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());
Joined: 01-Feb-2005
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.