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?

how about .Distinct().Select(x => new Foobar(...)).ToList() ? — Mauricio Scheffer, Nov 17 '09 at 02:40
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. — moribvndvs, Aug 10 '11 at 20:14
This question is obsolete now - it is for the old Linq2NH provider — cbp, Jul 17 '12 at 01:51

score 1 · Answer 1 · answered Nov 16 '09 at 04:36

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.

answered Nov 16 '09 at 04:36

Graviton

81,782
146
424
602

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. – cbp Nov 16 '09 at 04:42

Jon Bates · Answer 2 · 2011-08-10T20:14:46.087

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.

Linq to NHibernate: Distinct

2 Answers2