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?");
}