Hi,

I just seem to not get QuerySpec (yet). I've done some relatively simple queries now, but when I tried to do something with a subquery I just couldn't bring it togerther from the manual, examples and common sense. I hope someone can help me with the SQL query below (and also see the approach I've taken to convert it to QuerySpec).

If someone has the time I would also welcome 1 or 2 hours of paid for consultancy to get me on track, as I've now spent too much time trying it myself.

Feel like a real LLBLGen n00b again...

The SQL Query:

select ProductName, sum(Revenue) as TotalRevenue from 
    (
        select 
            Orderitem.ProductName,      
            Orderitem.Quantity * 
                (
                    Orderitem.ProductPriceIn + 
                    (
                        select sum(OrderitemAlterationitem.AlterationoptionPriceIn) 
                        from OrderitemAlterationitem 
                        where OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId
                    )
                ) as Revenue
        from Orderitem
        left join [Order] on [Order].OrderId = Orderitem.OrderId
        where [Order].CompanyId = 199
    ) as CalculatedOrderItems
group by ProductName
order by TotalRevenue desc

Attempt to build it in QuerySpec (I've tried quite some others as well):

                TypedListDAO dao = new TypedListDAO();
                var qf = new QueryFactory();
                DynamicQuery query2 = qf.Create()
                    .Select(OrderitemFields.ProductName, qf.Field("Revenue").Sum().As("TotalRevenue"))
                    .From(qf.Create()
                            .Select(OrderitemFields.ProductName, (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum())
                                ))))
                    .GroupBy(OrderitemFields.ProductName)
                    .As("G"); 

Result: An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a DynamicQuery to a String.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It feels like I fail to understand: - How to link subqueries to parent queries - When and how to use the .From() statement - When and how to use qf.Create() vs. qf.[EntityName]

If you can help me directly please let me know, and if you can sort this for me on the forum, great as well!

Thanks for bearing with me.

gabrielk wrote:

Hi,

I just seem to not get QuerySpec (yet). I've done some relatively simple queries now, but when I tried to do something with a subquery I just couldn't bring it togerther from the manual, examples and common sense. I hope someone can help me with the SQL query below (and also see the approach I've taken to convert it to QuerySpec).

If someone has the time I would also welcome 1 or 2 hours of paid for consultancy to get me on track, as I've now spent too much time trying it myself.

Feel like a real LLBLGen n00b again...

The SQL Query:

select ProductName, sum(Revenue) as TotalRevenue from 
    (
        select 
            Orderitem.ProductName,      
            Orderitem.Quantity * 
                (
                    Orderitem.ProductPriceIn + 
                    (
                        select sum(OrderitemAlterationitem.AlterationoptionPriceIn) 
                        from OrderitemAlterationitem 
                        where OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId
                    )
                ) as Revenue
        from Orderitem
        left join [Order] on [Order].OrderId = Orderitem.OrderId
        where [Order].CompanyId = 199
    ) as CalculatedOrderItems
group by ProductName
order by TotalRevenue desc

Attempt to build it in QuerySpec (I've tried quite some others as well):

                TypedListDAO dao = new TypedListDAO();
                var qf = new QueryFactory();
                DynamicQuery query2 = qf.Create()
                    .Select(OrderitemFields.ProductName, qf.Field("Revenue").Sum().As("TotalRevenue"))
                    .From(qf.Create()
                            .Select(OrderitemFields.ProductName, (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum())
                                ))))
                    .GroupBy(OrderitemFields.ProductName)
                    .As("G"); 

Result: An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a DynamicQuery to a String.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It feels like I fail to understand: - How to link subqueries to parent queries

The concept is called 'correlated subqueries', which is what you use in the SQL query as well. They're 'correlated' due to the where clause where OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId

To be able to tie a correlated/subquery to an outer query in queryspec you use 'CorrelatedOver' which is a method to specify precisely the where predicate you have in your sql query. So you did this just fine in your query. So when you specify a subquery somewhere and the results of that subquery are to be filtered based on rows in the outer query, you have a 'correlated subquery' and you specify the CorrelatedOver call on the subquery to specify the where predicate.

• When and how to use the .From() statement

From is required if you need to specify a join, or a source which is different from the target in the fields of the Select. For entity queries (see below) you don't to specify From, unless you need to join, as the entity query already contains the target (the targets of the entity the query is for). For dynamic queries, a From is required if you need a join, or the source is unclear based on the projection in Select or e.g. you have a query you want to use as the source, like you're doing here.

• When and how to use qf.Create() vs. qf.[EntityName]

qf.<entityName> creates an entity query, which is usable to fetch entities in a collection. It can also be used as a source of a dynamic query. A dynamic query is created with qf.Create() which simply creates an empty query object and you then build the query with select, from (if required), order by etc.

Dynamic queries result in List<object[]> or List<type> instances, not in entities. The modern form of a dynamic list with a projection.

I'll now look at your query as it's indeed rather complicated. I think you need one of the scalar query helpers. Scalar queries are indeed a bit problematic.

I think this is the problem:

This code:

OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum())

Will not work as '+' expects a field or expression but you specify a query (one which can potentially result in multiple values). To make it work, make it a scalar query:

OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()

this will result in a ScalarQueryExpression on the right side which is an expression and will then result in a database expression.

Thanks for the response.

I've tried as you suggested, which made me end up with:

                TypedListDAO dao = new TypedListDAO();
                var qf = new QueryFactory();
                DynamicQuery query = qf.Create()
                    .Select(OrderitemFields.ProductName, qf.Field("Revenue").Sum().As("TotalRevenue"))
                    .From(qf.Create()
                            .Select(OrderitemFields.ProductName, (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()
                                )).As("Revenue")))
                    .GroupBy(OrderitemFields.ProductName)
                    .As("G");
                DataTable result = dao.FetchAsDataTable(query);

Unfortunately still resulting in an exception: 'An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a ScalarQueryExpression to a String.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.'

Dynamic Query.ToString:

{SELECT
    With projection: 
        Field: OrderitemEntity.ProductName
        Field: .Revenue As TotalRevenue. Aggregate: Sum

FROM
    RelationCollection:
        DerivedTableDefinition: LPAA_2
            Field count: 2
            PredicateExpression:
            Field: OrderitemEntity.ProductName
            Field: .Revenue
                Expression: (Expression)
                    Left operand:
                        Field: OrderitemEntity.Quantity
                    Operator: Mul
                    Right operand:
                        Expression: (Expression)
                            Left operand:
                                Field: OrderitemEntity.ProductPriceIn
                            Operator: Add
                            Right operand:

GROUP BY
    GroupByCollection:
        Field: OrderitemEntity.ProductName

Additional information:
    Offset: 0
    Alias: 'G'
    CacheResultset: False
}
    base {SD.LLBLGen.Pro.QuerySpec.QuerySpec}: {SELECT
    With projection: 
        Field: OrderitemEntity.ProductName
        Field: .Revenue As TotalRevenue. Aggregate: Sum

FROM
    RelationCollection:
        DerivedTableDefinition: LPAA_2
            Field count: 2
            PredicateExpression:
            Field: OrderitemEntity.ProductName
            Field: .Revenue
                Expression: (Expression)
                    Left operand:
                        Field: OrderitemEntity.Quantity
                    Operator: Mul
                    Right operand:
                        Expression: (Expression)
                            Left operand:
                                Field: OrderitemEntity.ProductPriceIn
                            Operator: Add
                            Right operand:

GROUP BY
    GroupByCollection:
        Field: OrderitemEntity.ProductName

Additional information:
    Offset: 0
    Alias: 'G'
    CacheResultset: False
}

And an attachment of the exception.

Hmmm. The scalar query expression should be converted and inlined. It's now sent as-is to the db which is of course wrong (as it ends up as a parameter and thus fails). I'll see if I can reproduce it and if so, find a fix for it. Will be tomorrow (wednesday) though.

(edit) Reproduced. Will look into this tomorrow (wednesday)

[Test]
public void ScalarQueryInExpressionTest()
{
    var qf = new QueryFactory();
    var q = qf.Create()
                .Select(OrderFields.OrderId,
                            (OrderFields.OrderId +
                                qf.OrderDetail
                                        .CorrelatedOver(OrderFields.OrderId.Equal(OrderDetailFields.OrderId))
                                        .Select((OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).Sum()).ToScalar()).As("Foo"))
                .GroupBy(OrderDetailFields.OrderId);

    var results = new DataAccessAdapter().FetchQuery(q);
    Assert.AreEqual(818, results.Count);
    foreach(var v in results)
    {
        Assert.IsNotNull(v[0]);
    }
}

Hi,

Thanks a lot for looking in to this, and great that you've found the cause.

Looking forward to see a fix!

(edit) removed some nonsense.

Original post snippet: So the fix is to specify the expression explicitly:

[Test]
public void ScalarQueryInExpressionTest()
{
    var qf = new QueryFactory();
    var q = qf.Create()
                .Select(OrderFields.OrderId,
                            (new Expression(OrderFields.OrderId, ExOp.Add, 
                                qf.OrderDetail
                                        .CorrelatedOver(OrderFields.OrderId.Equal(OrderDetailFields.OrderId))
                                        .Select((OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).Sum()).ToScalar()).As("Foo")))
                .GroupBy(OrderFields.OrderId);

    Console.WriteLine(q.ToString());

    var results = new DataAccessAdapter().FetchQuery(q);
    Assert.AreEqual(818, results.Count);
    foreach(var v in results)
    {
        Assert.IsNotNull(v[0]);
    }
}

Here, the subquery is passed as the right operator of the expression and the Add operator, and this time it is resulting in a valid expression and therefore will result in a valid query.

See next post, that it's odd this happens. Looking into it. It is likely related to operator overloading (The '+' op) but it's still odd...

On second thought, what you wrote initially should work too. So I'm still looking into why it didn't work...

It's indeed related to operator overloading. The thing is that with the explicit new Expression(...) the ctor which knows the right side is an IExpression is called by the compiler. However with the '+' one, the right operand (the scalar query) is typed 'object', and the ctor for Expression which sees the right side as a value is called, and that one will not look further and accept the value as-is.

This of course can be done better so I'll fix this.

Fixed in next build (released later today)

I've just updated to that release (4.1.14.521)

Ran this again:

                TypedListDAO dao = new TypedListDAO();
                var qf = new QueryFactory();
                DynamicQuery query = qf.Create()
                    .Select(OrderitemFields.ProductName, qf.Field("Revenue").Sum().As("TotalRevenue"))
                    .From(qf.Orderitem
                            .Select(OrderitemFields.ProductName, (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()
                                )).As("Revenue")))
                    .GroupBy(OrderitemFields.ProductName)
                    .As("G");
                DataTable result = dao.FetchAsDataTable(query);

I am probably still missing something, but since you said my code should have worked as well, I ran that code.

Unfortunately an exception again: An exception was caught during the execution of a retrieval query: The multi-part identifier "DatebaseName.dbo.Orderitem.ProductName" could not be bound. The multi-part identifier "DatebaseName.dbo.Orderitem.ProductName" could not be bound. The multi-part identifier ".Revenue" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

what's the query being generated?

Thanks for still bearing with me.

(Sorry for me getting lazy by only posting the exception)

The query from that code was:

SELECT  
    [DatabaseName].[dbo].[Orderitem].[ProductName], 
    SUM(.[Revenue]) AS [TotalRevenue] 
FROM 
    (
        SELECT [DatabaseName].[dbo].[Orderitem].[ProductName], 
                (
                    [DatabaseName].[dbo].[Orderitem].[Quantity] * 
                        (
                            [DatabaseName].[dbo].[Orderitem].[ProductPriceIn] + 
                                (
                                    SELECT 
                                        SUM([DatabaseName].[dbo].[OrderitemAlterationitem].[AlterationoptionPriceIn]) AS [AlterationoptionPriceIn] 
                                    FROM 
                                        [DatabaseName].[dbo].[OrderitemAlterationitem]  
                                    WHERE 
                                        ( 
                                            ( 
                                                [DatabaseName].[dbo].[OrderitemAlterationitem].[OrderitemId] = [DatabaseName].[dbo].[Orderitem].[OrderitemId]
                                            )
                                        )
                                )
                            )
                    ) AS [Revenue] 
            FROM 
                [DatabaseName].[dbo].[Orderitem]  
    ) [LPA_L1] 
    GROUP BY [DatabaseName].[dbo].[Orderitem].[ProductName]

Which I corrected manually to: - ProductName to use 'as'

- Use [ProductName] in the outer query instead of [DatabaseName].[dbo].[Orderitem].[ProductName] (both in SELECT and GROUP BY)

SELECT  
    [ProductName], 
    SUM([Revenue]) AS [TotalRevenue] 
FROM 
    (
        SELECT [DatabaseName].[dbo].[Orderitem].[ProductName] as [ProductName], 
                (
                    [DatabaseName].[dbo].[Orderitem].[Quantity] * 
                        (
                            [DatabaseName].[dbo].[Orderitem].[ProductPriceIn] + 
                                (
                                    SELECT 
                                        SUM([DatabaseName].[dbo].[OrderitemAlterationitem].[AlterationoptionPriceIn]) AS [AlterationoptionPriceIn] 
                                    FROM 
                                        [DatabaseName].[dbo].[OrderitemAlterationitem]  
                                    WHERE 
                                        ( 
                                            ( 
                                                [DatabaseName].[dbo].[OrderitemAlterationitem].[OrderitemId] = [DatabaseName].[dbo].[Orderitem].[OrderitemId]
                                            )
                                        )
                                )
                            )
                    ) AS [Revenue] 
            FROM 
                [DatabaseName].[dbo].[Orderitem]  
    ) [LPA_L1] 
    GROUP BY [ProductName]

To sort of replicate that in code I did: - Alias the subquery - Alias the ProductName field in the subquery - Use aliassed fields in the outer query (SELECT and GROUP BY)

New code:

 DynamicQuery query = qf.Create()
                    .Select(qf.Field("sub", "ProductName"), qf.Field("sub", "Revenue").Sum().As("TotalRevenue"))
                    .From(qf.Orderitem
                            .Select(OrderitemFields.ProductName.As("ProductName"), (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()
                                )).As("Revenue")).As("sub"))
                    .GroupBy(qf.Field("sub", "ProductName"))                    
                    .As("G");

Resulting query which did work!

SELECT 
    [LPA_s1].[ProductName], 
    SUM([LPA_s1].[Revenue]) AS [TotalRevenue] 
FROM 
    (
        SELECT [DatabaseName].[dbo].[Orderitem].[ProductName], 
            (
                [DatabaseName].[dbo].[Orderitem].[Quantity] * 
                (
                    [DatabaseName].[dbo].[Orderitem].[ProductPriceIn] + 
                        (
                            SELECT 
                                SUM([DatabaseName].[dbo].[OrderitemAlterationitem].[AlterationoptionPriceIn]) AS [AlterationoptionPriceIn] 
                            FROM 
                                [DatabaseName].[dbo].[OrderitemAlterationitem]  
                            WHERE 
                                ( 
                                    ( 
                                        [DatabaseName].[dbo].[OrderitemAlterationitem].[OrderitemId] = [DatabaseName].[dbo].[Orderitem].[OrderitemId]
                                    )
                                )
                            )
                )
            ) AS [Revenue] 
        FROM 
            [DatabaseName].[dbo].[Orderitem]  
    ) [LPA_s1] 
    GROUP BY [LPA_s1].[ProductName]

Then I wanted to add the last bit to this, an OrderBy.

I tried to options:

.OrderBy(qf.Field("TotalRevenue").Descending())

Resulting in:

ORDER BY .[TotalRevenue] DESC

And

.OrderBy(qf.Field("sub", "TotalRevenue").Descending())

Resulting in:

ORDER BY [LPA_s1].[TotalRevenue] DESC

(Which, I must be honest, I would have thought is correct SQL - as it does work on the GROUP BY in that naming way)

And

.OrderBy(qf.Field("G", "TotalRevenue").Descending())

Resulting in:

ORDER BY [G].[TotalRevenue] DESC

While the correct SQL had to be:

ORDER BY [TotalRevenue] DESC

The last attempt I tried is aliasing the SELECT part of the outer query, resulting in:

                DynamicQuery query = qf.Create()
                    .Select(qf.Field("sub", "ProductName"), qf.Field("sub", "Revenue").Sum().As("TotalRevenue")).As("G")
                    .From(qf.Orderitem
                            .Select(OrderitemFields.ProductName.As("ProductName"), (OrderitemFields.Quantity *
                                (
                                    OrderitemFields.ProductPriceIn +
                                    qf.OrderitemAlterationitem
                                                .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()
                                )).As("Revenue")).As("sub"))
                    .GroupBy(qf.Field("sub", "ProductName"))
                    .OrderBy(qf.Field("G", "TotalRevenue").Descending());   

Which resulted in this query, also not working:

SELECT 
    [LPA_s1].[ProductName], 
    SUM([LPA_s1].[Revenue]) AS [TotalRevenue] 
FROM 
    (
        SELECT 
            [DatabaseName].[dbo].[Orderitem].[ProductName], 
            (
                [DatabaseName].[dbo].[Orderitem].[Quantity] * 
                (
                    [DatabaseName].[dbo].[Orderitem].[ProductPriceIn] + 
                        (
                            SELECT 
                                SUM([DatabaseName].[dbo].[OrderitemAlterationitem].[AlterationoptionPriceIn]) AS [AlterationoptionPriceIn] 
                            FROM 
                                [DatabaseName].[dbo].[OrderitemAlterationitem]  
                            WHERE 
                                ( 
                                    ( 
                                        [DatabaseName].[dbo].[OrderitemAlterationitem].[OrderitemId] = [DatabaseName].[dbo].[Orderitem].[OrderitemId]
                                    )
                                )
                            )
                        )
                ) 
            AS [Revenue] 
        FROM 
            [DatabaseName].[dbo].[Orderitem]  
    ) 
[LPA_s1] 
GROUP BY [LPA_s1].[ProductName] 
ORDER BY [G].[TotalRevenue] DESC

I hope you can give me some hints on who to achieve this last hurdle for this query.

.OrderBy(qf.Field("sub", "TotalRevenue").Descending());

doesn't work indeed, as it orders on the source, you need to order on the projection.

.OrderBy(qf.Field("sub", "Revenue").Sum().As("TotalRevenue").Descending())

should do it, it's replicating the expression. I fixed the '.' issue in v4.2, so "TotalRevenue".Descending() works there. If the line above doesn't fix it, I'll see if I can backport that change to v4.1

Thanks again for all the help.

The query I posted in this thread was a basic query that I wanted to incorporate in other queries that I already had in my code. I thought if I understand this I will be able to apply this to my other queries, but I simply don't seem to get this. This doesn't happen a lot; but I'm defeated. I can't justify investing anymore time in to using QuerySpec from an economic point of view. (Even though, as a developer I don't want to give up, but it's been a couple of days now for something that I can do using SQL in under an hour)

Since it's costing me so much time to understand this I am afraid any development in the future by myself and/or by other developers to maintain this will be hard. So I will restort back to doing 'manual' queries on the database by incorporating those queries as strings in my code. Again, I would be happy to pay for half a day of consulting to work with QuerySpec as it really seems to be the tool to use (I am not a fan at all of putting string queries in my code) - but with my current understanding I just can't work with this.

This isn't a rant or complaint, support has been great as seen in this thread, but I just really need someone sitting next to me to explain this for all the different queries and scenario's that I have (we're located close to the LLLBGen office ).

For your information below the queries that I had already done that need this subquery to be able to add the revenue of the Alterations on the Orderitems as well. It's the next level of complexity (Join and Where clauses) that I just don't know where and how to implement.

The one that I tried was 'Order Revenue per Deliverypointgroup and Order Type'

Which I got to this point, which seemed quite bulky to me anyway, but then when I tried to run it I got: 'Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?'.

If you find that you have invested enough time in this thread and us I'll understand, just say so and I will know the only option I have is SQL in strings in my code.

                DynamicQuery query = qf.Create()
                    .Select(
                        qf.Field("sub", ColumnNames.DeliverypointgroupId).As(ColumnNames.DeliverypointgroupId),
                        qf.Field("sub", ColumnNames.DeliverypointgroupName).As(ColumnNames.DeliverypointgroupName),
                        qf.Field("sub", ColumnNames.OrderTypeAlias).As(ColumnNames.OrderTypeAlias),
                        qf.Field("sub", "Revenue").Sum().As(ColumnNames.OrderitemRevenueAlias),
                        qf.Field("sub", ColumnNames.OrderitemQuantityAlias).As(ColumnNames.OrderitemQuantityAlias)
                    )
                    .From((qf.Orderitem
                            .Select(
                                DeliverypointgroupFields.DeliverypointgroupId.As(ColumnNames.DeliverypointgroupId),
                                DeliverypointgroupFields.Name.As(ColumnNames.DeliverypointgroupName),
                                OrderFields.Type.As(ColumnNames.OrderTypeAlias),
                                (
                                    OrderitemFields.Quantity *
                                    (
                                        OrderitemFields.ProductPriceIn +
                                        qf.OrderitemAlterationitem
                                                    .CorrelatedOver(OrderitemAlterationitemEntity.Relations.OrderitemEntityUsingOrderitemId)
                                                    .Select(OrderitemAlterationitemFields.AlterationoptionPriceIn.Sum()).ToScalar()
                                    )
                                ).As("Revenue"),
                                OrderitemFields.Quantity.Sum().As(ColumnNames.OrderitemQuantityAlias)
                            ).As("sub"))
                            .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                            .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId)
                            .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                            .LeftJoin(DeliverypointEntity.Relations.DeliverypointgroupEntityUsingDeliverypointgroupId)
                        )
                    .GroupBy(qf.Field("sub", ColumnNames.DeliverypointgroupId), qf.Field("sub", ColumnNames.DeliverypointgroupName), qf.Field("sub", ColumnNames.OrderTypeAlias));

I would be happy if you could help me with the ones below, but I think (since you can't test it and we have hit some bumps before) it will cost quite a bit of more time, as the code below are excerpts from my code, some additional filtering is also required.

Order Revenue per Deliverypointgroup and Order Type

            var queryFactory = new QueryFactory();
            DynamicQuery query = queryFactory.Create()
                .Select(DeliverypointgroupFields.DeliverypointgroupId.As(ColumnNames.DeliverypointgroupId), DeliverypointgroupFields.Name.As(ColumnNames.DeliverypointgroupName), 
                        OrderFields.Type,   
                        (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As(DeliverypointgroupStatsTable.OrderRevenueAlias), 
                        OrderitemFields.Quantity.Sum()) /* THIS NEEDS TO BE REPLACED */                     
                .From(queryFactory.Orderitem
                    .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                    .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId)
                    .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                    .LeftJoin(DeliverypointEntity.Relations.DeliverypointgroupEntityUsingDeliverypointgroupId))
                .GroupBy(DeliverypointgroupFields.DeliverypointgroupId, DeliverypointgroupFields.Name, OrderFields.Type)
                .OrderBy(DeliverypointgroupFields.Name.Ascending(), OrderFields.Type.Ascending());

Revenue based on Product/Category combinations

                query.Select(CompanyFields.CompanyId, CompanyFields.Name.As(ColumnNames.CompanyName), MenuFields.MenuId.As(ColumnNames.MenuId), MenuFields.Name.As(ColumnNames.MenuName),
                    CategoryFields.CategoryId.As(ColumnNames.CategoryId), ProductFields.ProductId.As(ColumnNames.ProductId),
                                (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As(ColumnNames.TotalRevenue) /* THIS NEEDS TO BE REPLACED */,
                                OrderitemFields.Quantity.Sum().As(ColumnNames.TotalQuantity))
                    .From(queryFactory.Orderitem
                                .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                                .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                                .LeftJoin(OrderitemEntity.Relations.ProductEntityUsingProductId)
                                .LeftJoin(OrderitemEntity.Relations.CategoryEntityUsingCategoryId)
                                .LeftJoin(CategoryEntity.Relations.MenuEntityUsingMenuId)
                                .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId))
                    .GroupBy(CompanyFields.CompanyId, CompanyFields.Name, MenuFields.MenuId, MenuFields.Name, CategoryFields.CategoryId, ProductFields.ProductId)
                    .OrderBy(CompanyFields.Name.Ascending(), MenuFields.Name.Ascending());

TopX stats per Product

            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) /* THIS NEEDS TO BE REPLACED */,
                            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)
                .Limit(maxResults);

TopX stats per Category

            query.Select(CompanyFields.CompanyId, CompanyFields.Name.As("Company Name"), CategoryFields.Name, CategoryFields.CategoryId,
                            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.CategoryEntityUsingCategoryId)
                            .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId))
                .Where(OrderFields.Type == (int)orderType)
                .GroupBy(CompanyFields.CompanyId, CompanyFields.Name, CategoryFields.CategoryId, CategoryFields.Name)
                .Limit(maxResults);

(Feel free to close the thread, but since it wasn't closed yet a little update)

Just found out that my own query also contained a flaw, making it yet another bit more challenging - an ISNULL function.

If an Orderitem doesn't have any Alterations than it's revenue wouldn't be added as it's plussing a 'null' value giving 0. So I actually need:

select ProductName, sum(Revenue) as TotalRevenue from 
    (
        select 
            Orderitem.ProductName,      
            Orderitem.Quantity * 
                (
                    Orderitem.ProductPriceIn + 
                    ISNULL((
                        select sum(OrderitemAlterationitem.AlterationoptionPriceIn) 
                        from OrderitemAlterationitem 
                        where OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId
                    ), 0)
                ) as Revenue
        from Orderitem
        left join [Order] on [Order].OrderId = Orderitem.OrderId
        where [Order].CompanyId = 199
    ) as CalculatedOrderItems
group by ProductName
order by TotalRevenue desc

gabrielk wrote:

Thanks again for all the help.

The query I posted in this thread was a basic query that I wanted to incorporate in other queries that I already had in my code. I thought if I understand this I will be able to apply this to my other queries, but I simply don't seem to get this. This doesn't happen a lot; but I'm defeated. I can't justify investing anymore time in to using QuerySpec from an economic point of view. (Even though, as a developer I don't want to give up, but it's been a couple of days now for something that I can do using SQL in under an hour)

Queryspec is actually very close to SQL. If you take the SQL you need to write, you can almost 1:1 translate it back to queryspec. The things you run into with your query here were roadblocks which were either a bug (fixed) or not yet supported (while it should have). But the more complex things take some time, like it does with SQL but also with e.g. Linq or our own lower level API. Writing aggregate queries in Linq is also challenging at first, but after a while you get it, same with our own (verbose) api. You don't have to use queryspec if it's too complex for you, feel free to write the query in Linq or our lower level API, you can have your queries written in any api, one in linq the other in queryspec, that doesn't matter.

Since it's costing me so much time to understand this I am afraid any development in the future by myself and/or by other developers to maintain this will be hard. So I will restort back to doing 'manual' queries on the database by incorporating those queries as strings in my code. Again, I would be happy to pay for half a day of consulting to work with QuerySpec as it really seems to be the tool to use (I am not a fan at all of putting string queries in my code) - but with my current understanding I just can't work with this.

Below I've tried to explain some things, which might clear things up a bit. I know it can be frustrating, though as mapping to sql is rather hard, it unfortunately is a learning curve you have to take. Feel free to write the query in the lower level API or linq if you want to, all 3 (lower level API, Linq and queryspec) will stay in the future.

For your information below the queries that I had already done that need this subquery to be able to add the revenue of the Alterations on the Orderitems as well. It's the next level of complexity (Join and Where clauses) that I just don't know where and how to implement.

I see you do the joins using relation objects. It might be easier to do it really using LeftJoin(<entity query>).On(<predicate>) style, as you then might see the resemblance with sql.

The one that I tried was 'Order Revenue per Deliverypointgroup and Order Type'

Which I got to this point, which seemed quite bulky to me anyway, but then when I tried to run it I got: 'Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?'.

The reason for this is the same as it would have been in SQL itself:

// derived table in FROM
).As("sub"))
   .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)

Here you define: sub LEFT JOIN OrderItemEntity however you use a relation object (which is between entities) and you don't specify an alias for one side on the relation object, but this is wrong as one side (sub) is aliased (namely 'sub') so it doesn't know to what it has to join the relation object to.

In SQL you have to write the ON clause of the LEFT join for this part, and in queryspec you should too. This immediately shows you can't make the join, as there's no OrderId in the projection (SELECT list) of 'sub'. So you have to add OrderitemFields.OrderId to the Select in sub. In SQL you would have had to do that as well.

I'll give some examples, which might give you some insight:

var q = qf.Customer;

means in SQL SELECT customerfield1, customerfield2, ..., customerfieldn -- so all the customer fields. FROM Customer

var q = qf.Customer.Select(CustomerFields.CustomerField1)

means in SQL SELECT customerfield1 FROM Customer

Because you defined a new projection (select) onto Customer. This is also the reason only the fields you specified in the Select in the query 'sub' are there, no OrderId.

I also see you alias every field, you don't have to, unless you want predictable aliases. So code like: DeliverypointgroupFields.DeliverypointgroupId.As(ColumnNames.DeliverypointgroupId) is unnecessarily verbose. The field will be aliased as DeliverypointgroupId.

After I've added OrderitemFields.OrderId to sub's Select call, I can now rewrite the first Left join:

**instead of **

// definition of subquery in From
).As("sub"))
   .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)

do

// definition of subquery in From
).As("sub")
   .LeftJoin(qf.Order).On(OrderitemFields.OrderId.Source("sub").Equal(OrderFields.OrderId))

First I specify what I want to join sub with: qf.Order. This means I want to join with the entity Order. Then I have to specify the ON clause. I do that by calling .On() and specifying a predicate (which is equal to what you do in SQL).

The one thing to pay attention to is .Source(<alias>). This specifies the source of the field. Normally it would the Orderitem entity here, but as the left side of the join statement is a derived table (aliased as 'sub'), I have to specify an aliased element instead, i.e. 'sub').

The rest of the joins are straight forward:

                    .LeftJoin(qf.Deliverypoint).On(OrderFields.DeliverypointId.Equal(DeliverypointFields.DeliverypointId))
                    .LeftJoin(qf.CompanyEntity).On(OrderFields.CompanyId.Equal(CompanyFields.CompanyId))
                    .LeftJoin(qf.DeliverypointGroup).On(DeliverypointFields.DeliverypointgroupId.Equal(DeliverypointgroupFields.DeliverypointgroupId))

(I guessed the fk/pk names) Here you see a pattern: <leftside>.LeftJoin(<rightside: entity to join with>).On(<leftside field>.Equal(<rightside field>))

same as with SQL. As with sql: you have to specify the source if the source is aliased.

Hopefully this is a bit clear now.

rest looks OK.

I would be happy if you could help me with the ones below, but I think (since you can't test it and we have hit some bumps before) it will cost quite a bit of more time, as the code below are excerpts from my code, some additional filtering is also required.

Order Revenue per Deliverypointgroup and Order Type

            var queryFactory = new QueryFactory();
            DynamicQuery query = queryFactory.Create()
                .Select(DeliverypointgroupFields.DeliverypointgroupId.As(ColumnNames.DeliverypointgroupId), DeliverypointgroupFields.Name.As(ColumnNames.DeliverypointgroupName), 
                        OrderFields.Type,   
                        (OrderitemFields.ProductPriceIn * OrderitemFields.Quantity).Sum().As(DeliverypointgroupStatsTable.OrderRevenueAlias), 
                        OrderitemFields.Quantity.Sum()) /* THIS NEEDS TO BE REPLACED */                     
                .From(queryFactory.Orderitem
                    .LeftJoin(OrderitemEntity.Relations.OrderEntityUsingOrderId)
                    .LeftJoin(OrderEntity.Relations.DeliverypointEntityUsingDeliverypointId)
                    .LeftJoin(OrderEntity.Relations.CompanyEntityUsingCompanyId)
                    .LeftJoin(DeliverypointEntity.Relations.DeliverypointgroupEntityUsingDeliverypointgroupId))
                .GroupBy(DeliverypointgroupFields.DeliverypointgroupId, DeliverypointgroupFields.Name, OrderFields.Type)
                .OrderBy(DeliverypointgroupFields.Name.Ascending(), OrderFields.Type.Ascending());

What is the exact issue here, besides the joins can be done better?

<field>.Sum() will simply result in SUM(<field>) As <field alias>

If it should be that way in your SQL query, you can define it like that in the query spec query.

With the other queries I too can't pinpoint you to what's wrong as I don't know what goes wrong when you run them.

Does this help a bit?

About the ISNULL() call, in sql you normally would use COALESCE().

So do: Functions.Coalesce(( select sum(OrderitemAlterationitem.AlterationoptionPriceIn) from OrderitemAlterationitem where OrderitemAlterationitem.OrderitemId = Orderitem.OrderitemId ), 0)

Which is what you'd have written in SQL as well

I've already done it with string in the code, but I will try this as well. Will let you know if I succeeded.

Thanks.