Hello!
Been dealing with same kind of things recently :).
I know lamdaExtension but I try to show you how without using it. You
could easliy translate parts of it on your own.
//This is one way how you declare a sqlfunction to use with criteria.
ISQLFunction arithmaticMultiply = new VarArgsSQLFunction("(", "*",
")");
PropertyProjection q = Projections.Property("Quantity");
PropertyProjection p = Projections.Property("Price");
PropertyProjection s = Projections.GroupProperty("SKU");
var sum = Projections.Sum(Projections.SqlFunction(arithmaticMultiply,
NHibernateUtil.Int32, q, p));
DetachedCriteria.For<OrderItem>()
.SetProjection(Projections.ProjectionList()
.Add(sum).As("sumAlias")
.Add(s))
.AddOrder(Order.Desc("sumAlias"))
.SetMaxResults(5);
Will this work for you?
On 20 Juni, 23:40, Martin Nilsson <[email protected]> wrote:
> Not solved it yet but this is how far as I get:
>
> var skus = DetachedCriteria
> .For<OrderItem>()
> .SetProjection(Projections.ProjectionList()
>
> .Add(LambdaProjection.Alias(LambdaProjection.Sum<OrderItem>(oi => oi.Price),
> () => sumAlias))
>
> .Add(LambdaProjection.GroupProperty<OrderItem>(oi => oi.SKU)))
> .AddOrder(Order.Desc("sumAlias"))
> .SetMaxResults(5);
>
> from this I get a list of object[2] where object is {sumAlias, SKU}.
>
> I need help to
> 1. Just get the SKU and not sumAlias in the list
> 2. Do a Sum of (price * qty), something like .Sum<OrderItem>(oi => oi.Price
> * oi.Quantity). If I do this I get error:
> "Could not determine member from (oi.Price * Convert(oi.Quantity))"
>
> Could someone please help or guide me in the right direction?
>
> On Fri, Jun 19, 2009 at 11:24 AM, Martin Nilsson <[email protected]>wrote:
>
> > Need help to write this sql query as a DetachedCriteria. Could anyone
> > please help? Thanks!
>
> > SELECT TOP 5 oi.SKU, sum(oi.Quantity * oi.Price) total
> > FROM OrderItem oi
> > GROUP BY oi.SKU
> > ORDER BY total DESC
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
