I just tried, the projection merely return the id of AbstractProduct,
no other properties.
I also tried this way:
var criteria = Session.CreateCriteria(typeof(Price), "pr")
.Add(Restrictions.Eq("pr.Quantity", 1m))
.Add(Restrictions.Or(Restrictions.Gt("pr.Expires",
DateTime.Now),
Restrictions.IsNull("pr.Expires")))
.AddOrder(global::NHibernate.Criterion.Order.Asc("pr.Amount"))
.CreateCriteria("AbstractProduct")
.List<Price>();
it returns a List of Prices which AbstractProduct has been fetched
eagerly,
then use C# to do the remaining convertion.
Thanks!
On Jul 7, 6:23 pm, Tuna Toksoz <[email protected]> wrote:
> .SetProjection(Projections.Property("pr.AbstractProduct"))
>
> hmm this may not work... Can you try? As far as I remember, there is no way
> to project on entity.
>
> Tuna Toksöz
> Eternal sunshine of the open source mind.
>
> http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.com/tehlike
>
> On Tue, Jul 7, 2009 at 11:46 AM, Tuna Toksoz <[email protected]> wrote:
> > select pr.AbstractProduct from Price pr where pr.Quantity = 1 and
> > (pr.Expires > :expire or pr.Expires is null) order by pr.Amount
>
> > This HQL?
> > yes.
>
> > Gmail code
>
> > var criteria=session.CreateCriteria(typeof(Price),"pr")
> > .Add(Restrictions.Eq("pr.Quantity",1))
> > .Add(
> > Restrictions.Or(
>
> >
> > Restrictions.Gt("pr.Expires",theDate),
>
> >
> > Restrictions.IsNull("pr.Expires")))
> > .AddOrder(Order.Asc("pr.Amount"))
>
> > Tuna Toksöz
> > Eternal sunshine of the open source mind.
>
> >http://devlicio.us/blogs/tuna_toksoz
> >http://tunatoksoz.com
> >http://twitter.com/tehlike
>
> > On Tue, Jul 7, 2009 at 4:45 AM, zhibin <[email protected]> wrote:
>
> >> I used "explain" in postgresql to see the query plans. It's done on a
> >> very small data set.
>
> >> The 2 SQL statement I mentioned above generate same query plan.
> >> Sort (cost=8494.29..8495.19 rows=360 width=40)
> >> Sort Key: ((subplan))
> >> -> Seq Scan on "AbstractProduct" this_ (cost=0.00..8479.00
> >> rows=360 width=40)
> >> SubPlan
> >> -> Aggregate (cost=23.50..23.52 rows=1 width=18)
> >> -> Seq Scan on "Price" this_0_ (cost=0.00..23.50
> >> rows=1 width=18)
> >> Filter: ((abstractproduct_id = $0) AND (quantity
> >> = 1::numeric))
>
> >> the query plan of Fabio's approach:
>
> >> Sort (cost=36.33..36.34 rows=4 width=209)
> >> Sort Key: price0_.amount
> >> -> Hash Join (cost=21.30..36.29 rows=4 width=209)
> >> Hash Cond: (abstractpr1_.id = price0_.abstractproduct_id)
> >> -> Seq Scan on "AbstractProduct" abstractpr1_
> >> (cost=0.00..13.60 rows=360 width=191)
> >> -> Hash (cost=21.25..21.25 rows=4 width=26)
> >> -> Seq Scan on "Price" price0_ (cost=0.00..21.25
> >> rows=4 width=26)
> >> Filter: (quantity = 1::numeric)
>
> >> look like much better as of performance.
>
> >> The only drawback is: each product possibly have multiple unit prices
> >> (with different Expires). But this can be handled in the C# side.
>
> >> Thanks very much!
>
> >> and is it possible to write the HQL with Criteria API?
>
> >> On Jul 6, 9:20 pm, Fabio Maulo <[email protected]> wrote:
> >> > select pr.AbstractProduct from Price pr where pr.Quantity = 1 and
> >> > (pr.Expires > :expire or pr.Expires is null) order by pr.Amount
>
> >> > 2009/7/6 zhibin <[email protected]>
>
> >> > > I have the following 2 classes:
>
> >> > > public abstract class AbstractProduct : Entity
> >> > > {
> >> > > public virtual ISet<Price> Prices { get; set; }
> >> > > }
>
> >> > > public class Price : Entity
> >> > > {
> >> > > public virtual AbstractProduct AbstractProduct { get; set; }
> >> > > public virtual decimal Amount { get; set; }
> >> > > public virtual decimal Quantity { get; set; }
> >> > > public virtual DateTime? Expires {get; set;}
> >> > > }
>
> >> > > I want to get products ordered by unit price. The SQL I supposed is:
> >> > > select p0.*, (select min(pr.amount) from "Price" pr
> >> > > where pr.abstractproduct_id=p0.id
> >> > > and pr.Quantity = 1
> >> > > and (pr.expires>'2009-7-1' or
> >> > > pr.expires is null)
> >> > > )as amount
> >> > > from "AbstractProduct" p0;
> >> > > order by amount;
>
> >> > > I tried it in Postgresql, it returns correct result. but I don't know
> >> > > how to code it with the Criteria API.
>
> >> > > Can anybody help me?
> >> > > Thanks!
>
> >> > --
> >> > Fabio Maulo
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
