Entity exist? Based on unique constraint

Jonas82 · **Joined:** Tue Oct 16, 2007 7:10 pm **Posts:** 3

Hi,

I am new to NHibernate.
Can somone give me a hint how I find out if an entity with a unique constraint already exist in database?

Thx.

jlockwood · **Posted:** Tue Oct 16, 2007 8:28 pm

There's a number of ways to do this. I've been using this lately:

Code:

int matchCount = session.CreateQuery(scalarQuery).UniqueResult();

the scalar query is an HQL query using select count(*). If the where clause specifies a unique column value then one or zero should return. You can also run aggregate queries using straight SQL if you wanted...you would then call session.CreateSQLQuery().

You could also use a get while specifying the unique field as a criterium. You should get a null if it is not already in the database.

Jonas82 · **Joined:** Tue Oct 16, 2007 7:10 pm **Posts:** 3

Thank you for the reply.

I am trying this:

int test = (int)session.CreateQuery("SELECT count(*) from User u WHERE u.UserName = :uname").SetString("uname", nickName).UniqueResult();

I get a cast exception. Looks like the query is performes correct. Can't the Object from UniqueResult be cast to an int?

jlockwood · **Posted:** Tue Oct 16, 2007 9:32 pm

If if the object reference is type int, sure. That's what I've done, well not using a direct cast. I wrapped the ExecuteScalarQuery in a method like so:

Code:

public object ExecuteScalarQuery(string query)
{
   return NHibernateSession.CreateQuery(query).UniqueResult()
}

I called it from VB.Net using CType(dao.ExecuteScalarQuery(query), INTEGER

Code:

...
return CType(dao.ExecuteScalarQuery(query), Integer)

I also wasn't using SetString(), but I'd imagine it would just return the modified IQuery instance. Not sure, I'd probably break it down into a few lines to ensure there were no issues. I'd also ensure that all input params are valid (like uname), check the SQL generated by NHibernate (in log file), run the query independently (which should make a difference here), then I'd ensure that I'm not getting a null back from the query (which I'm not sure why that would happen off hand).

Jonas82 · **Joined:** Tue Oct 16, 2007 7:10 pm **Posts:** 3

Following works. Thanks for your help!

Object test = session.CreateQuery("SELECT count(*) from User u WHERE u.UserName = :uname").SetString("uname", nickName).UniqueResult();
log.Debug("****Count obj = " + test.ToString());
int myint = Convert.ToInt32(test);
log.Debug("****Count int = " + myint.ToString());

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

I believe COUNT returns a long in NHibernate as of version 1.2.

Symon.

jlockwood · **Posted:** Thu Oct 18, 2007 1:36 pm

merge_s.rottem wrote:

I believe COUNT returns a long in NHibernate as of version 1.2.

Symon.

Hmm, I don't know for sure. I know that implementers of IExpectation use in for rowcounts. I would have guessed that it depended on what the database returns. In the tests it also looked as if NHibernateUtil.Int32 was used as the type for count when projections were defined (seen in CriteriaQueryTest).

I could see a long getting returned, although I'd probably never want to use too many aggregate funtions on a table with the over 2 billion records needed to justify a long...I joke.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

I'm pretty sure: http://www.hibernate.org/Documentation/ ... nGuide#A22

:)

Symon.

jlockwood · **Posted:** Thu Oct 18, 2007 8:43 pm

Nice! I didn't know that. I'll definitely keep that in mind (and change my code for good measure).

...I still wouldn't want to need that size...
;)