Uploaded image for project: 'NHibernate [Moved to GitHub]'
  1. NH-2492

Problem with ROW_NUMBER and DISTINCT operator using LINQ

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.0.0.GA
    • Fix Version/s: 3.3.0.CR1
    • Component/s: Linq Provider
    • Labels:
      None
    • Sprint:

      Description

      LINQ provider, .NET 4.0, MSSQL 2008 with MSSQL 2008 Dialect

      When applying the distinct operator to paged data, the generated SQL (using the LINQ provider) uses ROW_NUMBER to achieve paging but applies the DISTINCT operator to the query containing the ROW_NUMBER value, so that duplicate rows are returned. The LINQ query looks like this:

      var records = (from t in Table1
      join c in ChildTable1 on t.Id equals c.ParentId
      select t).Distinct().Sorted().Paged();

      (The Sorted and Paged methods are just extension methods that apply the ordering and the Skip & Take calls to the IQueryable.)

      And the generated SQL looks like this:

      SELECT TOP (10) Field1, Field2, Field3
      FROM
      (select distinct
      table1_.ID as Field1,
      table1_.Name as Field2,
      table1_.SomeType as Field3,
      ROW_NUMBER() OVER(ORDER BY table1_.Name) as __hibernate_sort_row
      from dbo.Table1 table1_, dbo.ChildTable childTable1_
      where childTable1_.ParentID=table1_.ID) as query
      WHERE query.__hibernate_sort_row > 5
      ORDER BY query.__hibernate_sort_row

      I would have expected the DISTINCT operator to be applied to the actual query, before a row number was generated to support the paging.

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                remslave Stephen Edwards
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Who's Looking?