CODEWITHSUNDEEP
c#sqlnhibernate
marc
marc

Reputation: 21

C# NHibernate Issue - Im trying to return Distinct values

C# NHibernate Issue - Im trying to return Distinct values.

This is what I have so far:

IList<TaskFor> objsResult = session.CreateCriteria(typeof(TaskFor)).setProjection(Projections.Distinct).List<TaskFor>();

return objsResult;

I am trying to return a group of values but I need to remove the duplicates. I cant seem to get set projections to work properly for this instance.

Look forward to hearing any answers.

Thanks,

marc

Upvotes: 2

Views: 3575

Answers (1)

Martin Buberl
Martin Buberl

Reputation: 47144

I'd say this answer from Aidan Boyle could be very helpfull for you.

To perform a distinct query you can set the projection on the criteria to Projections.Distinct. You then include the columns that you wish to return. The result is then turned back into an strongly-typed object by setting the result transformer to AliasToBeanResultTransformer - passing in the type that the result should be transformed into. In this example I am using the same type as the entity itself but you could create another class specifically for this query.

ICriteria criteria = session.CreateCriteria(typeof(Person));
criteria.SetProjection(
    Projections.Distinct(Projections.ProjectionList()
        .Add(Projections.Alias(Projections.Property("FirstName"), "FirstName"))
        .Add(Projections.Alias(Projections.Property("LastName"), "LastName"))));

criteria.SetResultTransformer(
    new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Person)));

IList people = criteria.List();

This creates SQL similar to (in SQL Server at least):

SELECT DISTINCT FirstName, LastName from Person

Please be aware that only the properties that you specify in your projection will be populated in the result.

The advantage of this method is that the filtering is performed in the database rather than returning all results to your application and then doing the filtering - which is the behaviour of DistinctRootEntityTransformer.

Upvotes: 4

Related Questions