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

Linq order by grouped count before select clause gives wrong sql

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects versions: 3.2.0.GA
    • Fix versions: 3.3.0.CR1
    • Components: Linq Provider
    • Labels:
      None
    • Sprint:

      Description

      The following test is a variation of the test in NH-2560 Closed . That test works now, after recent commits. This variation puts the ordering expression inside the call to OrderBy(), before the projection. This fails due to flawed SQL.

      Test
      public void SingleKeyPropertyGroupAndOrderByCountBeforeProjection()
      {
      // This is a variation of SingleKeyPropertyGroupAndOrderByProjectedCount()
      // that puts the ordering expression inside the OrderBy, without first
      // going through a select clause.

      var orderCounts = db.Orders
      .GroupBy(o => o.Customer.CustomerId)
      .OrderByDescending(g => g.Count())
      .Select(g =>
      new

      { CustomerId = g.Key, OrderCount = g.Count() }

      )
      .ToList();

      AssertOrderedBy.Descending(orderCounts, oc => oc.OrderCount);
      }

      The generated SQL. Notice missing count in the order by clause. There is also a superflouos join, but I suspect that may be covered by NH-3003 Closed .

      select
      customer1_.CustomerId as col_0_0_,
      cast(count as INT) as col_1_0_
      from
      Orders order0_
      left outer join
      Customers customer1_
      on order0_.CustomerId=customer1_.CustomerId
      group by
      customer1_.CustomerId
      order by
      order0_.OrderId desc

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              oskar.berggren Oskar Berggren
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Who's Looking?