Did it work?
On 21 Juni, 14:25, cws <[email protected]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
