Table of Contents

SELECT DISTINCT

Remove duplicate rows with Distinct on an entity builder or on a projected query, including over joins, paging and set operations.

Prerequisites: Querying and projections · Sorting and paging · Set operations

Overview

nextorm exposes two entry points for SELECT DISTINCT:

  • Distinct — sets the flag on the builder, before Select:

    dataContext.From<IComplexEntity>().Distinct().Select(x => new { x.Int })

  • Distinct — sets the flag on an already projected command:

    dataContext.From<IComplexEntity>().Select(x => new { x.Int }).Distinct()

Both produce the same SQL. DISTINCT applies to the whole select list, so the duplicates that are removed are duplicates of the projected tuple, not of a single column.

Basic distinct

// complex_entity stores nullableint values null, 1, 1 so DISTINCT must collapse the two 1s.
var rows = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Int })
    .Distinct()
    .ToList();

var same = dataContext.From<IComplexEntity>()
    .Distinct()
    .Select(e => new { e.Int })
    .ToList();

select distinct nullableint as 'Int'
 from complex_entity

The Output: tables below show the rows returned by each example against the integration-test seed data (tests/nextorm.integration.tests/Providers/TestProviders/SqliteTestProvider.cs).

Output:

Int
null
1

Distinct over a join

A projected join can contain duplicate rows; Distinct collapses them:

// complex_entity booleans are true, false, false, so the projected join has duplicate rows.
var rows = dataContext.From<ISimpleEntity>()
    .Join<IComplexEntity>((s, c) => s.Id == c.Id)
    .Select(p => new { p.Item2.Boolean })
    .Distinct()
    .ToList();

select distinct t2.b as 'Boolean'
 from simple_entity as 't1'
 join complex_entity as 't2' on cast(t1.id as bigint) = t2.id

A cross join produces the full Cartesian product, which Distinct then reduces to the distinct values of the projected column:

var all = dataContext.From<ISimpleEntity>()
    .CrossJoin(dataContext.From<IComplexEntity>())
    .Select(p => new { p.Item2.Id })
    .ToList(); // 30 rows

var distinct = dataContext.From<ISimpleEntity>()
    .CrossJoin(dataContext.From<IComplexEntity>())
    .Select(p => new { p.Item2.Id })
    .Distinct()
    .ToList(); // 3 rows

select distinct t2.id
 from simple_entity as 't1'
 cross join complex_entity as 't2'

Distinct and paging

Paging is applied to the distinct result. The Limit/Page call is placed before Select, and the double-checked shape (Distinct then Limit) is governed by the provider's keyword order:

var distinct = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Boolean })
    .Distinct()
    .ToList(); // 2 rows

var firstPage = dataContext.From<IComplexEntity>()
    .Limit(1)
    .Select(e => new { e.Boolean })
    .Distinct()
    .ToList(); // 1 row

-- SQLite / PostgreSQL: the distinct result is paged
select distinct b as 'Boolean'
 from complex_entity limit 1

-- SQL Server: DISTINCT must precede TOP
select distinct top(1) b as 'Boolean'
 from complex_entity

On SQL Server, select top(1) distinct ... is invalid T-SQL, so the dialect emits distinct before top(n) (the SQL-generation test pins the order with e.Distinct().Limit(5).Select(x => x.Id) → select distinct top(5) id from simple_entity).

Distinct and set operations

Distinct is a property of its own command, not of the whole chain, so it only deduplicates the branch it is attached to:

var cmd = dataContext.From<IComplexEntity>().Select(it => it.Int)
    .Distinct()
    .Union(dataContext.From<IComplexEntity>().Select(it => it.Int));

var count = dataContext.From(cmd).Count(); // 2

-- The left branch carries DISTINCT; UNION removes the remaining duplicates.
select distinct nullableint
 from complex_entity
 union 
select nullableint
 from complex_entity

With UnionAll the branches are concatenated without an extra deduplication, so the left branch is distinct but the right one is not:

var cmd = dataContext.From<IComplexEntity>().Select(it => it.Int)
    .Distinct()
    .UnionAll(dataContext.From<IComplexEntity>().Select(it => it.Int));

var count = dataContext.From(cmd).Count();
// Left branch is distinct ({null, 1}); UNION ALL keeps the right branch ({null, 1, 1}) => 5 rows.

select distinct nullableint
 from complex_entity
 union all 
select nullableint
 from complex_entity

DISTINCT ON (PostgreSQL)

PostgreSQL also supports DISTINCT ON (expr, ...), which keeps the first row of each distinct key according to the ORDER BY (the leading sort expressions must match the key). Use DistinctOn instead of Distinct; combining the two throws, because PostgreSQL treats them as mutually exclusive.

var rows = dataContext.From<IComplexEntity>()
    .DistinctOn(e => e.String)
    .OrderBy(e => e.String)
    .Select(e => new { e.Id, e.String })
    .ToList();

select distinct on (somestring) id, somestring
 from complex_entity order by somestring

The key may be an anonymous type to key on several columns. Only PostgreSQL implements DISTINCT ON; every other provider rejects it at SQL build time.

Provider differences

Provider DISTINCT + limit DISTINCT over a join
SQLite select distinct ... limit N supported
SQL Server select distinct top(N) ... (DISTINCT before TOP) supported
PostgreSQL select distinct ... limit N supported
MySQL select distinct ... limit N supported
MariaDB select distinct ... limit N supported
ClickHouse select distinct ... limit N supported
In-memory duplicates removed by the in-memory enumerator (IsDistinct is honoured) not covered by the in-memory test suite

See also

Source: tests/nextorm.integration.tests/CommonTestSuite/Queries/Rows/CommonTestSuite.Distinct.cs:9, tests/nextorm.sqlite.tests/SqlGeneration/SqlGenerationTests.cs:26, tests/nextorm.sqlserver.tests/SqlGeneration/SqlGenerationTests.cs:26,35, tests/nextorm.postgres.tests/SqlGeneration/SqlGenerationTests.cs:25.

Edit this page