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 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 AggregateDescendingOrderByClause()
{
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 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 async Task AggregateDescendingOrderByClauseAsync()
{
if (!Dialect.SupportsScalarSubSelects)
{
Assert.Ignore("Dialect does not support scalar sub-selects");
}
var query = from c in db.Customers
orderby c.Orders.Count descending
select c;
var customers = await(query.ToListAsync());
// Verify ordering for first 10 customers - to avoid loading all orders.
AssertOrderedBy.Descending(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 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?");
}