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] [mailto:[email protected]] On
Behalf Of Ricardo Peres
Sent: 02 May 2013 12:11
To: [email protected]
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] <javascript:>
[mailto:[email protected] <javascript:> ] On Behalf Of Ricardo
Peres
Sent: 02 May 2013 10:40
To: [email protected] <javascript:>
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] <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.
