Linq to NHibernate: Distinct

Question

I am trying to get the following SQL output using Linq-to-NHibernate:

SELECT DISTINCT Name, at.Year FROM MyTable mt
INNER JOIN AnotherTable at ON at.Id = mt.AnotherTableId

The Name and Year properties are going to be wrapped up in a new class, so the C# code will look something like this:

Session.Linq()
   .Select(x => new FooBar { Name = x.Name, Year = x.AnotherTable.Year }))
   .ToList();

How can I get the DISTINCT keyword to appear in the sql query?

Answer 1

Can't your try:

Session.Linq()
   .Select(x => new FooBar { Name = x.Name, Year = x.Year }))
   .Distinct()
   .ToList();

Select returns an IEnumerable, so by default it should have Distinct, regardless of whether your intellisense detects it or not.

Answer 2

I do not use the linq provider day-to-day, but looking around it does not seem possible.

Could you consider QueryOver?

 First firstReference = null;
        Second secondReference = null;
        var items = Session().QueryOver(() => firstReference)
                        .JoinAlias(() => firstReference.Seconds, () => secondReference)
                        .Select(Projections.Distinct(
                                Projections.ProjectionList()
                                    .Add(Projections.Property(() => firstReference.Name).WithAlias(() => firstReference.Name))
                                    .Add(Projections.Property(() => secondReference.Year).WithAlias(() => secondReference.Year))))
                        .TransformUsing(Transformers.AliasToBean(typeof(FooBar)))
                        .List<FooBar>();

I hope this helps.