Thanks - yes, I'll go down the named query route; I was just hoping to avoid it from a 'purity' perspective and because I'm mentoring a colleague who I don't want to start arbitrarily using SQL where it's avoidable. Glad you reminded me about the named query instead of just creating a SQL query 'on-the-fly'.
Appreciated, /Pete From: [email protected] [mailto:[email protected]] On Behalf Of Ricardo Peres Sent: 02 May 2013 16:53 To: [email protected] Subject: Re: [nhusers] Re: Determine the existence of an entity meeting a condition Ah, yes, sorry, now I understand! :-) I don't think you can do it with Query Over or any other API, they always require a source entity. But you can do it with SQL: bool isMyConditionMet = session.CreateSQLQuery("case ....")).UniqueResult<Boolean>() == true; Or even better, with a named query: bool isMyConditionMet = session.GetNamedQuery("CheckExistence").UniqueResult<Boolean>() == true; Will that do? RP On Thursday, May 2, 2013 2:14:25 PM UTC+1, PeteA wrote: Guess I've not explained myself very well - sorry, and thanks for your efforts to help. Basically, I want to execute *just* the "case when exists (select 1 from product where price < 100) then 1 else 0 end as [exists]" part of your suggestion - that's the exact statement I want the DB to run, nothing else. I think that your suggestion below would execute something like select case when exists (select 1 from product where price < 100) then 1 else 0 end as [exists] from Product where Product.ProductType = 1 Those last two lines (italicized) are what I want to avoid; I'm literally after a single, scalar result of '1' if any row(s) match my condition or '0' if none do. Sort of like bool isMyConditionMet = session.QueryOver<void>().Select(SqlProjection("case ....")).Single() == 1; /Pete From: [email protected] <javascript:> [mailto:[email protected] <javascript:> ] On Behalf Of Ricardo Peres Sent: 02 May 2013 12:11 To: [email protected] <javascript:> Subject: Re: [nhusers] Re: Determine the existence of an entity meeting a condition I may have misunderstood, but what about this, which is basically the same thing? var exists = session.QueryOver<Product>().Where(x => x.ProductType == 1).Select(NHibernate.Criterion.Projections.SqlProjection("case when exists (select 1 from product where price < 100) then 1 else 0 end as [exists]", new String[] { "exists" }, new IType[] { NHibernateUtil.Boolean })).SingleOrDefault<Boolean>(); RP On Thursday, May 2, 2013 10:56:41 AM UTC+1, PeteA wrote: Don't think so, unfortunately - my reading of that is that it'll produce a table with two columns (Name and pricerange) with a row per Product? My requirement is to produce a scalar result (0 or 1) depending upon whether there is / is not an entity meeting the condition - effectively I want a QueryOver<void>. As an aside, I believe that the 'case' part is actually Projections.Conditional, so "case when price < 100 then 1 else 2 end as pricerange" could be refactored slightly as Projections.Conditional(Restrictions.Lt(x => x.Price, 100), Projections.Constant(1), Projections.Constant(2)) Thanks for the suggestion :) /Pete From: [email protected] [mailto:[email protected]] On Behalf Of Ricardo Peres Sent: 02 May 2013 10:40 To: [email protected] Subject: [nhusers] Re: Determine the existence of an entity meeting a condition You know you can mix Query Over with Criteria, so you can use something like: var l = session.QueryOver<Product>().Select(NHibernate.Criterion.Projections.Pro perty<Product>(x => x.Name), NHibernate.Criterion.Projections.SqlProjection("case when price < 100 then 1 else 2 end as pricerange", new String[] { "pricerange" }, new IType[] { NHibernateUtil.String })).List<Object[]>(); Will that do? RP On Thursday, May 2, 2013 10:16:52 AM UTC+1, PeteA wrote: I know this should be simple, but I seem to be being brain-dead today... given some entity Foo, how can I write a QueryOver that translates into the following SQL: select case when exists (select 1 from FooTable where Name='some name') then 1 else 0 end I.E. - I want to be able to determine in code if the entity already exists; counting them would be trivial, but I don't want to do that because there could be a lot and it's a significant amount of unnecessary work for the DB. The inner subquery is easy enough - how do I generate the outer query though, which doesn't have a 'from' statement? Thanks, /Pete -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <javascript:> . To post to this group, send email to [email protected] <javascript:> . Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
