Uploaded image for project: 'NHibernate'
  1. NHibernate
  2. NH-3027

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

    Details

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

      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
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              oskar.berggren Oskar Berggren added a comment -

              The test is committed to GroupByTest.cs.

              Show
              oskar.berggren Oskar Berggren added a comment - The test is committed to GroupByTest.cs.
              Hide
              hazzik Alexander 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
              hazzik Alexander 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
              droth 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
              droth 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
              ccbristo 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
              ccbristo 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(); }
              Hide
              rjperes Ricardo Peres added a comment - - edited

              An alternative is to group by id, in LINQ:

              var orderCounts = db.Orders
              		.GroupBy(o => o.Customer.Id)
              		.Select(g => new { CustomerId = g.Key, OrderCount = g.Count() })
              		.OrderByDescending(t => t.OrderCount)
              		.ToList();
              

              And in HQL:

              
              var x = session.CreateQuery("select o.Customer.Id, count(o) from Order o group by o.Customer.Id").List();
              
              
              Show
              rjperes Ricardo Peres added a comment - - edited An alternative is to group by id, in LINQ: var orderCounts = db.Orders .GroupBy(o => o.Customer.Id) .Select(g => new { CustomerId = g.Key, OrderCount = g.Count() }) .OrderByDescending(t => t.OrderCount) .ToList(); And in HQL: var x = session.CreateQuery( "select o.Customer.Id, count(o) from Order o group by o.Customer.Id" ).List();
              Hide
              fab Fabrizio Gennari added a comment -

              The problem does not occur when using the ICriteria API, valid SQL is generated:

              IList numOrdersPerCustomer = session.CreateCriteria<Orders>()
                                  .SetProjection(
                                      Projections.ProjectionList()
                                      .Add(Projections.GroupProperty("Customer"))
                                      .Add(Projections.RowCount()))
                                  .List();
              
              Show
              fab Fabrizio Gennari added a comment - The problem does not occur when using the ICriteria API, valid SQL is generated: IList numOrdersPerCustomer = session.CreateCriteria<Orders>() .SetProjection( Projections.ProjectionList() .Add(Projections.GroupProperty( "Customer" )) .Add(Projections.RowCount())) .List();

                People

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

                  Dates

                  • Created:
                    Updated:

                    Who's Looking?