public void SingleKeyPropertyGroupAndOrderByCountBeforeProjection() { if (!TestDialect.SupportsOrderByAggregate) { Assert.Ignore("Dialect does not support ordering by an aggregation"); } // NH-3026, variation of NH-2560. // 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); }
public void OrderByWithSelfReferencedSubquery2() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-selects"); } if (!TestDialect.SupportsOrderByAndLimitInSubQueries) { Assert.Ignore("Dialect does not support sub-selects with order by or limit/top"); } if (Dialect is Oracle8iDialect) { Assert.Ignore("On Oracle this generates a correlated subquery two levels deep which isn't supported until Oracle 10g."); } //NH-3044 var result = (from order in db.Orders where order == db.Orders.OrderByDescending(x => x.OrderDate).First(x => x.Customer == order.Customer) orderby order.ShippingDate descending select order).ToList(); // Different databases may sort null either first or last. // We only bother about the non-null values here. result = result.Where(x => x.ShippingDate != null).ToList(); AssertOrderedBy.Descending(result.Take(5).ToList(), x => x.ShippingDate); }
public void OrderByWithSelfReferencedSubquery1() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-selects"); } if (!TestDialect.SupportsOrderByAndLimitInSubQueries) { Assert.Ignore("Dialect does not support sub-selects with order by or limit/top"); } if (Dialect is Oracle8iDialect) { Assert.Ignore("On Oracle this generates a correlated subquery two levels deep which isn't supported until Oracle 10g."); } //NH-3044 var result = (from order in db.Orders where order == db.Orders.OrderByDescending(x => x.OrderDate).First(x => x.Customer == order.Customer) orderby order.Customer.CustomerId select order).ToList(); AssertOrderedBy.Ascending(result.Take(5).ToList(), x => x.Customer.CustomerId); }
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); }
public void AggregateDescendingOrderByClause() { var query = from c in db.Customers orderby c.Orders.Count descending select c; var customers = query.ToList(); // Verify ordering for first 10 customers - to avoid loading all orders. AssertOrderedBy.Descending(customers.Take(10).ToList(), customer => customer.Orders.Count); }
public void GroupByThenOrderBy() { var query = from c in db.Customers group c by c.Address.Country into g orderby g.Key select new { Country = g.Key, Count = g.Count() }; var ids = query.ToList(); Assert.NotNull(ids); AssertOrderedBy.Ascending(ids, arg => arg.Country); }
public void OrderByWithSelfReferencedSubquery1() { if (Dialect is Oracle8iDialect) { Assert.Ignore("On Oracle this generates a correlated subquery two levels deep which isn't supported until Oracle 10g."); } //NH-3044 var result = (from order in db.Orders where order == db.Orders.OrderByDescending(x => x.OrderDate).First(x => x.Customer == order.Customer) orderby order.Customer.CustomerId select order).ToList(); AssertOrderedBy.Ascending(result.Take(5).ToList(), x => x.Customer.CustomerId); }
public void OrderByCalculatedAggregatedSubselectProperty() { //NH-2781 var result = db.Orders .Select(o => new { o.OrderId, TotalQuantity = o.OrderLines.Sum(c => c.Quantity) }) .OrderBy(s => s.TotalQuantity) .ToList(); Assert.That(result.Count, Is.EqualTo(830)); AssertOrderedBy.Ascending(result, s => s.TotalQuantity); }
public async Task SingleKeyGroupAndOrderByKeyProjectionAsync() { //NH-2452 var result = await(db.Products .GroupBy(i => i.UnitPrice) .Select(g => new { UnitPrice = g.Key, TotalUnitsInStock = g.Sum(i => i.UnitsInStock) }) .OrderBy(x => x.UnitPrice) .ToListAsync()); Assert.That(result.Count, Is.EqualTo(62)); AssertOrderedBy.Ascending(result, x => x.UnitPrice); }
public async Task AggregateAscendingOrderByClauseAsync() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-selects"); } var query = from c in db.Customers orderby c.Orders.Count select c; var customers = await(query.ToListAsync()); // Verify ordering for first 10 customers - to avoid loading all orders. AssertOrderedBy.Ascending(customers.Take(10).ToList(), customer => customer.Orders.Count); }
public void SingleKeyGroupAndOrderByKey() { //NH-2452 var result = db.Products .GroupBy(i => i.UnitPrice) .OrderBy(g => g.Key) .Select(g => new { UnitPrice = g.Max(i => i.UnitPrice), TotalUnitsInStock = g.Sum(i => i.UnitsInStock) }) .ToList(); Assert.That(result.Count, Is.EqualTo(62)); AssertOrderedBy.Ascending(result, x => x.UnitPrice); }
public void SingleKeyGroupAndOrderByKeyAggregateProjection() { //NH-2452 var result = db.Products .GroupBy(i => i.Name) .Select(g => new { Name = g.Max(i => i.Name), TotalUnitsInStock = g.Sum(i => i.UnitsInStock) }) .OrderBy(x => x.Name) .ToList(); Assert.That(result.Count, Is.EqualTo(77)); AssertOrderedBy.Ascending(result, x => x.Name); }
public void AggregateAscendingOrderByClause() { if (!TestDialect.SupportsAggregatingScalarSubSelectsInOrderBy) { Assert.Ignore("Dialect does not support aggregating scalar sub-selects in order by"); } var query = from c in db.Customers orderby c.Orders.Count select c; var customers = query.ToList(); // Verify ordering for first 10 customers - to avoid loading all orders. AssertOrderedBy.Ascending(customers.Take(10).ToList(), customer => customer.Orders.Count); }
public void SingleKeyPropertyGroupAndOrderByProjectedCount() { // NH-2560 var orderCounts = db.Orders .GroupBy(o => o.Customer.CustomerId) .Select(g => new { CustomerId = g.Key, OrderCount = g.Count() }) .OrderByDescending(t => t.OrderCount) .ToList(); AssertOrderedBy.Descending(orderCounts, oc => oc.OrderCount); }
public void SingleKeyGroupAndOrderByNonKeyAggregateProjection() { if (!TestDialect.SupportsOrderByAggregate) { Assert.Ignore("Dialect does not support ordering by an aggregation"); } //NH-2452 var result = db.Products .GroupBy(p => p.UnitPrice) .Select(g => new { UnitPrice = g.Max(i => i.UnitPrice), TotalUnitsInStock = g.Sum(i => i.UnitsInStock) }) .OrderBy(x => x.TotalUnitsInStock) .ToList(); Assert.That(result.Count, Is.EqualTo(62)); AssertOrderedBy.Ascending(result, x => x.TotalUnitsInStock); }
public async Task OrderByCalculatedAggregatedSubselectPropertyAsync() { if (!Dialect.SupportsScalarSubSelects) { Assert.Ignore("Dialect does not support scalar sub-selects"); } //NH-2781 var result = await(db.Orders .Select(o => new { o.OrderId, TotalQuantity = o.OrderLines.Sum(c => c.Quantity) }) .OrderBy(s => s.TotalQuantity) .ToListAsync()); Assert.That(result.Count, Is.EqualTo(830)); AssertOrderedBy.Ascending(result, s => s.TotalQuantity); }
public void OrderByCalculatedAggregatedSubselectProperty() { if (!TestDialect.SupportsAggregatingScalarSubSelectsInOrderBy) { Assert.Ignore("Dialect does not support aggregating scalar sub-selects in order by"); } //NH-2781 var result = db.Orders .Select(o => new { o.OrderId, TotalQuantity = o.OrderLines.Sum(c => c.Quantity) }) .OrderBy(s => s.TotalQuantity) .ToList(); Assert.That(result.Count, Is.EqualTo(830)); AssertOrderedBy.Ascending(result, s => s.TotalQuantity); }
public void SingleKeyPropertyGroupAndOrderByProjectedCount() { if (!TestDialect.SupportsOrderByAggregate) { Assert.Ignore("Dialect does not support ordering by an aggregation"); } // NH-2560 var orderCounts = db.Orders .GroupBy(o => o.Customer.CustomerId) .Select(g => new { CustomerId = g.Key, OrderCount = g.Count() }) .OrderByDescending(t => t.OrderCount) .ToList(); AssertOrderedBy.Descending(orderCounts, oc => oc.OrderCount); }
public void SingleKeyPropertyGroupByEntityAndSelectEntity() { // The problem with this test (as of 2014-07-25) is that the generated SQL will // try to select columns that are not included in the group-by clause. But on MySQL and // sqlite, it's apparently ok. // The try-catch in this clause aim to ignore the test on dialects where it shouldn't work, // but give us a warning if it does start to work. try { 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); } catch (Exception) { if (Dialect is MySQLDialect || Dialect is SQLiteDialect) { throw; } Assert.Ignore("Known bug NH-3027, discovered as part of NH-2560."); } if (Dialect is MySQLDialect || Dialect is SQLiteDialect) { return; } Assert.Fail("Unexpected success in test. Maybe something was fixed and the test needs to be updated?"); }