Hi Fabio, Your right. I was missing projection aliases and that fixed the issue with projections. Thank you, i didnet notice the alias issue right away.
Taavi On Aug 24, 12:34 am, Melborp <[email protected]> wrote: > This is the HQL of what i want and intend. Of course this is a string > and harder to validate :( > Works how i expect and would want the other to work. How to do this > with Criteria API > > var queryCategories = _nHibernateSession.CreateQuery( > @"select c.Categories from Contract c > where c.Unit.Id = :unitId and c.StatusId > :statusId and c.StopDate> > :stopDate"); > > queryCategories.SetParameter("unitId", unitId); > queryCategories.SetParameter("statusId", 7); > queryCategories.SetParameter("stopDate", DateTime.Now); > > On Aug 24, 12:21 am, Melborp <[email protected]> wrote: > > > > > 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 > > ... > > read more »- 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.
