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?

Kimbrough

did you try .Select(...).Distinct().ToList() ? – Miyamoto

how about .Distinct().Select(x => new Foobar(...)).ToList() ? – Miyamoto

Are you using the old Linq2NH provider for 2.1, or are you using the new native LINQ provider in NH 3.x? If you're using the old provider, I think the issue is that the distinct operation isn't actually implemented. – Resin

This question is obsolete now - it is for the old Linq2NH provider – Kimbrough

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.

Dysprosium

I've tried putting Distinct() in all different positions but it has no observable effect. Running query profiler shows that the DISTINCT keyword is not being added to the sql query. – Kimbrough

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.

Outspeak