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 AggregateAscendingOrderByClause()
{
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 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 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 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 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 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 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 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 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);
}