How can I get distinct values using Linq to NHibernate?

Question

I've been trying to get distinct values using Linq to NHibernate and I'm failing miserably.

I've tried:

var query = from requesters in _session.Linq<Requesters>()
        orderby requesters.Requestor ascending
        select requesters;

return query.Distinct();

As well as

var query = from requesters in _session.Linq<Requesters>()
        orderby requesters.Requestor ascending
        select requesters;

return query.Distinct(new RequestorComparer());

Where RequestorComparer is

public class RequestorComparer : IEqualityComparer<Requesters>
{

    #region IEqualityComparer<Requesters> Members
    bool IEqualityComparer<Requesters>.Equals(Requesters x, Requesters y)
    {
        //return x.RequestorId.Value.Equals(y.RequestorId.Value);
        return ((x.RequestorId == y.RequestorId) && (x.Requestor == y.Requestor));
    }

    int IEqualityComparer<Requesters>.GetHashCode(Requesters obj)
    {
        return obj.RequestorId.Value.GetHashCode();
    }
    #endregion
}

No matter how I structure the syntax, it never seems to hit the .Distinct(). Without .Distinct() there are multiple duplicates by default in the table I'm querying, on order of 195 total records but there should only be 22 distinct values returned.

I'm not sure what I'm doing wrong but would greatly appreciate any assistance that can be provided.

Thanks

Marilla

What sql are those linq queries generating? – Recapitulation

Why don't you report it as a bug? – Largehearted

Answer 1

I have found that the following works (NHibernate v3.3.1).

         var query= (from requesters  in _session.Query<Requesters>() 
                         orderby requesters.Requestor  ascending
                         select requesters.Requestor).Distinct();

Mentality

Note: This uses the integrated Linq Provider Query<T> in NHibernate 3.0. The original question relates to NHibernate.Linq provider which is no longer supported. – Imray

Answer 2

Try reordering to :

var query = from requesters in _session.Linq<Requesters>()

    select requesters;

return query.Distinct().OrderBy(x=>x.Requestor);

I have seen issues with ordering of OrderBy and Distinct.

Let me know if that doesn't work out for you.

Insectivorous

Tried that, still didn't work right. I'm getting the exact same results as before. – Marilla

SELECT this_.REQUESTORID as FK8_0_0_, this_.REQUESTOR as REQUESTOR0_0_ FROM REQUESTORS this_ WHERE this_.REQUESTORID = :p0 ORDER BY this_.REQUESTOR asc;:p0 = 10775 <-- This is the SQL that is output by the above query. – Marilla

Answer 3

Have you tried with the new integrated Linq provider in NH 3.0?

The old one is very limited.

Epitomize