Help w/Distinct

sspagna · **Joined:** Thu May 29, 2008 4:11 am **Posts:** 8

Hi all,

I have to perform distinct clause in this context :

Code:

Dim criteria As ICriteria = CreateCriteria()
criteria.SetProjection(Projections.RowCount())
Return criteria.UniqueResult(Of Integer)()

I have to obtain the number of distinct records.
I try to add a distinct Projection on Projection.Id but it doesn't work.
The Id is a Composite identifier.

How can I do it?
Thanks in advance,
Simone

wolli · **Posted:** Mon Jun 02, 2008 9:59 am

You can use

Code:

criteria.SetProjection(Projections.CountDistinct("PropertyName));

to do that on a certain property or

Code:

criteria.SetProjection(Projections.Distinct(Projecsions.ProjectionList()
   .Add(Projections.Property("one"))
   .Add(Projections.Property("two"))
   .Add(Projections.Property("..."))
));

to do that on more properties. If that does not help, give us some more information about the criteria, e.g. class, mapping or a sample sql.

sspagna · **Joined:** Thu May 29, 2008 4:11 am **Posts:** 8

Hi Wolli,
thank you for your reply.
I have already tried with

Code:

criteria.SetProjection(Projections.Distinct(Projecsions.ProjectionList()
   .Add(Projections.Property("one"))
   .Add(Projections.Property("two"))
));

In my case "one" and "two" are part of a composite identifier. In this situation the query fails.
How can I work aroud this?
Thank you,
Simone

wolli · **Posted:** Mon Jun 02, 2008 2:06 pm

What do you mean with fails ? Does it nor produce the expected result or does it throw an exception ? It the latter, can you post the exception ? And the mapping would be helpful ...

sspagna · **Joined:** Thu May 29, 2008 4:11 am **Posts:** 8

Hi,

I throw an exception.
My mapping is the following :

Code:

<class name="PPP" table="PPP">
    <composite-id class="YearNumberCompositeKey" name="Id">
      <key-property column="Year" name="Year" />
      <key-property column="Number" name="Number" />
    </composite-id>
    <property name="Year" column="Year" insert="false"  update="false"/>
    <property name="Number" column="Number" insert="false"  update="false"/>
...
</class>

The error message I have is

Code:

"Length of the source matrix is not enought".

Any suggestion?
Bye,
Simone

wolli · **Posted:** Wed Jun 04, 2008 3:34 am

hmmm ... sounds like an ADO exception ? Can you post the criteria you really used and the generated sql ?

sspagna · **Joined:** Thu May 29, 2008 4:11 am **Posts:** 8

wolli wrote:

hmmm ... sounds like an ADO exception ? Can you post the criteria you really used and the generated sql ?

yes, it is an ADO exception.
I read on the net that Hibernate and NHibernate engines have problems with composite-id and projections.
I used a work around : now I'm doing a count on a criteria.List that does distinct with resultransformer.
I Thank you very much for your help.
Simone