LINQ - How to use distinct with order by?

Question

I want to do a distinct on this:

query = from acao in query
  join itemAuditoria in Session.Query<ItemAuditoria>() on acao.Id equals itemAuditoria.Acao.Id
  join auditoria in Session.Query<Auditoria>() on itemAuditoria.Auditoria.Id equals auditoria.Id
  join maquina in Session.Query<Maquina>() on auditoria.Maquina.Id equals maquina.Id
  orderby maquina.Nome, acao.Numero
  select acao;

But if I try putting a distinct at the end of the query like this:

query = (from acao in query
  join itemAuditoria in Session.Query<ItemAuditoria>() on acao.Id equals itemAuditoria.Acao.Id
  join auditoria in Session.Query<Auditoria>() on itemAuditoria.Auditoria.Id equals auditoria.Id
  join maquina in Session.Query<Maquina>() on auditoria.Maquina.Id equals maquina.Id
  orderby maquina.Nome, acao.Numero
  select acao).Distinct();

then I get this error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

As the error says: `ORDER BY items must appear in the select list if SELECT DISTINCT is specified.` — L.B, Jun 02 '14 at 19:12
right, how do I rewrite this code to achieve that? I can't simply cut and paste the order by after distinct. — alansiqueira27, Jun 02 '14 at 19:13
you need to select your objects, then order them after you have disctint the collection — Derek, Jun 02 '14 at 19:13
remove the orderby clause and add .OrderBy(obj => obj.Nome).ThenBy(obj => obj.Numero) *after* the .Distinct() — , Jun 02 '14 at 19:32
`....).OrderBy(here lambda which must order your objects).Distinct();` — Hamlet Hakobyan, Jun 02 '14 at 19:35
@Toons33 His `select` isn't selecting out all of the fields that he is going to order by. — Servy, Jun 02 '14 at 19:37
@Servy I think the Distinct extension method in LINQ does not have a query syntax equivalent (see [http://stackoverflow.com/questions/5720945/is-there-a-linq-syntax-way-of-using-distinct]) so he probably needs to add those in the query — , Jun 02 '14 at 19:46
@Toons33 Yes, `Distinct` doesn't have a query syntax equivalent. Even if it did, it would *still* be a non-trivial problem to solve. You can't just translate everything to method syntax. That doesn't solve the problem. The select needs to come before the `Distinct` or else items that differ only in columns not projected aren't removed, but the select needs to be *after* the `OrderBy` because columns not projected are ordered, and the `OrderBy` needs to be before the `Distinct` because of the above error message. It's a cyclical dependency graph. — Servy, Jun 02 '14 at 19:51
This is a LINQ to SQL bug and the question is legitimate. No matter whether the query is logically broken L2S may never emit invalid SQL. Because L2S is no longer supported and will not be fixed, you must find a workaround. Play with the query. — usr, Jun 02 '14 at 21:01

Robert Synoradzki · Answer 1 · 2014-06-10T12:51:21.757

Not sure about the performance impact compared to the answer in OP's comments, but this could be subqueried for readability!

query =
    (from distinctAcao in
        (from acao in query
        join itemAuditoria in Session.Query<ItemAuditoria>() on acao.Id equals itemAuditoria.Acao.Id
        join auditoria in Session.Query<Auditoria>() on itemAuditoria.Auditoria.Id equals auditoria.Id
        join maquina in Session.Query<Maquina>() on auditoria.Maquina.Id equals maquina.Id
        select acao).Distinct()
    orderby maquina.Nome, distinctAcao.Numero
    select distinctAcao);

edit:

So sorry for overlooking that it didn't compile - it did in my head ;) Corrections on the way!

Because we're doing subquery here, the result of which is a list of acao items which don't have a Nome field, we cannot sort by it after we leave the subquery. Therefore we can either:

Sort inside subquery, then eliminate duplicates.

query=(
    from distinctAcao in (
        from acao in query
        join itemAuditoria in Session.Query<ItemAuditoria>() on acao.Id equals itemAuditoria.Acao.Id
        join auditoria in Session.Query<Auditoria>() on itemAuditoria.Auditoria.Id equals auditoria.Id
        join maquina in Session.Query<Maquina>() on auditoria.Maquina.Id equals maquina.Id
        orderby maquina.Nome,acao.Numero
        select acao
    ).Distinct()
    select distinctAcao
);

Result: sorted, unique acaos list

Cons: first sorting, then filtering might impact performance

Return a better, combined object from the subquery, which will expose new fields from joined tables.

query2=(
    from distinctMix in (
        from acao in query
        join itemAuditoria in Session.Query<ItemAuditoria>() on acao.Id equals itemAuditoria.Acao.Id
        join auditoria in Session.Query<Auditoria>() on itemAuditoria.Auditoria.Id equals auditoria.Id
        join maquina in Session.Query<Maquina>() on auditoria.Maquina.Id equals maquina.Id
        select new { Id=acao.Id,Numero=acao.Numero,NomeFromMaquina=maquina.Nome }
    ).Distinct()
    orderby distinctMix.NomeFromMaquina,distinctMix.Numero
    select distinctMix
);

Result: quickly sorted, unique (acao+more) list

Cons: returned object will be of different type than original query that was used to iterate acaos, so assigning query=from acao in query select new { /* something else than acao */ }; won't work (hence query2 in this code example)

Use answer from OP's comments :)

P.S.

This time the code compiles for sure - test code available at https://ideone.com/mDRRle :>

this code doesn't compile. I can't put "order by" after Distinct(), the compiler complains that "maquina" (after orderby) isn't recognized. — alansiqueira27, Jun 06 '14 at 13:13
thanks for the effort, but as I mentioned in the second block of code of my question, if I put an Distinct in the query that used order by, then it's enough to throw an exception at runtime. Maybe it works on EntityFramework, but I'm using NHibernate. — alansiqueira27, Jun 10 '14 at 20:22
Of the two solutions in your edit, neither works. The first one calls `Distinct` after `OrderBy`, and `Distinct` reorders the items. The second query won't work because you're performing a `Distinct` on additional columns, which means that items that differ only by those columns are being included when they shouldn't be. — Servy, Jun 10 '14 at 20:37
The second remark I didn't realise; as for the first Distinct() didn't change order on my computer nor online compiler so I assumed it preserves it; ergo: this is a well failed attempt at an answer, I apologise for that :C — Robert Synoradzki, Jun 11 '14 at 07:47

LINQ - How to use distinct with order by?

1 Answers1

edit:

P.S.