it worked with Projections and it was fairly comparable performance to
original now.
DetachedCriteria contractIds = DetachedCriteria.For<Contract>()
.SetProjection(LambdaProjection.Property<Contract>(c => c.Id))
.Add<Contract>(c => c.StatusId > 7)
.Add<Contract>(c => c.StopDate > DateTime.Now)
.Add<Contract>(c => c.Unit.Id == unitId);
var queryCategories = _nHibernateSession.CreateCriteria<Category>()
.SetProjection(
Projections.ProjectionList()
.Add(LambdaProjection.Property<Category>(c => c.Id))
.Add(LambdaProjection.Property<Category>(c => c.Name))
.Add(LambdaProjection.Property<Category>(c => c.Code))
.Add(LambdaProjection.Property<Category>(c => c.LanguageKey))
)
.CreateCriteria("Contracts")
.Add(Subqueries.PropertyIn("Id", contractIds))
.SetResultTransformer(Transformers.AliasToBean(typeof(Category)) );
The SQL Generated:
SELECT this_.cat_id as y0_,
this_.cat_name as y1_,
this_.cat_code as y2_,
this_.lkey as y3_
FROM [Category] this_
inner join contract_category contracts3_
on this_.cat_id = contracts3_.category_id
inner join [Contract] contract1_
on contracts3_.contract_id = contract1_.contract_id
WHERE contract1_.contract_id in (SELECT this_0_.contract_id as y0_
FROM [Contract] this_0_
WHERE this_0_.status_contract_id >
7 /* @p0 */
and this_0_.stop_date >
'2010-08-24T00:19:30.00' /* @p1 */
and this_0_.unit_id = 8321 /*
@p2 */)
My only difficulty of this is that the Category objects the
Transformer creates are completely empty - all properties with value
null.
On Aug 23, 6:32 pm, Melborp <[email protected]> wrote:
> The only way ive found to get the result that i am aiming was to use
> next criteria building.
>
> DetachedCriteria contractIds = DetachedCriteria.For<Contract>()
> .SetProjection(LambdaProjection.Property<Contract>(c => c.Id))
> .Add<Contract>(c => c.StatusId > 7)
> .Add<Contract>(c => c.StopDate > DateTime.Now)
> .Add<Contract>(c => c.Unit.Id == unitId);
>
> DetachedCriteria categoryIds = DetachedCriteria.For<Category>()
> .SetProjection(LambdaProjection.Property<Category>(c => c.Id))
> .CreateCriteria("Contracts")
> .Add(Subqueries.PropertyIn("Id", contractIds));
>
> var queryCategories = _nHibernateSession.CreateCriteria<Category>()
> .Add(Subqueries.PropertyIn("Id", categoryIds));
>
> I think this could be done with one detatched criteria as well, maybe?
>
> The query created isents as nice and is rather expencive than the
> simple one i need.
>
> SELECT this_.cat_id as cat1_1_0_,
> this_.cat_name as cat2_1_0_,
> this_.cat_code as cat3_1_0_,
> this_.key as key1_0_
> FROM [Category] this_
> WHERE this_.cat_id in (SELECT this_0_.cat_id as y0_
> FROM [Category] this_0_
> inner join contract_category
> contracts3_
> on this_0_.cat_id =
> contracts3_.category_id
> inner join [Contract] contract1_
> on contracts3_.contract_id =
> contract1_.contract_id
> WHERE contract1_.contract_id in (SELECT
> this_0_0_.contract_id as y0_
> FROM
> [Contract] this_0_0_
> WHERE
> this_0_0_.status_id > 7 /* @p0 */
> and
> this_0_0_.stop_date > '2010-08-23T18:23:36.00' /* @p1 */
> and
> this_0_0_.unit_id = 8321 /* @p2 */))
>
> Im guessing if i would project all the category fields i could do it
> with 1 detatched criteria?
>
> Taavi
>
> On Aug 23, 3:17 pm, Melborp <[email protected]> wrote:
>
>
>
> > Hej,
>
> > I have been trying numerous versions of queries with projections.
> > Hasnt worked yet.
> > I getting a feeling Projections deosnt work with collections?
>
> > Taavi
>
> > On Aug 21, 10:35 am, Melborp <[email protected]> wrote:
>
> > > I should have added the result before. The query looked like:
>
> > > SELECT this_.contract_id as y0_
> > > FROM [Contract] this_
> > > inner join contract_category categories3_
> > > on this_.contract_id = categories3_.contract_id
> > > inner join [Category] categoryal1_
> > > on categories3_.category_id = categoryal1_.cat_id
> > > WHERE this_.unit_id = 8321 /* @p0 */
> > > and this_.status_contract_id > 7 /* @p1 */
> > > and this_.stop_date > '2010-08-21T10:33:15.00' /* @p2 */
>
> > > The result as you can read is the list of contract id-s.
>
> > > On 20 aug, 23:25, Chris J <[email protected]> wrote:
>
> > > > What does it return?
>
> > > > On Aug 20, 1:09 pm, Melborp <[email protected]> wrote:
>
> > > > > Hej Chris,
>
> > > > > Thank you for taking the time to answer.
>
> > > > > I was thinking about projections, but wasnt really sure how to apply
> > > > > it in this case.
>
> > > > > I tried your approach:
> > > > > var query = _nHibernateSession.CreateCriteria<Contract>()
> > > > > .Add<Contract>(c => c.Unit.Id == unitId)
> > > > > .Add<Contract>(c => c.StatusId > 7)
> > > > > .Add<Contract>(c => c.StopDate > DateTime.Now)
> > > > > .SetProjection(
> > > > > LambdaProjection.Property<Contract>(c =>
> > > > > c.Categories)
> > > > > );
>
> > > > > This doesnt return Contracts, but neither does it return any
> > > > > categories. Tried with aliases as well.
>
> > > > > Taavi
>
> > > > > On 20 aug, 19:58, Chris J <[email protected]> wrote:
>
> > > > > > Does it work query the relationships from the other direction? I
> > > > > > tried
> > > > > > building a similar query with my own domain model, but the
> > > > > > relationships weren't quite the same. Here's what I was thinking,
> > > > > > not
> > > > > > sure if it will work. This is with lambda extensions, but without
> > > > > > creating aliases.
>
> > > > > > var query = session.CreateCriteria<Contract>()
> > > > > > .Add<Contract>(c => c.Unit.Id == unitId)
> > > > > > .SetProjection(LambdaProjection.Property<Contract>(c =>
> > > > > > c.Categories));
>
> > > > > > On Aug 19, 3:04 pm, Melborp <[email protected]> wrote:
>
> > > > > > > The subject is confusing, i couldnt figure out a way to ask it in
> > > > > > > clear english. I do have a sample code which will clear it up.
>
> > > > > > > Imagine having three entities:
> > > > > > > Category
> > > > > > > Contract
> > > > > > > Unit
>
> > > > > > > Contract and Category have a many to many relationship.
> > > > > > > Contract also has one-to-one with Unit.
>
> > > > > > > My interest is to get all categories that specific unit contracts
> > > > > > > have. A Criteria could be described as next:
>
> > > > > > > var query = _nHibernateSession.CreateCriteria<Category>("cat")
> > > > > > > .CreateAlias("cat.Contracts", "contract")
> > > > > > > .Add(Expression.Eq("contract.Unit.Id", unitId));
>
> > > > > > > or with lambda extensions:
>
> > > > > > > Category categoryAlias = null;
> > > > > > > Contract contractAlias = null;
>
> > > > > > > var query = _nHibernateSession.CreateCriteria(typeof (Category),
> > > > > > > () =>
> > > > > > > categoryAlias)
> > > > > > > .CreateAlias(() => categoryAlias.Contracts, () =>
> > > > > > > contractAlias)
> > > > > > > .Add(() => contractAlias.Unit.Id == unitId);
>
> > > > > > > This however seems to have a sideeffect of loading in all the
> > > > > > > referencing Contracts as well. I only want the categories loaded
> > > > > > > in.
> > > > > > > Contract is a very complex entity and is completely unnessecary
> > > > > > > in the
> > > > > > > current context.
>
> > > > > > > Can this be done with Criteria API?
> > > > > > > I tried to SetFetchMode to Lazy for category.Contracts but that
> > > > > > > had
> > > > > > > no effect. The SQL query generated selects contracts with each
> > > > > > > category.
>
> > > > > > > This is however the only way i could query for categories with the
> > > > > > > condition needed. Am i missing something?
> > > > > > > I prefer a no magic string solution and thats also why i have two
> > > > > > > different query representations that are working. I must admit
> > > > > > > that
> > > > > > > the non lambda is more readable. I wish Nhib to Linq could do
> > > > > > > this in
> > > > > > > 2.1.2.- Peida kommenteeritud tekst -
>
> > > > > > - Näita kommenteeritud teksti -- Peida kommenteeritud tekst -
>
> > > > - Näita kommenteeritud teksti -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--
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.
