NHibernate
  1. NHibernate
  2. NH-3027

Linq group by entity and returning the entity fails with wrong SQL

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.2.0.GA
    • Fix Version/s: None
    • Component/s: Linq Provider
    • Labels:

      Description

      The following test is a variation of the test given in the first comment on NH-2560. This test fails, because the generated SQL includes all properties from the customer in the select clause, which of course isn't allowed since we are only grouping on the customer's id. Not sure if this is an important case, but to get it working would require a subquery to be generated.

      [Test]
      public void SingleKeyPropertyGroupByEntityAndSelectEntity()
      {
      	var orderCounts = db.Orders
      		.GroupBy(o => o.Customer)
      		.Select(g => new { Customer = g.Key, OrderCount = g.Count() })
      		.OrderByDescending(t => t.OrderCount)
      		.ToList();
      
      	AssertOrderedBy.Descending(orderCounts, oc => oc.OrderCount);
      }
      

      The flawed SQL:

          select
              customer1_.CustomerId as col_0_0_,
              cast(count(*) as INT) as col_1_0_,
              customer1_.CustomerId as CustomerId27_,
              customer1_.CompanyName as CompanyN2_27_,
              customer1_.ContactName as ContactN3_27_,
              customer1_.ContactTitle as ContactT4_27_,
              customer1_.Address as Address27_,
              customer1_.City as City27_,
              customer1_.Region as Region27_,
              customer1_.PostalCode as PostalCode27_,
              customer1_.Country as Country27_,
              customer1_.Phone as Phone27_,
              customer1_.Fax as Fax27_ 
          from
              Orders order0_ 
          left outer join
              Customers customer1_ 
                  on order0_.CustomerId=customer1_.CustomerId 
          group by
              customer1_.CustomerId 
          order by
              cast(count(*) as INT) desc
      

        Issue Links

          Activity

          Hide
          Oskar Berggren added a comment -

          The test is committed to GroupByTest.cs.

          Show
          Oskar Berggren added a comment - The test is committed to GroupByTest.cs.
          Hide
          Alexander I. Zaytsev added a comment -

          Because we selecting entire customer the query is detected as non-aggregating group by and processes by NonAggregatingGroupByRewriter which fallback to very limit client-side grouping.

          Show
          Alexander I. Zaytsev added a comment - Because we selecting entire customer the query is detected as non-aggregating group by and processes by NonAggregatingGroupByRewriter which fallback to very limit client-side grouping.
          Hide
          David Roth added a comment - - edited

          I do have a similar problem. This issue is very important for as as we do rely on the expression generated by a third-party component (devexpress grid).
          Our proposal to fix this would be to apply an aggregate function (MAX or MIN), to each column selected in the foreign-key table. This way an additional sub-select won`t be necessary.

          session.Query<Person>()
                          .Where(x => !x.VersionInfo.IsDeleted)
                          .GroupBy(x => x.Country).OrderBy(x => x.Key)
                          .Select(x => new
                          {
                              Key = x.Key,
                              Count = x.Count(),
                          }).ToList();
          

          Current wrong SQL:
          ---------

          select country1_.Id as col_0_0_, 
          cast(count(*) as INT) as col_1_0_, 
          cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_, 
          country1_.Id as Id1_, 
          country1_.Name as Name1_ 
          from tbl_Person person0_ left outer join tbl_Country country1_ 
          	on person0_.CountryId=country1_.Id 
          	group by person0_.CountryId order by person0_.CountryId asc
          

          Possible fixed SQL using aggreagte function:
          ---------

          select MAX(country1_.Id) as col_0_0_, 
          cast(count(*) as INT) as col_1_0_, 
          cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_, 
          MAX(country1_.Id) as Id1_, 
          MAX(country1_.Name) as Name1_ 
          from tbl_Person person0_ left outer join tbl_Country country1_ 
          	on person0_.CountryId=country1_.Id 
          
          	group by person0_.CountryId order by person0_.CountryId asc
          Show
          David Roth added a comment - - edited I do have a similar problem. This issue is very important for as as we do rely on the expression generated by a third-party component (devexpress grid). Our proposal to fix this would be to apply an aggregate function (MAX or MIN), to each column selected in the foreign-key table. This way an additional sub-select won`t be necessary. session.Query<Person>() .Where(x => !x.VersionInfo.IsDeleted) .GroupBy(x => x.Country).OrderBy(x => x.Key) .Select(x => new { Key = x.Key, Count = x.Count(), }).ToList(); Current wrong SQL: --------- select country1_.Id as col_0_0_, cast(count(*) as INT) as col_1_0_, cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_, country1_.Id as Id1_, country1_.Name as Name1_ from tbl_Person person0_ left outer join tbl_Country country1_ on person0_.CountryId=country1_.Id group by person0_.CountryId order by person0_.CountryId asc Possible fixed SQL using aggreagte function: --------- select MAX(country1_.Id) as col_0_0_, cast(count(*) as INT) as col_1_0_, cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_, MAX(country1_.Id) as Id1_, MAX(country1_.Name) as Name1_ from tbl_Person person0_ left outer join tbl_Country country1_ on person0_.CountryId=country1_.Id group by person0_.CountryId order by person0_.CountryId asc
          Hide
          Chris Bristol added a comment - - edited

          This does not appear to be an issue with the LINQ provider. The HQL equivalent of the LINQ given in GroupByTest.SingleKeyPropertyGroupByEntityAndSelectEntity will generate the same SQL.

          using (var logSpy = new SqlLogSpy())
          {
              var x = session.CreateQuery("select o.Customer, count(o) from Order o group by o.Customer").List();
          }
          Show
          Chris Bristol added a comment - - edited This does not appear to be an issue with the LINQ provider. The HQL equivalent of the LINQ given in GroupByTest.SingleKeyPropertyGroupByEntityAndSelectEntity will generate the same SQL. using ( var logSpy = new SqlLogSpy()) { var x = session.CreateQuery( "select o.Customer, count(o) from Order o group by o.Customer" ).List(); }

            People

            • Assignee:
              Unassigned
              Reporter:
              Oskar Berggren
            • Votes:
              14 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

              • Created:
                Updated:

                Who's Looking?